Issue regarding Regional settings

[T.Mad]

Hi there,
I am programming in VBA having set up the Greek Regional settings. This
means that in Greece we use “,†instead of “.†for decimals and “.†instead
of “,†for thousands. I had to pass an excel formula via VBA which is:

Dim num as Double

Num=Cells(1,2).value ‘ this number is 10.1 (10,1 with Greek symbols)
Cells(1,1).formula = “=if(“ & num & “=0,0,5)â€

and I got an error message. I also used this formula:

Cells(1,1).formula = “=if(“ & num & “=0;0;5)â€

Which is the exact way to input the IF function ( to Excel with Greek
regional settings but again I got this error. Can anybody give me a good
piece of advice?
Many Thanks

 

[Rick Rothstein]

While this doesn't answer your regional setting question, why would you want
to put the *value* in B1 in your formula's comparison... wouldn't you want
B1 itself to go there? Putting the regional setting aside, I would think
your formula assignment should be this...

Cells(1, 1).Formula = “=IF(B1=0,0,5)â€

or, if your arguments to Cells(1,2) are really variables, then this...

Cells(1, 1).Formula = "=IF(" & Cells(R, C).Address & "=0,0,5)

where you can use the optional arguments of the Address property to set the
relative/absolute row/column references.

 

[Abdul]

in an English language version (excel 2007 with win Xp)
the following works without any error

Num = Cells(1, 2).Value
Cells(1, 1).Formula = "=If(" & Num & "=0,0,5)"

Please press "YES" if this post is helpful
Thanks,


Abdul

 

[T.Mad]

Hi,
Thanks for the answer, because this was just a simple example. Huge amount
of data is collected, through out the spreadsheet and many intermediate
calculations are taking place. I wonder if there is a vba function converting
the value of a variable from the local to the international format?

 

[Rick Rothstein]

You are going to have to wait for someone else who is knowledgeable about
such things for that question... I have never had to deal with international
issues in any of my programming throughout the years.

 

[JLGWhiz]

I don't know if there is anything useful for your specific problem, but this
site gives a lot of info on regional settings.

http://www.microsoft.com/resources/...stom_workspace_regionalsettings.mspx?mfr=true

I would think that once the setting is change to Greece, that you should be
able to use all local symbols and characters without creating error
messages. But, I don't know that for sure.

 

[T.Mad]

You just have to use the string function, str( variable ).
Thus:
Dim num as Double

Num=Cells(1,2).value
Cells(1,1).formula = “=if(“ & str(num) & “=0,0,5)â€