using decimal separator in VBA on computers with different language settings causing errors

[PeteWright]

Hello everybody,
as you can see in the topic title I get issues with the decimal separator on different computers.

The current situation:
I write and test VBA code on my computers (home desktop and laptop / work laptop) which all have Excel 365 (Personal / Business) English installed.

The task:
My VBA Excel-files should work on my colleagues computers running Windows/Excel German

The problem:
Different language settings use different decimal separators and although Excel formulas should be and in fact are translated from EN to DE and back, the VBA code isn't.

The challenge:
Write code that works on any machine using MS Excel

This is just an example:

Sub TEST()
   Dim str As String
   Dim dbl As Double
   Dim tmp As Variant
   str = "0.75"
   tmp = CDbl(str)
   MsgBox tmp
End Sub

The same VBA code results in different output on my computers.
On my home computer running Windows 10 Pro x64 English with MSO 365 Personal English I get "0.75"
On my work laptop running Windows 10 Enterprise x64 German with MSO 365 Business English I get "75"

Well, there is nothing more to say, I need some help in writing my code to work everywhere.

Any help or advice highly appreciated.

Kind regards
Pete

 

[mikerickson]

Try either

str = "0" & Application.DecimalSeparator & ".75"
' or
str = Format(0.75, "general")

 

[Alex Blakenburg]

I think we need a more realistic example of the scenario.
If you are reading a text string in US format and wanting to convert it to a number that doesn't use the regional setting to convert it then you could try just using Val()
eg:

Sub TEST()
   Dim str As String
   Dim dbl As Double
   Dim tmp As Variant
   str = "0.75"
  Debug.Print Val(str)
  ' or if there are US thousand delimiter
  str = "1,234.75"
  Debug.Print Val(replace(str,",",""))

If you have a trailing negative that will need to be handled as well as Val only converts a leading negative symbol

If you are not doing a calculation with it simply writing the text string to a cell will do the necessary conversion.