Array of formulas (regional difference)
 

Following fragment works when the region is set for US, but fails for
Dutch

Dim x As Range
Dim y As Range
Dim w As Range
Set w = Range("AH4")
Set x = Range("AH5")
Set y = Range("AG1:AH1")

'Following works for both US and Dutch
x = "=if(x,0,0)"
y = Array("hello", "=w")

' Following works for US, but not in Dutch, why?
y = Array("hello", "=if(x,0,0)")

Array of formulas (regional difference)
 

'Following works for both US and Dutch
x = "=if(x,0,0)"
y = Array("hello", "=w")

' Following works for US, but not in Dutch, why?
y = Array("hello", "=if(x,0,0)")

The default argument separator for Dutch regional settings is ";"
For US the comma is used.

Try the following statement
y = Array("hello", "=if(x;0;0)")

regards,
Erik

Array of formulas (regional difference)
 

Thanks
So how do you recommend supporting both regions without duplicating
macros.

*** Sent via Developersdex http://www.developersdex.com ***

Array of formulas (regional difference)
 

Thanks
So how do you recommend supporting both regions without duplicating
macros.

*** Sent via Developersdex http://www.developersdex.com ***

Ken

This might help you to decide which formula to parse

activeworkbook.application.International(xlListSep arator)

It will give a ";" for NL and a "," for US

This one will work I guess and avoid duplication

y = Array("hello", "=if(x" &
activeworkbook.application.International(xlListSep arator) & "0" &
activeworkbook.application.International(xlListSep arator) & "0)")

Cheers,
Erik

Array of formulas (regional difference)
 

I only use the USA version, so I couldn't test. But I don't think I'd rely on
the default property of the range (.value) to do the conversion correctly.

Maybe this would work ok:

Option Explicit
Sub testme01()
Dim x As Range
Dim y As Range
Dim w As Range

Set w = Range("A4")
Set x = Range("A5")
Set y = Range("A1:b1")

'test 1
x.Formula = "=if(a9=1,0,1)"
y.Formula = Array("hello", "=g1")

'test 2
y.Formula = Array("hello", "=if(a9=1,1,0)")

End Sub

(I had no idea what x and w represented in the formula. I didn't think that
they were the same as your range variables.)



ken4capitola wrote:

Following fragment works when the region is set for US, but fails for
Dutch

Dim x As Range
Dim y As Range
Dim w As Range
Set w = Range("AH4")
Set x = Range("AH5")
Set y = Range("AG1:AH1")

'Following works for both US and Dutch
x = "=if(x,0,0)"
y = Array("hello", "=w")

' Following works for US, but not in Dutch, why?
y = Array("hello", "=if(x,0,0)")

--

Dave Peterson

Array of formulas (regional difference)
 

Thanks Dave
But your code has same issue as mine. See Eriks solution.

Erik
So why does following work in Dutch?
x = "=if(x,0,0)"

Wouldn't it fall into the same trap as the array ?

Array of formulas (regional difference)
 

What happened when you tried it?

Can you post what you used to try it?

Thanks,

ken4capitola wrote:

Thanks Dave
But your code has same issue as mine. See Eriks solution.

Erik
So why does following work in Dutch?
x = "=if(x,0,0)"

Wouldn't it fall into the same trap as the array ?

--

Dave Peterson

Array of formulas (regional difference)
 

Hi Dave,

The scratch sheet I'm using have all blanks. In the US regional setting
your program runs successfully. In Dutch, I'm getting Run-time error
'1004': Application-defined or object-defined error.

BTW, what I implemented in my code is shown below. The quantities
within the IF expression are complex so I wanted to stay as close to
the original IF syntax as possible. The Replacement string operation
changes a target pattern "\," with either a ";" or "," depending on the
region.

sub test
Dim ListSep As String
Dim BackComma As String
BackComma = "\,"
ListSep = Application.International(xlListSeparator)

'****** y = Array("hello", "=if(x,0,0)") becomes ....

y = array("hello", _
Replace("=IF(x \, 0 \, 0 ", BackComma, ListSep)
end

Array of formulas (regional difference)
 

What line causes the error in the code that failed?



ken4capitola wrote:

Hi Dave,

The scratch sheet I'm using have all blanks. In the US regional setting
your program runs successfully. In Dutch, I'm getting Run-time error
'1004': Application-defined or object-defined error.

BTW, what I implemented in my code is shown below. The quantities
within the IF expression are complex so I wanted to stay as close to
the original IF syntax as possible. The Replacement string operation
changes a target pattern "\," with either a ";" or "," depending on the
region.

sub test
Dim ListSep As String
Dim BackComma As String
BackComma = "\,"
ListSep = Application.International(xlListSeparator)

'****** y = Array("hello", "=if(x,0,0)") becomes ....

y = array("hello", _
Replace("=IF(x \, 0 \, 0 ", BackComma, ListSep)
end

--

Dave Peterson

Array of formulas (regional difference)
 

I think this problem only occurs when you try to insert formulas with
an array. In Europe, f you examine a formula in a spreadsheet you'll
see =if(x ; 0 ; 0), in US you'll see =if(x, 0 , 0). As far as I can
tell, Excel isn't smart enough to translate the delimiters when they
are stuffed into an array. Wiser people may have better explanations.

Try this:
control panel - regional settings - dutch(belgium)

Then run your program.