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

>
> '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

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

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

>
> 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(xlListSeparator)

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(xlListSeparator) & "0" &
activeworkbook.application.International(xlListSeparator) & "0)")

Cheers,
Erik

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

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 ?

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

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

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

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.