[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

Excel VBA - Append string with "0"'s

Im trying to make a function to append a number with 0's, which I know cant work as an interger seeing as 00000 will recognize as 0, so trying to put it as a string.

I have two problems with my code:-
    Dim endRule As String
    endRule = Int("0")
    
    While Len(Str(startRule)) < 7
        startRule = Str(startRule & "0")
    Wend

Open in new window


1 - On first run startRule becomes " 0" (space at the front of the string).
2 - the "0" is never appended to the string, it always is " 0" (which the unknown space from issue #1).

My code runs great if it starts with a number apart from 0.

Does anyone have any suggestions?

Thank you
0
tonelm54
Asked:
tonelm54
  • 2
2 Solutions
 
MacroShadowCommented:
Why use a custom function, use the built-in Format() function.
Format("0", "0000000")

Open in new window

0
 
Rgonzo1971Commented:
Hi,

in your example you define endRule but not StartRule

myString = "1"
myNewString =Left(myString & "0000000";7)

'or

myInt = 1
myNewString =Left(Format(myInt, "0") & "0000000";7)

Regards
0
 
tonelm54Author Commented:
Good afternoon,
MacroShadow: Using the format fills the wrong way, for example if run for the number 1, it becomes "0000001" instead of "1000000", I could use StrReverse, so for example:-
startRule = StrReverse(Format(startRule, "0000000"))

Open in new window

Which seems to work ok, but seems a bit of a hack :-S, but Ill go with it.

Rgonzo1971: Works as well, thank you
0
 
Rgonzo1971Commented:
Attention

if you number is bigger than 9 with your hacked code you will get a wrong answer

with 12 you get
2100000

Regards
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now