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