How to save a formula as a string in VBA [duplicate]

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
– Brian M Stafford, Commented Mar 21, 2022 at 12:14

Gustav · Accepted Answer · 2022-03-21 12:36:25Z

1

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

answered Mar 21, 2022 at 12:36

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manny · Accepted Answer · 2022-03-21 11:56:39Z

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, 2022 at 11:56

Manny

5091 gold badge3 silver badges5 bronze badges

4 Comments

Sam Over a year ago

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

Manny Over a year ago

where are you getting these formulas from

Sam Over a year ago

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.

Manny Over a year ago

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?

Collectives™ on Stack Overflow

How to save a formula as a string in VBA [duplicate]

2 Answers 2

Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Linked

Related