Can I prevent rounding with the Format command?

[USAMax]

I need to return a value with five decimal places.

If a value is 1.999999, the Format command returns 2.
var = 1.999999
debug.print Format(var, "#.00000")

Returns 2

I need it to return 1.99999

Mathematically I can change the value but there has got to be a better way. Can rounding be disabled?

This value can be multiplied by as much as a million so it does mater. I know 2 is closer to the original value but this is what they want.

Thank you.

 

[Rick Rothstein]

As my Excel uses comma as decimal separator this worked for me
Debug.Print Mid(var, 1, InStr(1, var, ",") + 5)

So i think this should work for who uses dot as decimal separator
Debug.Print Mid(var, 1, InStr(1, var & ".", ".") + 5)

You should add what I show in red above just in case var is set to an integer value having more than 5 digits.

Edit Note
------------------------
Just saw Message #10 . Same comment as above making the code line this...

Debug.Print Mid(var, 1, InStr(1, var & Application.DecimalSeparator, Application.DecimalSeparator) + 5)

Although it might be better to assign the decimal separator to a variable to avoid the additional call to the Application object or, alternately, use a With..EndWith block for the decimal separator.

 

[USAMax]

Thank you JoeMo but your solution will display six digits to the right of the decimal and I need to show five only without rounding.

 

[USAMax]

Hi Rick, you have come through for me several times and I always appreciate your help. Sometimes the value will return a larger value, in those cases we will need a comma but most of the time it will be a very small number and we only want five digits.

I came up with the same solution but to me that is a work-around and I thought there would be another way.

As always, you help is very much appreciates.

 

[USAMax]

Marcelo,

I am very impressed with your creative idea! I brushed on something similar but never really considered it. I would have turned this back into a number but as it is being used in a UserForm for display only this may be the way to go.

Thank you!

 

[joeu2004]

Sometimes the value will return a larger value, in those cases we will need a comma but most of the time it will be a very small number and we only want five digits.

Not sure what you mean by "we only want five digits". I assume that "five digits" means "five decimal places", as you said originally.

I think this modification to Rick's solution does what you want:

Debug.Print Format(Int(100000*Value)/100000, "#,###.00000")
or
Debug.Print Format(Int(100000*Value)/100000, "#,##0.00000")

The difference is: if the integer part is zero, the first formula shows .12345 (no leading zero), which is consistent with your original format. The second formula shows 0.12345 (leading zero), which would be my preference. It's your choice, of course.

For localization purposes, write:

Debug.Print Format(Int(100000*Value)/100000, "#" & Application.ThousandsSeparator & "##0" & Application.DecimalSeparator & "00000")

 

[Marcelo Branco]

Marcelo,

I am very impressed with your creative idea! I brushed on something similar but never really considered it. I would have turned this back into a number but as it is being used in a UserForm for display only this may be the way to go.

Thank you!

You are welcome. Thanks for the feedback.

M.

 

[USAMax]

Rick,

You have helped me several times before and for that I thank you. I hated your answer but after searching I have found that there is no other way but to solve this mathematically.

Your help has been much appreciated!

 

[RoryA]

Purely as an alternative:

Format(Evaluate("TRUNC(" & v & ",5)"), "#,###.00000")

 

[USAMax]

Thank you Rory!