vba to extract formula with values

This will work - maybe - as long as your sheet names don't have spaces - written to process just the activecell:

Option Explicit

Sub ReplaceReferences()
    Dim f As String
    Dim strF As String
    Dim i As Integer
    Dim strR As String
    Dim v As Variant
    
    strR = "+-*&^=><,/"
    strF = ActiveCell.Formula
    
    f = ActiveCell.Formula
    For i = 1 To Len(strR)
        f = Replace(f, Mid(strR, i, 1), " ")
    Next i
    
    v = Split(f, " ")
    
    For i = LBound(v) To UBound(v)
        If InStr(v(i), "!") > 0 Then
            strF = Replace(strF, v(i), Evaluate("=" & v(i)))
        End If
    Next i
    
    For i = LBound(v) To UBound(v)
        If InStr(v(i), ":") > 0 Then
            strF = Replace(strF, v(i), Evaluate("=" & v(i)))
        End If
    Next i
    
    For i = LBound(v) To UBound(v)
        If Len(v(i)) > 1 Then
            strF = Replace(strF, v(i), Evaluate("=" & v(i)))
        End If
    Next i
    
    
    ActiveCell.Formula = "'" & Right(strF, Len(strF) - 1)
    
    
End Sub

If you want it as a function, then try this code, used like

=ReplaceReferences(A2)

where A2 has the formula you want evaluated.

Option Explicit

Function ReplaceReferences(r As Range) As String
    Dim f As String
    Dim strF As String
    Dim i As Integer
    Dim strR As String
    Dim v As Variant
    
    strR = "+-*&^=><,/"
    strF = r.Formula
    
    f = r.Formula
    For i = 1 To Len(strR)
        f = Replace(f, Mid(strR, i, 1), " ")
    Next i
    
    v = Split(f, " ")
    
    For i = LBound(v) To UBound(v)
        If InStr(v(i), "!") > 0 Then
            strF = Replace(strF, v(i), Evaluate("=" & v(i)))
        End If
    Next i
    
    For i = LBound(v) To UBound(v)
        If InStr(v(i), ":") > 0 Then
            strF = Replace(strF, v(i), Evaluate("=" & v(i)))
        End If
    Next i
    
    For i = LBound(v) To UBound(v)
        If Len(v(i)) > 1 Then
            strF = Replace(strF, v(i), Evaluate("=" & v(i)))
        End If
    Next i
    
    
    ReplaceReferences = "'" & Right(strF, Len(strF) - 1)
    
    
End Sub

Sorry - I should have told you to remove the old subroutine code, or comment it out.

I didn't save the routine in your third message
https://www.excelforum.com/excel-pro...ml#post5881955

This message is there unfortunately. I am be doing something wrong.

I noticed also that macros are blank. Shouldn't there be a macro name replacereferences?

rr.png

D'oh! Thank you for catching that!

Try this variation on Bernie's function. If a worksheetname has spaces, you can specify a delimiter, for example "|"

Option Explicit

Function ReplaceReferences(r As Range, Optional delim As String = " ") As String
    
' Call using, for example, =ReplaceReferences(B1)
' Call using, for example, =ReplaceReferences(B1," ")
' Call using, for example, =ReplaceReferences(B1,"|")
    
    Dim f As String
    Dim strF As String
    Dim i As Integer
    Dim strR As String
    Dim v As Variant
    
    strR = "+-*&^=><,/"
    strF = r.Formula
    
    f = r.Formula
    For i = 1 To Len(strR)
        f = Replace(f, Mid(strR, i, 1), delim)
    Next i
    
    v = Split(f, delim)
    
    For i = LBound(v) To UBound(v)
        If InStr(v(i), "!") > 0 Then
            strF = Replace(strF, v(i), Evaluate("=" & v(i)))
        End If
    Next i
    
    For i = LBound(v) To UBound(v)
        If InStr(v(i), ":") > 0 Then
            strF = Replace(strF, v(i), Evaluate("=" & v(i)))
        End If
    Next i
    
    For i = LBound(v) To UBound(v)
        If Len(v(i)) > 1 Then
            strF = Replace(strF, v(i), Evaluate("=" & v(i)))
        End If
    Next i
    
    
    ReplaceReferences = "'" & Right(strF, Len(strF) - 1)

End Function

I don't think you need the single quote:

    ReplaceReferences = Right(strF, Len(strF) - 1)

You're welcome.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Because this line:

strR = "+-*&^=><,/"

does not include ( and ).

I did that so that something like

=SUM(A:A)

would not blow up the function by trying to include all of A in the result, but would show the SUM() result.

You can change that line to this to fix your issue:

strR = "+-*&^=><,/()"