Keeping leading 0 in string - numeric string conversion

I am working on a macro for a document tracking sheet at work. I am using a button that prompts the user to enter a document number and I would like to provide a default number based on the following numbering convention. The first two characters of the document number are the last two-year digits (in this case 15), then there is a "-" followed by a five-digit serialization.

My current code is looking at the last document entered and incrementing those last 5 characters, but chopping off any leading zeros I want to keep. This is a code extraction to generate this default number (assuming the "prevNCRF" variable is the name of the previous document found in the document):

    Sub codeChunkTester()

    Dim prevNCRF, defNCRFNum As String
    Dim NCRFNumAr() As String

    'pretend like we found this in the sheet.
    prevNCRF = "15-00100"

    'split the string into "15" and "00100" and throw those into an array.
    NCRFNumAr() = Split(prevNCRF, "-")

    'reconstruct the number by reusing the first part and dash, then converting
    'the "00100" to a number with Val(), adding 1, then back to a string with CStr().
    defNCRFNum = NCRFNumAr(0) & "-" & CStr(Val(NCRFNumAr(1)) + 1)

    'message box shows "15-101" rather than "15-00101" as I had hoped.
    MsgBox (defNCRFNum)

    End Sub


So can someone help me to store these zeros? I guess I could include a loop that checks the length of the string and adds a leading zero until there are 5 characters, but maybe there is a better way ...


source to share

3 answers

Converting "00100"

to Double

with Val

turned it to 100

, so it CStr(100)

returns "100"

as it should.

You need to format the string so that it looks like this:

defNCRFNum = NCRFNumAr(0) & "-" & Format(Val(NCRFNumAr(1)) + 1, "00000")


If you need to parameterize the length of a string, you can use a function String

to generate a format string:

Const digits As Integer = 5

Dim formatString As String
formatString = String(digits, "0")

defNCRFNum = NCRFNumAr(0) & "-" & Format(Val(NCRFNumAr(1)) + 1, formatString)




This is the solution to the loop I mentioned above. If anyone has anything better, I'm all ears!

prevNCRF = "15-00100"
NCRFNumAr() = Split(prevNCRF, "-")
zeroAdder = CStr(Val(NCRFNumAr(1)) + 1)

'loop: everytime the zeroAdder string is not 5 characters long, 
'put a zero in front of it.
Do Until Len(zeroAdder) = 5
    zeroAdder = "0" & zeroAdder

defNCRFNum = NCRFNumAr(0) & "-" & zeroAdder
MsgBox (defNCRFNum)




defNCRFNum = NCRFNumAr(0) & "-" & Format(CStr(Val(NCRFNumAr(1)) + 1), String(Len(NCRFNumAr(1)), "0"))




All Articles