VBA Evaluate UDF that can process range of cells/array

[mc-lemons]

Hi all,

I need some help with a UDF, since my VBA skills are very limited (largely copy/paste and tweak willy-nilly!). My first goal was to find a way to evaluate various formulas that are stored in cells as text strings (without an "=" at the start). Through some internet searching, I came to the conclusion that a UDF was probably the best way to accomplish. At the bottom is an example of a UDF I found that works. There are many other variations that work as well, however, none of them can process multiple cells at once. Using the example data below, if I enter =Eval(A1) in a cell, it correctly returns the value 2. But, if I enter =Eval(A1:A3), I get a #VALUE! error. I would like the UDF to "lift" the function (Lifting) so that each item is processed individually and an array is returned to be used for further calculation (say in SUMPRODUCT), or just to output to a Spill Range. So continuing with the below example, I would like =Eval(A1:A3) to return {2,4,6}. Appreciate it!

‎ ‎ ‎ ‎ ‎ ‎ ‎ A
1‎ ‎ ‎ 1+1
2‎ ‎ ‎‎ ‎2+2
3‎ ‎ ‎‎ ‎3+3

Function Eval(strTextString As String)
    Application.Volatile
    Eval = Application.Caller.Parent.Evaluate(strTextString)
End Function

 

[lrobbo314]

I would suggest updating your profile to let us know what version of Excel you are using.

If you're on 365, this should work.

EVAL
	A	B
1	1+1	2
2	2+2	4
3	3+3	6
4	2+3	5
5	3+4	7
Sheet3

Cell Formulas
Range	Formula
B1:B5	B1	=EVAL(A1:A5)
Dynamic array formulas.

Function EVAL(r As Range)
Dim AR() As Variant:    AR = r.Value2

For i = 1 To UBound(AR)
    AR(i, 1) = Evaluate(AR(i, 1))
Next i

EVAL = AR
End Function

 

[mc-lemons]

It worked! Thank you so much. I do have a few questions.

1. It does not appear to work on a single cell. Is there any way to modify so it handles both single cells and ranges of cells?
2. Is there anyway to handle errors like #NAME?, #VALUE!, etc? Some of the cells in the range will be modified, possibly cleared out and/or changed to something that isn't a valid formula. In these cases, I would like those items to evaluate to zero.

FYI I have the below version of Office
Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 32-bit

Thanks again!

 

[lrobbo314]

Sure. How about this version?

EVAL
	A	B	C	D
1	1+1	2		2
2	2+2	4
3	#N/A	0
4	2+3	5
5	3+4	7
Sheet3

Cell Formulas
Range	Formula
B1:B5	B1	=EVAL(A1:A5)
D1	D1	=EVAL(A1)
A3	A3	=NA()
Dynamic array formulas.

Function EVAL(r As Range)
Dim AR() As Variant
Dim x As Variant

If r.Rows.Count = 1 Then
    ReDim AR(1 To 1, 1 To 1)
    AR(1, 1) = r.Text
Else
    AR = r.Value2
End If

For i = 1 To UBound(AR)
    x = Evaluate(AR(i, 1))
    AR(i, 1) = IIf(IsError(x), 0, x)
Next i

EVAL = AR
End Function

 

[mc-lemons]

Fantastic! Thank you so very much!

 

[lrobbo314]

No problemo! Thanks for the feedback.