How to Format numbers like calculators do

[Stefano Gatto]

Hello all,

I would like to use the Format function to achieve the following:

1.234567 -> 1.23457
1.23456 -> 1.23456
1.2345 -> 1.2345
1.234 -> 1.234
1.23 -> 1.23
1.2 -> 1.2
1 -> 1

(maximum 5 digits after the decimal point)

Note that the last result has no dot.

I have tried Format(1,"#.#####") but I get "1." and I don't want the dot.

Can someone help me to find the correct pattern?

Thank you

Stefano Gatto, Geneva

 

[Karl E. Peterson]

Hello all,

I would like to use the Format function to achieve the following:

1.234567 -> 1.23457
1.23456 -> 1.23456
1.2345 -> 1.2345
1.234 -> 1.234
1.23 -> 1.23
1.2 -> 1.2
1 -> 1

(maximum 5 digits after the decimal point)

Note that the last result has no dot.

I have tried Format(1,"#.#####") but I get "1." and I don't want the
dot.

Can someone help me to find the correct pattern?

Sometimes, if you need a custom format, you need to produce it yourself.
For example, you could accomplish the above with a routine like this:

Public Function MyFormat(ByVal Num As Double) As String
Dim retval As String
Const MyFmt As String = "0.#####"
retval = Format$(Num, MyFmt)
If Right$(retval, 1) = "." Then
MyFormat = Left$(retval, Len(retval) - 1)
Else
MyFormat = retval
End If
End Function

Note that this doesn't account for non-dot decimal characters. If that's
something you need to consider, there are other approaches. The quickest
way to determine the localized decimal character is:

Mid$(Format$(1.1, "0.0"), 2, 1)

Later... Karl

 

[Helmut Weber]

Hi Karl,

how about:

application.International(wdDecimalSeparator)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Karl E. Peterson]

application.International(wdDecimalSeparator)

Heh, there ya go. Is that available throughout Office, or just within
certain Application objects? (Not knowing, and too lazy to ascertain, I
tend to write code that doesn't rely on externals when unnecessary. <g>)

 

[Helmut Weber]

Hi Karl,

I don't know either.

It just occurred to me.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Stefano Gatto]

Many thanks Karl for your suggestion. I was actually looking for a direct VB
function which I believe would have been faster that VB code itself, since I
need to format about 12000 numbers in a report the program produces... Anyway
nowadays this is not a criteria anymore so I am going to take this suggestion
and the user will wait a bit longer.

Interesting to note that in VB6 this is not a problem!!
In VB6, Format(123, "###.#")="123" and not "123."
In VBA the decimal separator is there.

See http://msdn2.microsoft.com/en-us/library/wb216dct.aspx

Thanks again.

Stefano Gatto

 

[Karl E. Peterson]

Many thanks Karl for your suggestion. I was actually looking for a
direct VB function which I believe would have been faster that VB
code itself, since I need to format about 12000 numbers in a report
the program produces... Anyway nowadays this is not a criteria
anymore so I am going to take this suggestion and the user will wait
a bit longer.

I don't think it will be a measurable difference. Certainly not a noticable
one.

Interesting to note that in VB6 this is not a problem!!
In VB6, Format(123, "###.#")="123" and not "123."
In VBA the decimal separator is there.

Umm, just tried that in VB6, and it certainly did display the decimal.
Worse still, if the number is 0, *all* you get is the decimal!

See http://msdn2.microsoft.com/en-us/library/wb216dct.aspx

That page shows your example in (the abomination known as) "VB 2005", but
not VB6.