Clever Geek Handbook

VBA - Error using Application.Evaluate on Long Formula

Let's say I have a long formula for some cell in Excel

= IF (SUM (D3: D6)> 1, ABCDEFGHIJKLMNOPQRSTU-VWXYZ 01 "IF (SUM (D3: D6)> 1," A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 02 ", IF (SUM (D3: D6)> 1, "A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 03", IF (SUM (D3: D6)> 1, "A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 04", IF ( SUM (D3: D6)> 1, "A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 05", IF (SUM (D3: D6)> 1, "A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 06 ", IF (SUM (D3: D6)> 1," A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 07 ", IF (SUM (D3: D6)> 1," A-B-C-DEFGHIJKL-M -NOPQRSTUVWXYZ, 08 ", IF (SUM (D3: D6)> 1," A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ,09 ", IF (SUM (D3: D6)> 1," A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 10 ", IF (SUM (D3: D6)> 1," A-B-C-DEFGHIJKL- M-NOPQRSTUVWXYZ, 11 ", IF (SUM (D3: D6)> 1," A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 12 ", IF (SUM (D3: D6)> 1," A-B- -DEFGHIJKL--NOPQRSTUVWXYZ, 13 ", IF (SUM (D3: D6)> 1," ---DEFGHIJKL--NOPQRSTUVWXYZ, 14 "," no "))))))))) )))))D6)> 1, "A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 13", IF (SUM (D3: D6)> 1, "A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 14", "no "))))))))))))))D6)> 1, "A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 13", IF (SUM (D3: D6)> 1, "A-B-C-DEFGHIJKL-M-NOPQRSTUVWXYZ, 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, this works just fine, so I was wondering if there is a way to evaluate long formulas (in general) using VBA.

+1


source to share


1 answer


According to Microsoft documentation, this throws your error:

Parameters

Name: Name

Required / Optional: Required

Data type: variant

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

Note that the description also says that you can use "Object Name" instead of a formula.

However, even further reading will help narrow down which options are for evaluating long formulas. In the next section, we'll discuss what "names" can be used with this method:

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

  • Formula.
  • A1-style references. You can use any single cell reference in A1 format. All links are considered absolute links.
  • Changes. You can use range, intersect and (colon, space and comma, respectively) with Recommendations.
  • Certain names. You can specify any name in the language of the macro.
  • External links. You can use! operator, refer to the cell or to the name specified in another book - for example, Evaluate ("[BOOK1.XLS] Sheet1! A1").
  • Chart objects. You can specify any name for the chart object, such as "Legend", "Plot Area" or "Series 1" to access the properties and methods of this object. For example, a chart ("Chart1"). Assessment ("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 of this, we show that we can use the cell address to perform the calculation, instead of carrying over the entire formula.

This means that we can change yours 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
+3


source






More articles:


All Articles