excel - 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?

Answer 1

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