Since Excel 2013, Microsoft has given us a FORMULATEXT function, which if you point at a cell will do just that. Here’s a naughty snapshot of FORMULATEXT playing with itself in the corner:

I thought I’d have a go at writing a FormulaArray function to complement it, because when I’m building up a complicated formula that uses lots of array manipulation, then I like to document how all the different arrays within it fit together. (I was going to say “come together” there, but after that crack about FORMULATEXT playing with itself, I thought better of it. But now that I’ve said crack, I’m gonna throw caution to the wind and say wind too.)

Currently I document my formula beasts by either either array-enter a sub-part in the sheet with some notes, like this:

…which shows how my ExtractNumber formula works, or I enter the desired formula in one cell with a ShowFormula to the left and a hand-rolled hard-coded array to the right, like in this table where I’m documenting a few ways to dynamically generate consecutive integers:

I get that ResultArray manually, by clicking in the formula bar, pushing F9, copying the resulting evaluated array, then pasting it in another cell. Tedious. Especially when I later make a change to that sub-part, because then I get to do those steps all over.

So I started to roll my own FormulaArray function. I got a bit bogged down in the joining bit, but after about an hour of Googling, I *rediscovered* Nigel Heffernan’s code for joining two dimensional arrays. Which is *very* concerning, because I *discovered* it like just 10 days ago, and even wrote an extensive blog post on it right here. Senility is obviously setting in. If I start saying the same thing over and over like my mother does, just shoot me. If I start saying the same thing over and over like my mother does, just shoot me.

Anyways, Nigel’s function needs a 2D array. You can create an array from formula text by using VBA’s Evaluate method. If the formula returns a Row vector or a 2D vector, then Evaluate nicely turns it into a 2D vector. But here’s the rub: if the formula returns a Column vector, then Evaluate only gives us a 1D vector, which ain’t gonna wash with Nigel’s function:

So what we need to do is TRANSPOSE any formulas that would return Column vectors, because chucking a TRANSPOSE into the mix has the desired effect:

Note that I’m using the square brackets [ ] shortcut for Evaluate. I could just have easily done it like this:

Okay, so we know that **if** our formula string returns a Column vector, we’ve got to transpose it. But how can we tell that ahead of time? I can’t think of a way. So I just do this:

Function FormulaArray(Target As Range) As String

Dim strInput As String

Dim var2 As Variant

Dim lb As Long

strInput = Mid$(Target.Formula, 2)

var2 = ActiveSheet.Evaluate(strInput)

On Error Resume Next

lb = LBound(var2, 2)

If Err.Number <> 0 Then

var2 = Application.Transpose(ActiveSheet.Evaluate(strInput))

FormulaArray = Join2d(var2, ",", ";")

Else:

FormulaArray = Join2d(var2, ";", ",")

End If

End Function

So I evaluate the formula as if it’s a Row vector, then check if I’ve got 2 dimensions as a result. If not, it must have been a column vector, in which case I transpose it, then reevaluate it. Shame about the double evaluation, but I can’t think of a foolproof way to do it differenty, other than perhaps array entering the formula into a 2D range on the worksheet and looking at where the #N/A! errors fall.

Anyway, it seems to work just fine:

..unless you happen to be using Structured Table References, and your arguments happen to use the @ table notation to point at something on the same row:

…or unless you happen to have a formula with the INDIRECT function in it:

With the ThisTableRow thing, I guess I can just replace the @[SomeColumn] bit with the actual address, but I can’t think of easy ways around the INDIRECT thing. Anyone got any ideas?

Edit: Thinking about this some more, all I need to do is substitute the INDIRECT(SomeExpression) with whatever gets returned by RANGE(SomeExpression).value

Sample workbook:

FormulaArray_20141125

There’s a handy post over at Charles Williams’ site that talks about some other quirks of Evaluate that’s worth checking out:

https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/