VBA: Set decimal places based on variable?

[cbrf23]

I'm looking for a way to set the decimal places for text i'm outputting by counting the decimal places of another variable.

Im thinking I could count the length of the input, and figure out how many decimals it is, then set the format of the output based on that. But I'm not 100% sure how...

Maybe something like:

intInLen = Len(dblTextIn) <--length of input number
intInDec = InStr(dblTextIn,".") <--location where first decimal occurs
intDec = intInLen - intInDec <--how many decimal places I need
strTextOut= Format(dblTextOut, ".000"[<--zeros based on intDec])<--but how??

I feel like there has got to be a better way than what I'm thinking above...

I just need to set decimal places of strTextOut to be the same as my input.
Ex:
If my input is 50.125:
I want strTxtOut format to be set to ".000"

Or if my input is 50.12
I want the format for strTxtOut to be set to ".00"

Or if my input is 0.5625
I want the format for strTxtOut to be ".0000"

Anyone know how best to do this?

 

[Rick Rothstein]

strTextOut= Format(dblTextOut, ".000"[<--zeros based on intDec])<--but how??

Anyone know how best to do this?

This statement should do what you want...

strTextOut = Format(dblTextOut, "." & String(intDec, "0"))

 

[njimack]

Untested code, combining your several lines into one and using REPT

strTextOut = Format(dblTextOut, "0." & WorksheetFunction.Rept("0", Len(dblTextIn) - InStr(dblTextIn, ".")))

 

[cbrf23]

Rept is exactly what I was looking for. Thanks!

 

[Rick Rothstein]

Rept is exactly what I was looking for. Thanks!

Just to point out, we can combine all your lines of code into a single statement using VB's own built-in String function instead of calling out to the worksheet's REPT function (I thought you had used individual statements for use later on in your code)...

strTextOut = Format(dblTextOut, "." & String(Len(dblTextIn) - InStr(dblTextIn, "."), "0"))

 

[cbrf23]

Actually Rick, I didnt even see your original response, but that does the trick and its a native funciton. Very cool. Thanks guys.

I just set the variables for length and "." location because then I can use them elsewhere and even if I dont, its easier for me to keep track of the code and edit it later.

 

[Scott Huish]

Or you could just see if the integer part of the number is 0:

Sub test()
Dim x As Double, y As String
x = 0.13475
y = x
If Int(x) = 0 Then y = Mid(y, 2)
MsgBox y
End Sub

 

[Scott Huish]

If you don't want the initial 0 stripped off, such as in 0.5437, just assign the numeric value to the string variable.