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:

Eval2.zip

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