CODEWITHSUNDEEP
regexvbaoutlook
Matt
Matt

Reputation: 15

VBA Regex replace loop

I'm having issues with a regex replace loop for ticketing system formatting.

I am testing a segment of code that will regex match a specific format for a ticket number. The ticket number should be in a format of "INC01234567" (up to 8 numeric digits). The "INC" can be optional so that the user can just type in the ending number (i.e. 1234567) and the loop will add the additional "0's" to give the numeric amount up to 8 digits. Currently however, I am stuck on a math logic issue where it is adding one too many 0's to the result if you type in a full ticket number.

Sub Incident()
Dim sInc As String  'Incident Number Field
Dim strPattern As String: strPattern = "^(?:INC|NC|C)?([0-9]{1,8}$)"
Dim strReplaceINC As String: strReplaceINC = "$1"
Dim regEx As New RegExp
Dim strInput As String
Dim IncResult As Boolean

Do
    If strPattern <> "" Then
        
        strInput = inputbox("Input Incident Number", "Ticket Number")
        
        If strInput = vbNullString Then
            Exit Sub
        End If
        
        IncResult = False
    
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = True
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            sInc = regEx.Replace(strInput, strReplaceINC)
            Dim L As Integer: L = Len(sInc)
            Do
                sInc = "0" & sInc
                L = L + 1
            Loop While L <= 8
            sInc = "INC" & sInc
            IncResult = True
            'sInc = strInput
        Else
            MsgBox ("Please input a valid ticket number format")
            IncResult = False
        End If
    End If
    
Loop While IncResult = False
MsgBox (sInc)
End Sub

Upvotes: 0

Views: 238

Answers (3)

Valon Miller
Valon Miller

Reputation: 1156

The loop is unnecessary overhead, just use Format()

Replace all of this:

Dim L As Integer: L = Len(sInc)
Do
    sInc = "0" & sInc
    L = L + 1
Loop While L <= 8
sInc = "INC" & sInc

With this:

sInc = "INC" & Format(sInc, "00000000")

Upvotes: 2

Matschek
Matschek

Reputation: 215

The regex "^(?:INC|NC|C)?([0-9]{1,8}$)" does match a 8 digit ticket number 012345678, doesn't it? So If regEx.Test(strInput) Then will be true, and the do..loop runs at least on time before it breaks

Upvotes: 0

Josh Eller
Josh Eller

Reputation: 2065

You're checking the condition at the end of the loop, meaning the loop will always run at least once, regardless of the length of sInc.

Just replace:

Do
    sInc = "0" & sInc
    L = L + 1
Loop While L <= 8

With:

While L < 8
    sInc = "0" & sInc
    L = L + 1
Wend

Upvotes: 0

Related Questions