How to get the formula with the real value in vba?

Question

I'm writing vba code in excel and I'm new:)

Please help me with the following problems:

• I have a formula in cell A1 of =B1*C1*D1
• Cells B1, C1 and D1 have respective values (i.e 3,2,1)

I want to get the formula as a string - i.e. =3*2*1 - using VBA

Answer 1

You could use

• a regexp to parse out all the non mathematical operator portions
• then Evaluate all these string sections

It probably will need finessing for complicated strings, but its a good start

Updated for more complex matches

code

Sub ParseEm()
Dim strIn As String
Dim strNew As String
Dim strCon As String
Dim lngCnt As Long

Dim objRegex As Object
Dim objRegMC As Object
Dim objRegM As Object

strCon = "|"
Set objRegex = CreateObject("vbscript.regexp")

With objRegex
    .Pattern = "[^=/+/\/-/*\^]+"
    .Global = True
    strIn = [a1].Formula
    If .test(strIn) Then
        Set objRegMC = .Execute(strIn)
        For Each objRegM In objRegMC
        strNew = Evaluate(CStr(objRegM))
        If objRegM.Length >= Len(strNew) Then
        Mid$(strIn, objRegM.firstindex + 1 + lngCnt, objRegM.Length) = strNew & Application.Rept(strCon, objRegM.Length - Len(strNew))
        Else
           strIn = Left$(strIn, objRegM.firstindex + lngCnt) & strNew & Right$(strIn, Len(strIn) - objRegM.firstindex - objRegM.Length - lngCnt - 1)
        lngCnt = lngCnt + Len(strNew) - objRegM.Length
        End If
        Next
        strIn = Replace(strIn, strCon, vbNullString)
        MsgBox strIn
    Else
        MsgBox "sorry, no matches"
    End If
End With
End Sub

Answer 2

The only way I know is to use the Precedents functionality:

Sub GetFormulaAsString()
    Dim var As Range, temp As String, formulaAsString As String

    temp = "="

    For Each var In Range("A1").Precedents
        temp = temp & var & "*"
    Next var

    formulaAsString = VBA.Left$(temp, Len(temp) - 1) // "=3*2*1"
End Sub

Answer 3

Idea is to replace cells' references in .Formula property by its' values. the only trouble is with $ sign in formulas, so cA and rA cycles were added.

Sub GetFormulaAsString()
    Dim var As Range
    Dim temp As String
    Dim R As Range
    Dim v As Variant
    Dim cA As Integer
    Dim rA As Integer
    Dim cellname As String

    Set R = Range("A1")
    temp = R.Formula
    Debug.Print temp

    For Each var In R.DirectPrecedents
        v = var.Value
        For cA = 1 To 0 Step -1
            For rA = 1 To 0 Step -1
                cellname = var.Address(cA, rA)
                temp = Replace(temp, cellname, v)
            Next rA
        Next cA

    Next var

    Debug.Print temp
    msgbox temp
End Sub

Answer 4

I've done the problem, the pseudo is:

1. finding string a1 b1 c1

2. making the new formula like this ="="&b1&"*"&c1&"*"&d1 at cell a1

3. get the value of a1

not bad? :)