Array Formulas in VBA

[UniMord]

I'd like to count the number of errors in Selection.

The most straightforward way, it seems to me, would be to use a single array formula: {=SUM(0+ISERROR(SELECTION))}

This can be entered into a cell with Selection.FormulaArray, but I don't want to enter it into a cell, I need the value inside a subroutine.

In short: can an Array formula be used in VBA without actually entering it into a cell, or do I have to go the loop route? (Or are there other options?)

 

[sous2817]

I think you can use Evaluate to get what you want. If I recall correctly, Evaluate treats the formula as an array...

 

[Juan Pablo González]

Yeah, use the Evaluate method. That will work just fine.

 

[sous2817]

Something like this worked on my test sheet:

Dim i As Long

i = Application.Evaluate("SUM(0+ISERROR(A1:A10))")

 

[UniMord]

Thanks a million, guys! Too bad VBA Help couldn't be troubled to document this very cool feature of Evaluate. For that matter, none of my VBA books mention it either. If it's not a closely guarded secret, can you please tell me where you learned that?

 

[sous2817]

Show us the handshake first!