Excel VBA - Append string with "0"'s

Posted on 2014-09-02
Last Modified: 2014-09-02
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")

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
Question by:tonelm54
    LVL 26

    Assisted Solution

    Why use a custom function, use the built-in Format() function.
    Format("0", "0000000")

    Open in new window

    LVL 47

    Accepted Solution


    in your example you define endRule but not StartRule

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


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


    Author Comment

    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
    LVL 47

    Expert Comment


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

    with 12 you get


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now