VBA Evaluate UDF that can process range of cells/array

mc-lemons

Board Regular
Joined
Apr 30, 2012
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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

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

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
AB
11+12
22+24
33+36
42+35
53+47
Sheet3
Cell Formulas
RangeFormula
B1:B5B1=EVAL(A1:A5)
Dynamic array formulas.


VBA Code:
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
 
Upvote 0
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!
 
Upvote 0
Sure. How about this version?

EVAL
ABCD
11+122
22+24
3#N/A0
42+35
53+47
Sheet3
Cell Formulas
RangeFormula
B1:B5B1=EVAL(A1:A5)
D1D1=EVAL(A1)
A3A3=NA()
Dynamic array formulas.


VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top