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.
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
