excel方括号在VBA中的妙处

在许多程序中，我们经常会看到像图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