Issue regarding Regional settings

T

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
 
R

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.
 
A

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

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?
 
R

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.
 
T

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)â€
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top