I cannot force excel vba to use application splitter in windows 10 while I can in Windows 7

Question

I was looking for two days to solve my problem, but so far nothing.

There are many (very many) vba excel tools developed where I work, and our regional settings on the PC define a comma as a decimal separator, but reports and data downloaded from our systems have a dot in the form of a delimiter. In these tools, when necessary, we simply installed it UseSystemSeparators = False, then DecimalSeparators = "."at the end of the macro we returned it back.

Now new people get Windows 10 PCs, and some of the tools fall into errors. I got Win10 from IT for testing and found out that no matter how I am configured in Excel settings, VBA Macro uses regional PC settings, and on the worksheet it still uses what is defined in Excel settings. The same file, the same test on my win7 computer, and if it is installed, then the local application settings are used on the sheet and vba macros.

Does anyone know what the reason is and how this can be fixed? I can find many workarounds, but all of them mean that the tools need to be transcoded, and there are so many that I still continue to find new tools that were developed and which were developed before I joined the company in March. Changing the common parameter ~ 300 for a PC is not a parameter, because it must be a comma as a decimal separator in normal mode.

Edit: just to make it more understandable, I will add the code:

Sub test()
    Application.UseSystemSeparators = False
    Application.DecimalSeparator = "."
    variable = "10.1"
    MsgBox CDbl(variable)
End Sub

Windows 7 - . , , . Windows 10 - , . "," . , "10,1" "10,1", . . ",", Excel ".".

Answer 1

( ) DecimalSeparator (,) ThousandsSeparator, (.) - Windows 10

:

, DecimalSeparator Excel, VBA:

Application.UseSystemSeparators = False
Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","

, DecimalSeparator , . , :

100.000.000,99

:

100,000,000.99

, , DecimalSeparator, VBA .

:

( Application.DecimalSeparator = ",", Application.UseSystemSeparators = True)

Sub TestSeparator()

Application.UseSystemSeparators = False
Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","

Debug.Print CDbl("100.99")

End Sub

10099, double 100,99 ( ), VBA DecimalSeparator of. ". DecimalSeparator.

:

VBA . , "" , , .

:

( Application.DecimalSeparator = ",", Application.UseSystemSeparators = True)

"100,000,000.99" A1 :

CDbl(Range("A1").Value)

" ". , , , . Replace, ThousandsSeparators DecimalSeparators :

CDbl(Replace(Replace(Range("A1").Value, ",", ""), ".", ",")

Answer 2

, :

Set ws = ThisWorkbook.Worksheets("Name of Worksheet")
ws.Cells.Replace What:="", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
Set ws = Nothing