How to save a formula as a string in VBA

user_15451183 0
Viewed 103

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?

vba
2 Answers

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
user_35272970

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
user_148805780
Related