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
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