Increment in String in VBA excel

Question

Increment in String in VBA excel

Navigation

#1 by (1 votes)

0

A Strign in VBA excel has the following value: "T110A17014". I need to increment this string with each iteration. To increment only the last position the code below works! Now if we have this value: "T110A17099" and use the code below it 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

asked by anonymous 28.08.2018 / 07:47

1 answer

Bootstrap 4 - NavBar Fixed with BackGround White Analyze URL image

score 1 · Accepted Answer

One solution is to use Regular Expressions.

Regular Expression

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

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

And the demo can be viewed at this link

Enable Regex in Excel

RegEx needs to be enabled, Enable Developer mode

In the 'Developer' tab, click 'Visual Basic' and the VBA window will open.

Go to 'Tools' - > 'References ...' and a window will open.

Look for 'Microsoft VBScript Regular Expressions 5.5', as in the image below. And enable this option.

Code

DimnumeroAsLongDimcodigoAsString,resultadoAsStringDimobjCorrespAsObject,objExpRegAsObjectSetobjExpReg=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