convert an integer variable to a string with leading zeros in VBA

Question

For starters, there are LOTS of questions that have been asked with this topic. However all the ones I kept clicking on were in languages other than VBA and I did not understand the syntax of those languages.

When I did a google search I found this answer which seemed promising. AH FIDDLE STICKS! I just realized that answer for VB and probably explains why its not working in my VBA

Situation

I have a variable called DimScale that is an integer. I want to create a string called DimName that will start with "mm-" and be following by the integer from DimScale with leading 0s such that there are a minimum of characters after "mm-".

IF DimScale = 25
Then DimName = "mm-0025"

IF DimScale = 235
Then DimName = "mm-0235"

Note Dimscale >=1 and <= 9999

What I have tried

Dim Dimscale as Integer
Dim Dimension_Style_Name as String

String.Format("{0:0000}", DimScale)

Dimension_Style_Name = DimScale$
Dimension_Style_Name.Format("{0:0000}", DimScale)

I have read the gist too that Dimscale get converted to a string and then is sent through a loop of adding a leading zero until the length of the string equals the 4 characters in my case for the integer part.

I have also seen the case with IF statments where IF Dimscale <10 then "000"& If Dimscale <100 then "00"& etc.

Is there a way to do it like like the VB method in VBA?

You might want to peek at [this](https://stackoverflow.com/a/14534361/1188513) if you want to be able to use the .NET `String.Format` syntax in VBA. — Mathieu Guindon, Aug 17 '18 at 16:44

DisplayName · Accepted Answer · 2018-08-17T16:53:56.300

16

maybe:

DimName = "mm-" & format(DimScale,"0000")

As per @MathieuGuindon valuable (as usual) contribution:

Format (fully-qualified VBA.Strings.Format) takes a Variant parameter, and returns a Variant - you can also use its little brother Format$, which takes a String and returns a String, eliminating implicit conversions along the way

edited Aug 17 '18 at 16:53

answered Aug 17 '18 at 16:36

DisplayName

13,283
2
11
19

I hate it when you are all around the answer, but just a few keystrokes off in the formatting! Thanks for the quick response and I will accept as soon as it lets me – Forward Ed Aug 17 '18 at 16:39
somebody had mercy on me – DisplayName Aug 17 '18 at 16:39
@ForwardEd, it happens to everybody here – DisplayName Aug 17 '18 at 16:41
2

Side note: `Format` (fully-qualified `VBA.Strings.Format`) takes a `Variant` parameter, and returns a `Variant` - you can also use its little brother `Format$`, which takes a `String` and returns a `String`, eliminating implicit conversions along the way. – Mathieu Guindon Aug 17 '18 at 16:43
@MathieuGuindon, thanks. I guess you were typing another page of your _book_ here.. But if you don't mind I'd add that in my answer (too few upovotes till now...). – DisplayName Aug 17 '18 at 16:47
1

@MathieuGuindon as a complete tangent. Rubberduck is the command I taught my dog to formally release her from whatever command she was following. Was looking for something the general public would not guess. – Forward Ed Aug 17 '18 at 16:55
@MathieuGuindon, _WTH_ I take a look at this _Rubberduck_ and ... how to exploit it? – DisplayName Aug 17 '18 at 16:56

score 0 · Answer 2 · edited Mar 10 '22 at 17:25

I had a similar need to apply leading zeros ( 12 to 00012 ) to a specified range. But everything I'd found thus-far used an iterative cell-by-cell approach. I found an older but still valuable posting from SiddHarth Rout. His posting pertains to case conversion ( lower to upper case ) but I found it adapted nicely to applying leading zeros.

Here is link to SiddHarth's posting: Convert an entire range to uppercase without looping through all the cells

The need

Here is the adaptation for applying leading zeros to a specified range:


Sub rngLeadingZeros(rng As Range, nbrZeros As Integer)
' Add leading zeros to a specified range.

    Dim strZeros As String
    Dim x As Integer

    'build string as required for text() function:
    For x = 1 To nbrZeros
        strZeros = strZeros & "0"
    Next

    'make sure the range is formatted as text:
    rng.NumberFormat = "@"
    
    'apply the format to the range:
    rng = Evaluate("index(text(" & rng.Address & ", """ & strZeros & """),)")

End Sub

Sub testZ()
    With ActiveSheet
        rngLeadingZeros .Range("e3:e9"), 5
    End With
End Sub

The Result

Sincere thanks for the assist. – Wunderdawg57 Mar 11 '22 at 19:39 — Wunderdawg57, Mar 11 '22 at 19:39

convert an integer variable to a string with leading zeros in VBA

2 Answers2

Linked

Related