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.
To calculate multiple results by using an array formula, enter the array into a range of cells that has the exact same number of rows and columns that you'll use in the array arguments. Select the range of cells in which you want to enter the array formula. Enter the formula that you want to use.
A CSE formula in Excel is an array formula that must be entered with control + shift + enter.
You must press the Ctrl+Shift+Enter shortcut to complete an array formula. Every time you edit an array formula, the braces disappear and you must press Ctrl+Shift+Enter again to save the changes.
An array in Excel is a structure that holds a collection of values. Arrays can be mapped perfectly to ranges in a spreadsheet, which is why they are so important in Excel. An array can be thought of as a row of values, a column of values, or a combination of rows and columns with values.
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,)")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With