Add leading zeroes in Function

[hyoung3]

Hi, I am trying to format a number to add leading zeroes in a function I have created. The input Integer will have a maximum length of 5 characters, but the output must be five characters with the proper number of leading zeroes. I tried using the format function (ie. Format(Strike, "00000"), but the output only gives me the input strike. Here is an example:

Public Function OSI(Strike As String)    
    'Declare variable to format Dollar Strike
    Dim IntDollar As Single
    Dim aDollar As Single
        
    'Dollar Strike format
        IntDollar = Int(Strike)
        aDollar = Format(IntDollar, 00000)
             
    'Output
        OSI = aDollar
    
End Function

If the Strike input equals 25.5 then the OSI output should be 00025. With this I am only getting 25.

I appreciate the help!

 

[gsistek]

Try putting quotes around the 00000.

 

[njimack]

Most importantly, you need quotes around the 2nd argument of the Format function. Nearly as importantly, you've got several redundant steps...

Public Function OSI(Strike As String)
       
    OSI = Format(Int(Strike), "00000")
    
End Function

 

[Mohammad Basem]

Try,

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Dim aDollar As String[/COLOR][/SIZE][/FONT]

instead of

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Dim aDollar As Single[/COLOR][/SIZE][/FONT]

 

[hyoung3]

Thanks for the help all! I got it to work!

 

[Mohammad Basem]

Thank you for the feedback!

 

[Scott Huish]

Why do you need a UDF? This can be done with standard Excel functions:

=TEXT(INT(A1),"00000")