CODEWITHSUNDEEP
arraysvbaexcelexcel-formula
Greedo
Greedo

Reputation: 5543

How to evaluate array formulae with the Evaluate method

In VBA, the Evaluate() and [] methods can both be used to return arrays:

Dim i As Variant
i = Evaluate("{1,2;3,4}")
i = [{1,2;3,4}]

both lines set i to be a 2D array containing the numbers 1-4. An added functionality is that Evaluate(...)(1,2) returns the R1C2 indexed element of the array (i.e. 2 - [...](1,2) meanwhile errors)

I'm wondering whether there's any syntax for evaluating an array-returning worksheet function in the same way, e.g.

i = Evaluate("LEN(A1:A5)>3") 'or similar like [{LEN(A1:A5)>3}]

which should return the 1D array like {False,False,False,True,True} if I have text longer than 3 characters in A4 & A5, but it doesn't.

If not, is there another 1-liner way of evaluating an array-formula to return the full array? I'm looking for whatever approach gives the smallest character-count.

Upvotes: 8

Views: 2051

Answers (1)

Florent B.
Florent B.

Reputation: 42528

To return an array, you need to use a function that supports an array as a result.

The function usually used with VBA to evaluate an expression to an array is INDEX since it doesn't alter the values :

Dim data()
data = Evaluate("INDEX(LEN(A1:A5)>3,)")

Upvotes: 3

Related Questions