Decimal Sign Replaced with ; Sign

**DonkeyOte** · 12-09-2009, 06:00 AM

Once approach might be to switch to

.FormulaLocal

Note you would then use ; delimiter etc in your formula string as you would natively, eg:

.FormulaLocal = "IF(AND(A1=1;A2=2);""x"";""y"")"

**lopiner** · 12-10-2009, 08:20 AM

DonkeyOte and chrns12, thanks for the suggestions.

chrns12 wrote me this:

Not sure if this would do it, but try putting a forward slash in front of the comma between the quotes.

Example: "/,"

Depending on what you are working with it tells VBA to take the comma literally. Since it's getting converted to a semicolon I'm assuming that the comma has a special meaning within the syntax you are working with.

Let me know if that worked.

I have worked this out like this.
First i have declared the variable as string:

Public STD As String

Then i used replace like this:

STD = Replace(STD, ",", ".")

This as solved my problem.
I think this is a big fault for non English language excel because VBA will always convert signs and some of them totally change the meaning of variables (like the decimal sign).
Thanks again for your suggestions.
Regards

**DonkeyOte** · 12-10-2009, 08:32 AM

VBA is heavily american-ised hence the issue.

When you write a comma back to native XL using .Formula XL when "translating" will interpret that as an American argument delimiter hence this is subsequently translated to ; natively in your locale.

As you say by changing the , to . using the standard Formula based approach XL when translating interprets that as a decimal delimiter (US) and it is thus subsequently translated back to your locale specific decimal delimiter - the comma.

On the other hand the FormulaLocal property won't conduct any translation of your formula string - the string will be literally inserted as requested without alteration.

A quick way to illustrate on your own client would be the following:

Sub Example
Dim dblValue As Double: dblValue = "29,2"
Cells(1,"A").Formula = "=IF(" & dblValue & ",1)"
Cells(2,"A").FormulaLocal = "IF(" & dblValue & ";1)"
Cells(3,"A").Formula = "=IF(" & Replace(dblValue,",",".") & ",1)"
End Sub

You will find the first example returns 2 as the formula translates into

=IF(29;2;1)

ie , interpreted as US based argument delimiter - translated to ;

whereas the latter two both return 1 as both are inserted as

=IF(29,2;1)

FormulaLocal - no translation of string

Formula Replace method - US based decimal delimiter (.) translated to locale specific delimiter (,)

Which approach you adopt is obviously down to your own personal preference.... though of course if you have thousand separators (.) in your values the Formula Replace method becomes further convoluted (ie 2 replace calls required - in correct order)

**lopiner** · 12-10-2009, 08:47 AM

DonkeyOte,
Thanks for the extended explanation and methods to solve this problem. I'm going to tag this thread in hope it helps others with the same problem. This took me a lot of time :D