VBA - Long Array Formula via Application.Evaluate
Let's say we have a long formula stored in cell A1 :
=SomeArrayFunction(
IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X 01",
"part_one"),
IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X 02",
IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X 03",
"part_two"))
)
which uses the following VBA function
Public Function SomeArrayFunction(sOne As String, sTwo As String) As Variant
Dim V() As Variant
ReDim V(1 To 2, 1 To 1)
V(1, 1) = sOne
V(2, 1) = sTwo
SomeArrayFunction = V
End Function
returns a 2 × 1 array.
Now when I call this VBA function
Public Sub EvaluateFormula()
Dim vOutput As Variant
vOutput = Application.Evaluate(Selection.Formula)
If VarType(vOutput) >= vbArray Then
MsgBox "Array:" & vbCrLf & vOutput(1, 1) & vbCrLf & vOutput(2, 1)
Else
MsgBox "Single Value: " & vbCrLf & vOutput
End If
End Sub
after selecting cell A1 , I get an error because Application.Evaluate cannot handle formulas longer than 255 characters (see for example VBA - Error when using Application.Evaluate on Long Formula ). On the other hand, if I write
vOutput = Application.Evaluate(Selection.Address)
instead (as suggested in the link above) then this works fine. Except for the fact that the array is no longer reconstructed, that is, MsgBox "Single Value:" is called instead of MsgBox "Array:".
So my question is, how can I evaluate long formulas (returning arrays) using VBA?
Edit: Let me emphasize that I need this to work when I only select one cell that supports the formula (not an area or multiple cells). And I didn't enter it as an array formula (i.e. No curly braces):
Edit2:Let me answer the question why: my current job requires me to have a long list of such large formulas in a spreadsheet. And since they are organized in a list, each such formula can only take one cell. In almost all cases, formulas return single values (and therefore one cell is enough to store / display the output). However, if there is an internal error while evaluating the formula, the formula returns an error message. These error messages are usually quite long and therefore are returned as an array of different sizes (depending on how long the error message takes). So my goal was to write a VBA function that would first receive and then output the complete error message for the selected selected entry from the list.
source to share
I believe it Application.Evaluate
will return a result that matches the size of the input address. I suspect yours Selection
is a separate cell, so it returns a single value.
If you call it with instead Selection.CurrentArray.Address
, you'll get a response that is the same size as the correct array.
VBA and Excel Image
Code to test with
Public Function Test() As Variant
Test = Array(1, 2)
End Function
Sub t()
Dim a As Variant
a = Application.Evaluate(Selection.CurrentArray.Address)
End Sub
Edit based on the comments is a way to appreciate this outside of the sheet by creating a new sheet. I use the cut / paste method to ensure that all formulas work the same. It probably works better if the cells do not reference the cut. This won't technically break any other cells, though, as I'm using cut / paste.
In the code below, I had an array formula in a cell J2
that was being referenced by several other cells. It is expanded to have 3 lines and then the call is made Evaluate
. This returns an array as you want. Then he shrinks it down to one cell and moves it back.
I tried this for a simple example. I have no idea if this is the application you mean works.
Sub EvaluateArrayFormulaOnNewSheet()
'cut cell with formula
Dim str_address As String
Dim rng_start As Range
Set rng_start = Sheet1.Range("J2")
str_address = rng_start.Address
rng_start.Cut
'create new sheet
Dim sht As Worksheet
Set sht = Worksheets.Add
'paste cell onto sheet
Dim rng_arr As Range
Set rng_arr = sht.Range("A1")
sht.Paste rng_arr
'expand array formula size.. resize to whatever size is needed
rng_arr.Resize(3).FormulaArray = rng_arr.FormulaArray
'get your result
Dim v_arr As Variant
v_arr = Application.Evaluate(rng_arr.CurrentArray.Address)
''''do something with your result here... it is an array
'shrink the formula back to one cell
Dim str_formula As String
str_formula = rng_arr.FormulaArray
rng_arr.CurrentArray.ClearContents
rng_arr.FormulaArray = str_formula
'cut and paste back to original spot
rng_arr.Cut
Sheet1.Paste Sheet1.Range(str_address)
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End Sub
source to share
Try
vOutput = Application.Evaluate(Selection.CurrentArray.Address)
(assuming you have two cells with =SomeArrayFunction(...)
entered as an array formula)
I think the difference might be that evaluating one cell will only result in the values returned by that cell: the entire array is not returned there, only the first value.
source to share