Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

excel - convert an integer variable to a string with leading zeros in 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...