Excel揭秘8：看看方括号在VBA中的妙处——有用的Evaluate方法

 

在《Excel VBA解读77：Evaluate方法》中，我们简单介绍了该方法的语法及使用。在本文中，我们将详细剖析这个方法。

在许多程序中，我们经常会看到像图1所示的代码片段，即出现了带方括号的字符串。

 图1

这些方括号里面的字符串为工作簿中定义的名称，方括号的作用就是告诉VBE编译器将名称转换成相应的单元格或单元格区域。

其实，这是Evaluate方法的简写格式。

 

根据VBA帮助文件指出，Evaluate方法将Excel名称转换为对象或值。这样的解释确实有点模糊。

其语法为：

expression.Evaluate(Name)

expression可以省略，即简写为：

Evaluate(Name)

参数不只限于名称，还可以是公式表达式字符串。

如果参数中没有变量，还可以更简略地写为：

[Name]

使用Evaluate加上带引号的参数的方法的优点是，可以在表达式中使用变量。而使用括号简写的优点是，不仅简短，而且在引用对象时会出现对象的属性和方法的智能提示且不会导致失败，例如代码：

Evaluate('Sheet2').Activate

运行失败。而代码：

[Sheet2].Activate

成功运行，并且在输入[Sheet2].后会出现属性和方法的提示。

 

其参数可以是：

• A1样式引用。可以使用A1样式引用中对单个单元格的任何引用，所有引用都应该是绝对引用。

• 单元格区域。可以使用引用单元格的区域、交叉和联合操作符（分别是冒号、空格和逗号）。

• 定义的名称。可以在宏语言中指定任意名称。

• 外部引用。可以使用！操作符指向其他工作簿中的单元格或者名称。

• 图表对象。可以指定任意图表对象名称。

• 公式表达式。代表任何有效的公式表达式的字符串。

 

下面是VBA帮助中给出的一些示例代码。

下列表达式是等价的：

[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')

 

下面的代码演示了在Evaluate方法中使用变量，将工作表Sheet1中单元格A1变为粗体格式：

Worksheets('Sheet1').Activate

boldCell = 'A1'

Application.Evaluate(boldCell).Font.Bold = True

 

为什么要使用Evaluate方法？

下面是几个使用Evaluate方法的优点：

1. 代码更简短

大多数情形下，Evaluate方法可以使用方括号的简写形式，使代码更简短。

下面的代码在单元格A1中输入数值100：

[A1].Value = 100

[A1] = 100

 

2. 可以在VBA中使用更多的工作表函数

在VBA中，通过WorksheetFunction对象可以使用很多在VBA中没有与之等价的工作表函数，但仍有一些工作表函数不能在VBA中使用。然而，通过Evaluate方法却可以在VBA中使用这些函数，或者是工作表数组公式。

例如，由于VBA有等效的IsEmpty函数提供了工作表函数ISBLANK相同的功能，因此不能通过WorksheetFunction对象使用ISBLANK函数。但是，如果需要在VBA使用ISBLANK函数，可以使用代码：

Evaluate('=ISBLANK(A1)')

[ISBLANK(A1)]

如果工作表单元格A1为空，则返回TRUE，否则返回FALSE。

 

3. 公式更直观

使用Evaluate方法比使用Application.WorksheetFunction更简单且更直观，其外观与工作表函数相同，就像在单元格中编写的公式一样。

例如，在工作表中使用VLOOKUP函数：

=VLOOKUP(D1,A1:B4,2,FALSE)

通常，在VBA中的等效代码为：

Application.WorksheetFunction.VLookup(Range('D1'),Range('A1:B4'), 2, False)

而如果使用Evaluate方法并忽略“=”号，在工作表中的公式可以直接复制到代码中：

Evaluate('VLOOKUP(D1,A1:B4,2,FALSE)')

或者：

[VLOOKUP(D1,A1:B4,2,FALSE)]
因此，对于很复杂的公式来说，使用Evaluate方法将其转换到VBA代码中将更方便，否则要修改为符合VBA语法的形式就需要大量的工作。

 

4. 代码更有效率

下面的代码将101至200的数值输入到单元格区域A1:A100：

[A1:A100] = [ROW(101:200)]

下面的代码将101至200的数值赋给一个Variant数组：

varArray = [ROW(101:200)]

它们都比使用循环更有效率。

 

5. 调用在运行时才创建的子过程

如果想要调用在运行时才创建子过程，由于这个子过程在编译时不存在，因此会导致编译时错误。虽然Run(“子过程名”)不会导致编译时错误，但如果在运行时代码还不存在，就会产生运行时错误。

而[子过程名]不会导致编译时或者运行时错误。

因此，如果仅当满足某种条件时才创建某子过程的情形下，并且另一子过程使用的值要从调用该子过程中获取。如果该子过程存在则调用它，如果不存在则继续运行，此时，Evaluate方法是调用该子过程的唯一选择。

 

Evaluate方法的使用

正如已经讲述的内容，Evaluate方法主要用于两种情形。

情形1：Evaluate( 公式 )

评估公式表达式或者值，并转换为值。

实际上，Evaluate方法在用于计算时，允许创建“虚拟单元格”，作为VBA代码与工作表公式之间的接口。这些虚拟单元格允许VBA访问和直接处理当前工作簿中每个工作表函数，因此提供了一种允许仅用一两行VBA代码就替换成千行公式的方法。

情形2：Evaluate( 对象名 )

将代表对象的对象名转换为对象。

 

下面是Evaluate方法的一些基本应用示例。

示例1：从关闭的工作簿中取值

下面的代码从当前工作簿所在文件夹的工作簿test.xlsx中获取值。

Sub GetValueFromClosedWB()

    With [Sheet2!A1:A5]

        .Value = '='' &ActiveWorkbook.Path & '\[test.xlsx]Sheet1'!A1:A5'

        .Value = .Value '移除对原工作簿的链接

    End With

End Sub

 

示例2：提取名称中的值

下面的代码首先创建一个名为“我的公众号”的名称，其内容为“完美Excel”，然后在当前工作表的单元格A1中输入值，最后使用Evaluate方法将A1中的值和名称的内容连接并显示。

Sub GetNameValue()

    ThisWorkbook.Names.Add '我的公众号','完美Excel'

    Range('A1').Value = '我的公众号是'

    MsgBox Evaluate('A1 & 我的公众号')

End Sub

或者将最后一行代码替换为简写形式：

MsgBox [A1 & 我的公众号]

运行后的结果如图2。

 图2

 

示例3：调用函数过程并从中获取值

下面的代码演示了调用子函数过程，并将返回的值加上100。

Sub CallFunc()

    MsgBoxEvaluate('testFunc(100) 100')

    MsgBox [testFunc(100) 100]

End Sub

 

Function testFunc(i As Long)

    testFunc = i 10

End Function

运行后的效果如下图3。

 图3

 

示例4：使用变量

下面的简单示例演示了在Evaluate方法中使用变量的基本方法。

下面的代码显示当前工作表单元格B1至B10中的值。

Sub testGetVarValue()

    Dim i As Long

    For i = 1 To 10

        MsgBox Evaluate('B' & i)

    Next i

End Sub

 

下面的代码在当前工作表单元格A1至A10中的值。

Sub testEnterValue()

    Dim rng As Range, i As Long

    For i = 1 To 10

        Set rng = Range('A' & i)

        [rng] = '完美Excel'& i

    Next

End Sub

 

示例5：引用图表和工作表对象

下面的代码设置当前工作表单元格背景色及图表格式。

Sub testObject()

    [图表 1].Activate

    With ActiveChart.ChartArea

        .Interior.Color = vbRed

        .Border.Color = vbYellow

    End With

    [Sheet6].Cells.Interior.Color = vbBlue

End Sub

运行后的效果如图4。

 图4

 

下面的代码依次激活当前工作簿中的工作表。

Sub testObject1()

    Dim ws As Worksheet, i As Long

   

    For i = 1 To Worksheets.Count

        Set ws = Worksheets('Sheet'& i)

        [ws].Activate

    Next i

End Sub

 

示例6：数组

下面的代码在虚拟单元格中创建数组，然后将其输入到工作表单元格中。

Sub EvaluateArray()

    Dim Array_1D, Array_2D

    With Worksheets('Sheet8')

        Array_1D =[{'A','B','C','D','E'}]

        .[A1].Resize(1, UBound(Array_1D, 1)) =Array_1D

        Array_2D = [{1,2;3,4;5,6}]

        .[A3].Resize(UBound(Array_2D, 1),UBound(Array_2D, 2)) = Array_2D

    End With

End Sub

运行代码后的结果如图5。

 图5

 

示例7：统计单元格数据的数量

下面的程序统计列A中，某单元格的值在其上面的单元格中出现的次数。

Sub CountCellNum()

    Dim i As Long

    For i = 2 To [COUNTA(A:A)]

        Evaluate('B' & i) =Evaluate('COUNTIF(A1:A' & (i - 1) & ',A' & i& ')')

    Next i

End Sub

运行后的结果如图6。

 图6

 

小结

Evaluate是一个强大的命令，然而它往往并不为人所知。

Evaluate基本上可以表示：做任何所需要执行的计算，如果要汇总数据，那么就汇总；如果要运行另一个程序，那么就运行另一个程序；如果要做…那么就做…，总之，无论需要什么，立即完成。

然而，Evaluate没有很多介绍文档，也会在许多程序中看到它的使用。但基本上是，虽然知道它能做什么但并没有真正看到它的好处，而只是看到了它表面上的一些，它的强大功能还需要深入挖掘。

---

 

本文为原创文章，转载请联系我（[e-mail]）或者注明出处。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。