VBA - 使用Application.Evaluate对长公式时出错 [英] VBA - Error when using Application.Evaluate on Long Formula

问题描述

说我在Excel中的一些单元格中有一个长的公式

= IF（SUM（D3：D6）> 1，ABCDEFGHIJKLMNOPQRSTU 1，ABCDEFGHIJKLMNOPQRSTU-VWXYZ，
IF（SUM（D3：D6）> 1，ABCDEFGHIJKLMNOPQRSTU-VWXYZ
02，
IF（SUM（D3：D6）
03，
IF（SUM（D3：D6）> 1，ABCDEFGHIJKLMNOPQRSTU-VWXYZ
04，
IF（SUM（D3：D6）> 1，ABCDEFGHIJKLMNOPQRSTU 1，ABCDEFGHIJKLMNOPQRSTU-VWXYZ
06，
IF（SUM（D3：D6）> 1， ABCDEFGHIJKLMNOPQRSTU-VWXYZ
07，
IF（SUM（D3：D6）> 1，ABCDEFGHIJKLMNOPQRSTU-VWXYZ
08，
IF（SUM（D3：D6）> 1，ABCDEFGHIJKLMNOPQRSTU-VWXYZ
09，
IF（SUM（D3：D6）> 1，ABCDEFGHIJKLMNOPQRSTU-VWXYZ
10，
IF（SUM（D3：D6 ）> 1，ABCDEFGHIJ- 1，ABCDEFGHIJKLMNOPQRSTU-VWXYZ
12，
IF（SUM（D3：D6）> 1， ABCDEFGHIJKLMNOPQRSTU-VWXYZ
13，
IF（SUM（D3：D6）> 1，ABCDEFGHIJKLMNOPQRSTU-VWXYZ
14，no）））））））））））） ）））

，我运行以下VBA代码

  Private Sub ExecuteFormula（）
 Dim sFormula As String，vReturn As Variant 
 sFormula = Selection.Formula 
 
 vReturn = Application.Evaluate（sFormula）
如果VarType（vReturn）<> vbError然后
 MsgBox vReturn，vbInformation 
 Else 
 MsgBoxError，vbExclamation 
 End If 
 End Sub 
  

然后我得到错误。对于较短的公式，它的工作原理很好，所以我想知道是否有一种方法来评估长公式（一般来说）使用VBA。

解决方案

根据 Microsoft文档，这是什么导致您的错误：

参数

名称：名称

必需/可选：必需

数据类型：变体

描述：使用Microsoft Excel的命名约定的公式或对象的名称。名称的长度必须小于或等于255个字符。

请注意在说明中也可以使用对象的名称而不是公式。

然而，更进一步的阅读将有助于缩小选项用于评估长公式。下一节将给出我们可以使用以下方法的名称：

Microsoft Excel中的以下类型的名称可以与这种方法：

• 公式。


• A1样式引用。您可以使用A1样式符号中的单个
  单元格的引用。所有引用都被认为是
  的绝对引用。


• 范围。您可以使用范围，交叉和
  联合运算符（分别为冒号，空格和逗号）与
  引用。


• 定义的名称。您可以使用宏的语言
  指定任何名称。


• 外部引用。你可以使用！运算符
  指的是单元格或另一个工作簿中定义的名称 - 对于
  示例，Evaluate（[BOOK1.XLS] Sheet1！A1））。


• 图表对象。您可以
  指定任何图表对象名称，例如Legend，Plot Area或
  Series 1，以访问该对象的属性和方法。对于
  示例，Charts（Chart1）。评估（Legend）Font.Name返回图例中使用的
  名称。

然后具体的例子：

  a1] .Value = 25 
 Evaluate（A1）。Value = 25 
 
 trigVariable = [SIN（45）] 
 trigVariable = Evaluate（SIN（45） ）
 
设置firstCellInSheet =工作簿（BOOK1.XLS）。表格（4）。[A1] 
设置firstCellInSheet = _ 
工作簿（BOOK1.XLS）。表格（4）。评估（A1）
  

将所有内容显示给我们，我们可以使用该单元格的地址执行计算，而不是覆盖整个公式。

这意味着我们可以修改您的子使用单元格地址：

  Private Sub ExecuteFormula（）
 Dim sFormula As String，vReturn As Variant 
 sFormula = Selection.Address'使用单元格地址不是公式
 
 vReturn = Applicatio n.Evaluate（sFormula）
如果VarType（vReturn）<> vbError然后
 MsgBox vReturn，vbInformation 
 Else 
 MsgBoxError，vbExclamation 
 End If 
 End Sub 
  

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

这篇关于VBA - 使用Application.Evaluate对长公式时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！