Evaluate function with array formulas

Question

I like using the Evaluate function in VBA to return the results of a formula. It's especially nice because Evaluate will also return the results of array formulas. 

HOWEVER, today I am trying to use Evaluate with some array formulas and I am getting this error:

"Application-defined or object-defined error"

I did some troubleshooting to discover that this error only happens when I attempt to use an array formula. Otherwise it works just fine, just like it always did.

No matter what array formula I use I get this error. The same formula works in a cell, just not with Evaluate.

What happened?

tod

Answer 1

Your reply gave me the idea of looking at my ranges to make sure they were equal in size and that's where the problem was. 

thanx,

tod

Answer 2

tod wrote:

I am trying to use Evaluate with some array formulas and I am getting this error:

"Application-defined or object-defined error"

Obviously, it might be helpful to see the formula and any related values.  Perhaps the error is not at all what you imagine it to be.  VBA error messages are misleading, to say the least.

As you noted, for example, MsgBox Evaluate("=MIN(IF(A1:A10>0.5,A1:A10))") works as intended for me.  Evaluate seems to know to try that as array-entered.

I guess there is no semantic ambiguity in that example because there is no concept of parallel cells here.

The ambiguity is:  if the formula were normally-entered into B1:B10 (with absolute references $A$1:$A$10), for example, it would be interpreted as =MIN(IF(Ax>0.5,$A$1:$A$10)), where "x" is the row number of the cell with the formula.