Decimal separators in formulas

[Somatophylax]

I have a VBA application in Excel that puts formulas in various cells. For
example, I need cells that look like this:
=IF(D4=0,0,D3/D4)
This is pretty simple. But in some cases, I need to put an actual number
from a variable.

For example, I have this line of code:
...Cells(A1).Value = "=IF(RC[+3]=0,0," & nMyVar & "/RC[+3])"

If nMyVar = 5, then I get this in cell A1:
=IF(D4=0,0,5/D4)
And if nMyVar = 5.5, I get this in cell A1:
=IF(D4=0,0,5.5/D4)

Again, this is pretty simple.. but for a little thing. Our application runs
on PCs which have different regional settings for the decimal separator. So
for those who use a comma (,) as a separator, the formula becomes:
=IF(D4=0,0,5,5/D4)

As you can see, the number of 'commas' in there will mess up the formula.
When I use RecordMacro to do it manually, it will record it with a period (.)
instead of a comma. But then if I program this, it won't work. Also, the
RecordMacro will use semi-colon (; as the IF(; separator... (that's what it
says in Excel's help file).. But again it won't work... My Excel will only
accept commas for the IF (,,) separator.

Any ideas anyone????

Thanks
Somatophylax

 

[Frank Kabel]

Hi
use:
Cells(A1).formulaR1C1= "=IF(RC[+3]=0,0," & nMyVar & "/RC[+3])"

 

[sebastienm]

Hi,

Try using the Formula property of the Rangeinstead of Value:
Cells(A1).FormulaR1C1 = "=IF(RC[+3]=0,0," & nMyVar & "/RC[+3])"

Similar properties:
- Formula: A1 style, in the language of the marco
- FormulaR1C1: R1C1 style, in the language of the macro
- FormulaLocal: A1 style, in the language of the user
- FormulaR1C1Local: R1C1 style , in the language of the user.

Regards,
Sebastien

 

[Stephen Bullen]

Hi Somatophylax,

For example, I have this line of code:
...Cells(A1).Value = "=IF(RC[+3]=0,0," & nMyVar & "/RC[+3])"

If nMyVar = 5, then I get this in cell A1:
=IF(D4=0,0,5/D4)
And if nMyVar = 5.5, I get this in cell A1:
=IF(D4=0,0,5.5/D4)

The problem is that when setting formulas for ranges, we always need to
use the US-english function names and number formats (regardless of
whether we're using .Value, .Formula or .FormulaR1C1). If we don't
explicitly tell VBA how to convert a number to a string (as you're
doing here), it will use the regional settings formats by default.

Together, that means that whenever we build formula strings, we have to
*explicitly* tell VBA to convert the number to a string using US number
formats. We do that using the Str() function, so your code should be:

...Cells(A1).Value = "=IF(RC[+3]=0,0," & Str(nMyVar) & "/RC[+3])"

There's much more about these types of issue in my 'International
Issues' chapter of John Green's "Excel 2002 VBA Programmer's
Reference", which can also be read online on my web site, at:
http://www.oaltd.co.uk/ExcelProgRef/ch22/

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk