How to save a formula as a string in VBA

Question

I've got the following code

Sub reset_formulas()

uptime_formula = "=IFS(AND(C25="Deployed",C26="Returned"), B26-B25, AND(C25="Returned",C26="Deployed"), 0, AND(C25="Deployed",C26="Deployed"),TODAY()-B25, AND(C25="Returned",C26="Returned"), 0, AND(C25="Deployed",C26=""), TODAY()-B25, AND(C25="Returned",C26=""), 0)"

End Sub

And I keep getting an error message of "Syntax Error" which I'm guessing is due to the " " characters around the words "Deployed" and "Returned".

Is there an easy way to save a formula as a string in Excel?

You will need to double-up any of the quotes between the opening and closing quotes. — Brian M Stafford, Mar 21 '22 at 12:14

score 1 · Accepted Answer · answered Mar 21 '22 at 12:36

Formulas quickly become a mess. In code, you can break it up in (sub)lines, like this example:

' Build a cell formula like:
'
'   =SUMPRODUCT(ROUND(Tabel_1234[ColumnName1]*Tabel_1234[ColumnName2]*Tabel_1234[ColumnName3],2))
'
Public Function BuildSumproductResource( _
    ByRef Table As ListObject, _
    ByVal ColumnIndex1, _
    ByVal ColumnIndex2, _
    ByVal ColumnIndex3) _
    As String

    Const Sum       As String = "=SUMPRODUCT(ROUND("
    
    Dim Formula     As String
    
    Formula = Sum & _
        Table.Name & _
        "[" & Table.ListColumns(ColumnIndex1).Name & "]*" & _
        Table.Name & _
        "[" & Table.ListColumns(ColumnIndex2).Name & "]*" & _
        Table.Name & _
        "[" & Table.ListColumns(ColumnIndex3).Name & "]," & _
        "2))"

    BuildSumproductResource = Formula

End Function

score 0 · Answer 2 · answered Mar 21 '22 at 11:56

0

If you want to extract the formula from a specified cell directly this might help.

Sub reset_formulas()

    Dim uptime_formula As String: uptime_formula = CStr(ThisWorkbook.Sheets(1).Cells(1, 1).Formula)
    Debug.Print uptime_formula

End Sub

answered Mar 21 '22 at 11:56

Manny

509
1
3
5

The problem is that the formula won't be stored in a cell so I need a way to save it in a variable. I am trying to replace the "" with Chr(34) but that's quite confusing because the formula is so long so I was expecting there was another way to do this – Sam Mar 21 '22 at 12:12
where are you getting these formulas from – Manny Mar 21 '22 at 12:15
Basically it is currently on a cell, but the data will change by the moment that I decide to run my macro so I wanted to save the formula somehow so I can have it in there in the future when I decide to get my data to its original format. – Sam Mar 21 '22 at 12:19
so if you use the code I provided you could: 1. Save the cells formula to a string variable 2. Run your other macro 3. place the formula back onto the cell Unless im missing something? – Manny Mar 21 '22 at 12:21

How to save a formula as a string in VBA

2 Answers2