I recently posted an update to the Eval2 spreadsheet with a subroutine to display a cell formula as a text string, with the cell references replaced by the associated values.
I have now added three user defined functions (UDFs) to do the same job:
- Addr2Val1 takes a text string as input and returns the formula with all cell references or range names converted to the value in the referenced cell. Cell references may be anywhere on any spreadsheet.
- The Eval function has been modified to work with cell references and range names, as well as optional lists of parameter symbols, and their associated values.
- Addr2Val2 works the same as Addr2Val1, except the input is an active cell formula, rather than a text string.
The new file may be downloaded from:
Examples of each function are shown in the screenshot below, followed by source code for the Addr2Val2 function. Full open-source code for the other functions is included in the download file.
Updated 3 Sep 2017: There seem to be issues with the WordPress system removing line breaks in some places, and inserting them in others. I have corrected the code below, but if you want to copy the code I recommend doing so from the download spreadsheet, rather than from the listing below, which probably still has some errors.
Function Addr2Val2(FuncRng As Range, Optional CommaDec As Boolean = False) As Variant Dim NumChar As Long, ParamDict As Scripting.Dictionary, i As Long, CheckC As String, AscCheckC As Long, CheckP As String, NewFunc As String, iErr As Long Dim ParamRng As Range, NumParam As Long, PVal As Variant, IsRng As Boolean, CheckRng As String, Func As String ' Evaluate a cell formula (Func), replacing cell addresses or range names with the values in the referenced cells. 'Func is a single cell containing the formula to be evaluated ' CommaDec = True to convert commas to decimal point and semi-colons to commas ' CommaDec = False (default) for no convertion. Func = Trim(FuncRng.Formula) Func = Replace(Func, "$", "") If CommaDec = True Then ' Replace all , with . and ; with , Func = Replace(Func, ",", ".") Func = Replace(Func, ";", ",") End If NumChar = Len(Func) i = 1 Do While i <= NumChar CheckP = "" Do CheckC = Mid(Func, i, 1) AscCheckC = Asc(CheckC) If (AscCheckC > 64 And AscCheckC < 91) Or (AscCheckC > 96 _ And AscCheckC < 123) Or AscCheckC = 95 Or AscCheckC = 33 Then CheckP = CheckP & CheckC i = i + 1 ElseIf CheckP <> "" And Asc(CheckC) > 47 And Asc(CheckC) < 58 Then CheckP = CheckP & CheckC i = i + 1 Else ' Check if CheckP is a cell address or range name On Error Resume Next CheckRng = "" IsRng = False CheckRng = TypeName(Range(CheckP)) If CheckRng = "Range" Then IsRng = True If IsRng Then NewFunc = NewFunc & Range(CheckP).Value2 & CheckC ' else leave it unchanged Else NewFunc = NewFunc & CheckP & CheckC End If i = i + 1 Exit Do End If Loop Loop Addr2Val2 = NewFunc End Function