Confusing number format in VBA (word&excel)

[Piet]

Hello there!
I stumbled over a very annoying and strange problem when formatting
numbers for output.
When I execute the macro
##
Sub test()
MsgBox (Format("0,5", "0.0000"))
End Sub
##
I get a messagebox with the expected value ("0.5000"), but when I type
instead
##
Sub test()
MsgBox (Format("0.5", "0.0000"))
End Sub
##
I get "5.0000" instead. Similarly, when I use the "#" format string
instead of the "0.00" I get values of "1" and "5". This is independent
of whether the comma or the dot is used as a decimal separator in the
corresponding settings in the control panel. However, my country
settings are german. When I switch to english/USA, the behaviour is
opposite.
It looks as if word and excel first parse the string of the
format-function and try to convert it to a number by throwing away
everything that is not part of a number and as if they don´t care
about the settings for the decimal separator in this process, but use
the settings that are common to the current country settings. Is it
possible to override that? I work in a WinNT 4.0 network. My PC at
home which runs under Win2K does not have this problem.
I hope someone can geive me a hint, because this is really confusing.
Regards
Peter

 

[Piet]

(e-mail address removed) (Piet) wrote in message I checked the behaviour on Win ME last evening (half-asleep ;-) ).
There, VBA seems to care about the current decimal settings, but the
behaviour is somewhat strange. When the "," is defined as separator,
both "0,5" and "0.5" are interpreted as numbers, but when the "." is
used, only "0.5" is regarded as a number, and "0,5" seems to be
converted to "5" and then displayed using the chosen format. So this
problem might be OS related and not depend on VBA. Does anybody have
information on this and/or have a fix for that on Win NT?
Best regards

Peter