VBA - Error when using Application.Evaluate on Lon

Question

Say I have a long formula on some cell in Excel

=IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 01", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 02", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 03", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 04", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 05", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 06", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 07", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 08", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 09", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 10", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 11", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 12", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 13", IF(SUM(D3:D6)>1,"A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z 14","no" ))))))))))))))

and I run the following VBA code

Private Sub ExecuteFormula()
    Dim sFormula As String, vReturn As Variant
    sFormula = Selection.Formula

    vReturn = Application.Evaluate(sFormula)
    If VarType(vReturn) <> vbError Then
        MsgBox vReturn, vbInformation
    Else
        MsgBox "Error", vbExclamation
    End If
End Sub

then I get "Error". For shorter formulas it works just fine, so I was wondering if there was a way to evaluate long formulas (in general) using VBA.

Answer 1

According to the Microsoft documentation this is what is causing your error:

Parameters

Name: Name

Required/Optional: Required

Data Type: Variant

Description: A formula or the name of the object, using the naming convention of Microsoft Excel. The length of the name must be less than or equal to 255 characters.

Note that in the description it also says you can use "The name of the object" instead of a formula.

However even further reading will help narrow down what the options are for evaluation long formulas. This next section gives us what "names" can be used with this method:

The following types of names in Microsoft Excel can be used with this method:

• Formulas.
• A1-style references. You can use any reference to a single cell in A1-style notation. All references are considered to be absolute references.
• Ranges. You can use the range, intersect, and union operators (colon, space, and comma, respectively) with references.
• Defined names. You can specify any name in the language of the macro.
• External references. You can use the ! operator to refer to a cell or to a name defined in another workbook — for example, Evaluate("[BOOK1.XLS]Sheet1!A1").
• Chart Objects. You can specify any chart object name, such as "Legend", "Plot Area", or "Series 1", to access the properties and methods of that object. For example, Charts("Chart1").Evaluate("Legend").Font.Name returns the name of the font used in the legend.

And then specific examples:

[a1].Value = 25 
Evaluate("A1").Value = 25 

trigVariable = [SIN(45)] 
trigVariable = Evaluate("SIN(45)") 

Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).[A1] 
Set firstCellInSheet = _ 
    Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")

Taking all that in shows us that we can use the cell's address to perform the calculation instead of bringing over the entire formula.

This means we can modify your sub to use the cell address:

Private Sub ExecuteFormula()
    Dim sFormula As String, vReturn As Variant
    sFormula = Selection.Address ' use the cells address not the formula

    vReturn = Application.Evaluate(sFormula)
    If VarType(vReturn) <> vbError Then
        MsgBox vReturn, vbInformation
    Else
        MsgBox "Error", vbExclamation
    End If
End Sub