Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

vba

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?

like image 996
Forward Ed Avatar asked Aug 17 '18 16:08

Forward Ed


People also ask

How do you convert integers to string with leading zeros?

To pad an integer with leading zeros to a specific length To display the integer as a decimal value, call its ToString(String) method, and pass the string "Dn" as the value of the format parameter, where n represents the minimum length of the string.

How do I keep a zero in front of a number in Excel VBA?

For starters, let's see how you can put 0 in front of a number in Excel, for example type 01 in a cell. For this, simply change the cell format to Text: Select the cell(s) where you want to prefix numbers with 0. Go to the Home tab > Number group, and select Text in the Number Format box.

How do I convert a value to a string in VBA?

This example uses the CStr function to convert a numeric value to a String. Dim MyDouble, MyString MyDouble = 437.324 ' MyDouble is a Double. MyString = CStr(MyDouble) ' MyString contains "437.324".

How do you add leading zeros in Excel and convert to text?

You can type an apostrophe (') in front of the number, and Excel will treat it as text.


1 Answers

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

like image 91
DisplayName Avatar answered Sep 18 '22 22:09

DisplayName