In my last post, I created an array from formula text by using VBA’s Evaluate method, in order to roll my own FormulaArray function that displays the array returned by a formula, for documentation purposes.
In the course of this, I’ve discovered something a bit weird about how this method evaluates the arrays returned by a MID function.
Let’s use this snippet:
Sub EvaluateThis()
Dim var As Variant
var = ActiveSheet.Evaluate(ActiveCell.Formula)
End Sub
Dim var As Variant
var = ActiveSheet.Evaluate(ActiveCell.Formula)
End Sub
First, let’s look at how it handles an array generated by the COLUMN() function:
Now watch what happens when we use that array to split apart a string using the MID() function:
So if you push F9, you get an array, but if you use the Evaluate method you don’t…you just get the first letter. Is this weird, or am I missing the point?