Using Evaluate method in VBA to write the formula result to worksheet cell

[MahTah]

I can use "Evaluate" method to write the formula's result into a worksheet cell for a function like:

ActiveSheet.Range("C27") = Evaluate("MAX('Test Data'!K:K)")

which works fine and I get the value in the cell "C27"

but when I try to use it for a formula like this:

ActiveSheet.Range("C16").Value = Evaluate("AVERAGEIF('Test Data'!B:B; MIN('Test Data'!B:B); 'Test Data'!I:I)")
OR
ActiveSheet.Range("C21") = Evaluate("=SLOPE('Test Data'!I:I;'Test Data'!D:D)")


I get "#VALUE!" as the result of evaluate method in cell "C16". This is while the formula itself works fine if I use it as a worksheet formula in the cell rather than using it in VBA!

1. What am I doing wrong here?
2. Beside this problem, how should I use the evaluate method with an array function?

Any help would be appreciated!

 

[taurean]

I think it is locale specific argument separator which is causing the issue. Make following changes to your evaluate code and test. Change the semicolon to comma and see.
ActiveSheet.Range("C16").Value = Evaluate("AVERAGEIF('Test Data'!B:B, MIN('Test Data'!B:B), 'Test Data'!I:I)")
OR
ActiveSheet.Range("C21") = Evaluate("=SLOPE('Test Data'!I:I,'Test Data'!D:D)")

Evaluate function can handle array functions (CSE in user interface). No different setting is required as far as I know.

 

[MahTah]

Thanks taurean,
Changing the semicolons to comma worked for those two functions but I still get the same "#VALUE!" result for a function array like:

ActiveSheet.Range("I16").Value = Evaluate("=AVERAGE(IF('Test Data'!B:B=MIN('Test Data'!B:B), IF('Test Data'!I:I<>"",'Test Data'!D:D)))")

Do you see any point also here!?

 

[MahTah]

Thanks taurean,
Changing the semicolons to comma worked for those two functions but I still get the same "#VALUE!" result for a function array like:

ActiveSheet.Range("I16").Value = Evaluate("=AVERAGE(IF('Test Data'!B:B=MIN('Test Data'!B:B), IF('Test Data'!I:I<>"",'Test Data'!D:D)))")

Do you see any point also here!?

 

[taurean]

Thanks taurean,
Changing the semicolons to comma worked for those two functions but I still get the same "#VALUE!" result for a function array like:

ActiveSheet.Range("I16").Value = Evaluate("=AVERAGE(IF('Test Data'!B:B=MIN('Test Data'!B:B), IF('Test Data'!I:I<>"",'Test Data'!D:D)))")

Do you see any point also here!?

Yes. This formula unlike previous ones contains double quotes ("") which can make situation tricky. Wrap the double quotes in a pair like below and test.

ActiveSheet.Range("I16").Value = Evaluate("=AVERAGE(IF('Test Data'!B:B=MIN('Test Data'!B:B), IF('Test Data'!I:I<>"""",'Test Data'!D:D)))")

Evaluate function does not raise a runtime error but just returns result albeit erroneous.