Decimal Sign Replaced with ; Sign

Hi to all,

I'm trying to solve a strange problem in a piece of code.
I have a variable that is define as Double called STD. When i try to insert that variable in a formula the decimal sign (for me a comma "," because I'm Portuguese) gets converted to ";" (which is for me the separation sign for the expressions in excel formulas. ex: AND(A1>0;B1>0)=TRUE). The code is:

Public STD as Double
STD = ActiveSheet.Cells(n, NUMBEROFASSETS + 5).Value
ActiveSheet.Cells(m + 1, n + TOPPAIRS + NUMBEROFASSETS + 7).Formula = "=IF(AND(INDEX(" & SPREAD & "," & m + 1 & "," & n & ")>" & STD & "," & ANT & "=0)=TRUE,-1,IF(AND(INDEX(" & SPREAD & "," & m + 1 & "," & n & ")<-" & STD & "," & ANT & "=0)=TRUE,1,IF(AND(" & ANT & "<>0,SIGN(INDEX(" & SPREAD & "," & m + 1 & "," & n & "))<>SIGN(INDEX(" & SPREAD & "," & m & "," & n & ")))=TRUE,0,IF(AND(" & ANT & "<>0,INDEX(" & SPREAD & "," & m + 1 & "," & n & ")="""")=TRUE,0,IF(" & ANT & "<>0," & ANT & ",0)))))"

Can anyone help me preventing this automatic conversion?
Thanks in advance for any advice.

Edit: I must add, i have tried to convert the sign before using the variable in the formula but with no success. I tried this:

STD = SUBSTITUTE(STD,"","",""."")

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"")"

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)