Increment in String in VBA excel

Asked

Viewed 414 times

0

A Strign in VBA excel has the following value: "T110A17014". I need to increment this string every time. To increase only the last position the code below works! Now if we have this value: "T110A17099" and use the code below will look like this: "T110A170910"! How to increment correctly so that "T110A17099" stays: "T110A17100"? 

Dim TB As String = "T110A17014", txtBOX As String, Array() As String, x As Integer, I As Integer

If Not IsEmpty(TB) And TB <> "" Then      
     Array = TextBoxTOArray(TB) 
     x = Array(UBound(Array)) + 1
     For I = LBound(Array) To UBound(Array) - 1    
             txtBOX = txtBOX & Array(I) 
     Next
     TB = txtBOX & x 
End If
excel vba excel-vba

  • Can’t separate the numeric part of the string? Then you just have to increment it as a number and re-concatenate.

  • That! In this case we have 5 numerical positions after one letter; in other cases we will have 3 numerical positions after one letter.

  • For deduced something of this kind :/ And if you go through the string, inversely until you find the character that is not a number, and then, only increments in that part of the string, I do not know if it is a viable suggestion.

  • That’s exactly what I intend to do! Using the above code I could go back and forth and solve (theoretically). But I see a huge code nestled in. We need something smarter.

1 answer

1


One solution is the use of Regular Expressions.

Regular Expression

The following expression may be used: \d+(?=$)

Where this expression captures one or more digits \d+ before the end of the string (?=$)

And the demo can be seen in this link

Enable Regex in Excel

  1. Regex needs to be enabled, Enable the Developer mode
  2. In the 'Developer' tab, click 'Visual Basic' and the VBA window will open.
  3. Go to 'Tools' -> 'References...' and a window will open.
  4. Search for 'Microsoft Vbscript Regular Expressions 5.5', as in the image below. And enable this option.

Janela Referências

Code

Dim numero As Long
Dim codigo As String, resultado As String
Dim objCorresp As Object, objExpReg As Object
Set objExpReg = CreateObject("VBScript.RegExp")
'Expressão Regular
objExpReg.Pattern = "\d+(?=$)"
objExpReg.Global = True

codigo = "T110A17099"
Set objCorresp = objExpReg.Execute(codigo)
    If objCorresp.Count <> 0 Then
        For Each c In objCorresp
            numero = CLng(c) + 1
            codigo = Replace(codigo, c, "")
        Next c
    End If
resultado = codigo & numero
MsgBox (resultado)

Function Code (UDF)

Function funcao_incrementar(codigo As String) As String

Dim numero As Long
Dim objCorresp As Object, objExpReg As Object
Set objExpReg = CreateObject("VBScript.RegExp")
'Expressão Regular
objExpReg.Pattern = "\d+(?=$)"
objExpReg.Global = True

Set objCorresp = objExpReg.Execute(codigo)
    If objCorresp.Count <> 0 Then
        For Each c In objCorresp
            numero = CLng(c) + 1
            codigo = Replace(codigo, c, "")
        Next c
    End If
funcao_incrementar = codigo & numero
End Function

Testing

Sub teste()
    MsgBox (funcao_incrementar("T110A17099"))
End Sub

  • danieltakeshi san, what eh UDF Function?

  • User Defined Function, is a function created by VBA. Hence you can use it either in the spreadsheet or in a Sub.

Browser other questions tagged excel vba excel-vba

You are not signed in. Login or sign up in order to post.