I cannot make excel vba to use application separator in windows 10 while I can in windows 7

4
votes

I've been searching for two days to solve my issue but so far nothing.

There are many (Very many) vba excel tools developed where I work and our regional settings in PC-s determine comma as decimal separator but reports and data downloaded from our systems have dot as decimal separator. In these tools, when needed we just have set UseSystemSeparators = False then DecimalSeparators = "." and at the end of the macro reverted back.

Now new people have been getting windows 10 pcs and some of the tools run into errors. I got a Win10 pcs from IT to test and found out that no matter how I set in Excel settings VBA Macro uses PC regional settings while on the sheet it still uses what is determined in Excel settings. Same file, same test in my win7 pc and if set then both on sheet and vba macro use local application settings.

Does anyone know what is the reason and how it could be fixed? I can figure many workarounds but all of these mean that the tools need to be re-coded and there are just so many of these that I still keep finding out new tools used and what were developed before I joined the company in March. Changing all ~300 PC-s regional setting is not an option because it needs to be comma as decimal separator normally.

Edit: just to make it bit more clear I'll add some code:

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

Under Windows 7 - no problem. The variable containing a string can be converted. Under Windows 10 - Run-time error, Type mismatch. Both PCs have "," as system separator. The funny thing is that when I change "10.1" to "10,1" then that works on both PC-s. Settings for both PC-s are same. System uses "," as separator and Excel is set to use ".".

2
vbaexcel
@codegenerator Is this data that has been generated as a non-Excel file, and then being imported into Excel? If so, how do you get the data into Excel? Using VBA, a better method would be to use the Workbook.OpenText method which will allow you to specify the thousands and decimal separators as it exists in the data being imported. It seems to me that this will be a more bulletproof method, especially going forward. – Ron Rosenfeld
@codegenerator your test with cdbl does not show that Excel is not using a system separators. Write the value to a worksheet cell and see what happens. VBA is not Excel – Ron Rosenfeld
@codegenerator also, you may have to change the thousands separator – Ron Rosenfeld
#codegenerator I do not believe you can change the decimal separator in VBA. I don't know why it worked in 2007. Possibly something in your code or data that you're not sharing with us. As far as I know, maybe a has always used only the dot as a decimal separator – Ron Rosenfeld
@codegenerator I cannot reproduce your problem in W10 / Excel 2016 when I set my system separators to be comma for decimal and space for thousands (digits). I copied/pasted your code and the MsgBox show 10,1 There must be something different about your environment. (If I use dot for the digit separators, then 10.1 gets interpreted as 101; that's why I made it a non-dot). What are your system regional decimal and digit separators when things don't work? – Ron Rosenfeld

2 Answers

1
votes

This answer is based on your system (default) DecimalSeparator being a comma (,) and ThousandsSeparator being a point (.) - using Windows 10

Explanation:

I've done a little testing and found that the following functions only change the DecimalSeparator within Excel, not within VBA:

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

This means that when you change the DecimalSeparator by using these lines of code, it will simply update the values in your workbooks to be displayed in the format you are specifying. For example if your system format is:

100.000.000,99

Then the lines of code above would convert the values to:

100,000,000.99

However, when you run the code above and try to use a string that uses the non-system format for the DecimalSeparator then VBA will not recognise it.

Example:

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

Sub TestSeparator()

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

Debug.Print CDbl("100.99")

End Sub

This code will return 10099, not the expected double of 100,99 (in your system format) because VBA does not see the DecimalSeparator of "." as a DecimalSeparator.

Solution:

When referencing doubles within VBA you will need to always use the computer's system separators for thousands and decimals. If the doubles are being imported as strings you will need to use Replace to convert the string into a format that uses system separators.

Solution Example:

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

If you have the string of "100,000,000.99" in cell A1 and use the following code:

CDbl(Range("A1").Value)

It will produce a "Type mismatch" error. However if you convert this into a string that uses your system separators then no error will be given. You will need to use two Replace functions though, one to remove the ThousandsSeparators and one to convert the DecimalSeparators to your system ones:

CDbl(Replace(Replace(Range("A1").Value, ",", ""), ".", ",")
-1
votes

Perhaps this:

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