VBA: incrementing a number stored in a string while preserving it's formatting-CodePudding

In a VBA macro that I'm writing, I need to increment a number that is stored in a String variable, for example "1", "01", "001", etc. It can also be "010", "000123", and so on. I am aware of the Format call that can turn incremented number, for example "2" into "002" with Format(2, "00#"), but I don't know how to first read the format of the original number to store it's format and re-apply it after the increment. The only way I can think of is counting trailing zeroes, but isn't there a better way to do this?

CodePudding user response：

This function will preserve leading zeros:

Function IncrementNumberLeadingZeros(sVal As String, Change As Long) As String
    
    Dim L As Long
    Dim I As Long
    Dim Frmt As String
    
    L = Len(sVal)
    For I = 1 To L
        Frmt = Frmt & "0"
    Next I
    
    IncrementNumberLeadingZeros = Format(CLng(sVal)   Change, Frmt)
    
End Function

I tested like this:

Sub test()
    Debug.Print IncrementNumberLeadingZeros("00210", 1)
End Sub

Output Was:

OR, even shorter with a little help for our friends:

Function IncrementNumberLeadingZeros(sVal As String, Change As Long) As String
   IncrementNumberLeadingZeros = Format$(CLng(sVal)   Change, String(Len(sVal), "0"))
End Function