在VBA中可以利用Excel函数的强大功能。
使用WorksheetFunction对象
WorksheetFunction对象的成员就是Excel内置函数，因此，利用该对象可以直接在VBA中使用这些函数。
例如，求单元格区域A1:A5的数值之和，假如将该区域命名为“myRange”，则可以在VBA中使用下列代码：
Application.WorksheetFunction.Sum(Range("myRange"))
Application.WorksheetFunction.Sum(Range("A1:A5")) |
注意，在VBA中当输入WorksheetFunction后再输入一个句点时，就会显示所有的函数列表，如下图所示。
Evaluate方法
使用Worksheet对象或Application对象的Evaluate函数。该函数接受字符串参数，返回引用的公式的值（字符串为公式）或者引用的单元格区域（字符串代表单元格或单元格区域，A1样式）。其语法为：
表达式.Evaluate(Name)
将Excel名称转换为对象或值，其中：
- 表达式 对于Application，表达式可选；对于Chart、DialogSheet和Worksheet，表达式必需。
- Name 必需的字符串。满足Excel命名规范的对象的名称，包括：
（1）A1样式引用。可以以A1样式的引用方式使用任意对单个单元格的引用，引用都被考虑是绝对引用。
（2）单元格区域。可以使用区域、交叉和联合操作符（冒号、空格和逗号）引用单元格区域。
（3）定义的名称。
（4）外部引用。可以使用!操作符引用另一工作簿中的单元格或定义的名称。例如Evaluate(“[BOOK1.XLS]Sheet1!A1″)。
（5）Chart对象。可以指定任意图表对象名称，例如“图例”、“绘图区”或“系列1”，来访问该对象的属性和方法。例如，Charts(“Chart1″).Evaluate(“Legend”).Font.Name返回图例中使用的字体名。
也可以使用方括号代替evaluate函数，使用方括号(例如，”[A1:C5]”)与调用带字符串参数的Evaluate方法是相同的。例如，下面的代码等价：
[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 |
更简短一些，如设置当前工作表中单元格B1为加粗：
下面的代码返回当前工作表中单元格A1的值：
下面的代码返回当前工作表中单元格区域A1:A5的值之和：
又例如：
For i = 2 To 10
Debug.Print Evaluate("A" & i), Evaluate("COUNTIF(A1:A" & (i - 1) & ",A" & i & ")")
Next i |
将打印出A1:A10区域内，某单元格的值在其上面的单元格中出现的次数。
输出结果为：
可以将上述代码稍作变化，以统计A列中的值，即可以动态调整。
For i = 2 To [COUNTA(A:A)]
Debug.Print Evaluate("A" & i), Evaluate("COUNTIF(A1:A" & (i - 1) & ",A" & i & ")")
Next i |
注意，上面的代码中使用[COUNTA(A:A)]来统计A列中有多少个数值单元格。
下面的代码选择单元格区域A1:C3：
Range(“offset(a1,,,3,3)”).Select |
使用Evaluate函数，可以使用在VBA中不能使用的工作表函数。例如，ISBLANK函数。下面的示例判断单元格A1是否为空，如果为空返回True，不为空则返回False。
MsgBox Evaluate(“=ISBLANK(A1)”)
MsgBox [ISBLANK(A1)] |
可以更加灵活地编写代码。
Sub IsActiveCellEmpty()
Dim strFunctionName As String, strCellReference As String
strFunctionName = "ISBLANK"
strCellReference = ActiveCell.Address
MsgBox Evaluate(strFunctionName & "(" & strCellReference & ")")
End Sub |
但是，如果只是使用方括号，则不能够在其中包含变量表达式。
示例1
首先，创建隐藏的名称来保存密码。（在名称定义对话框中看不到隐藏的名称，因此可以非常方便地存储信息）
Names.Add Name:="PassWord", RefersTo:="Bazonkas", Visible:=False |
接着，在表达式中使用隐藏的数据：
strUserInput = InputBox("请输入密码:")
If strUserInput = [Password] Then
... |
示例2
下面的表达式生成一个二维的Variant型数组，100行1列，值为101到200。
vRowArray = [Row(101:200)] |
下面的代码将101至200的值赋给单元格区域B1:B100：
[B1:B100] = [Row(101:200)] |
以上都比For…Next循环更有效率。
示例3
自定义函数——FindOffset函数
该函数将在某区域内搜索某值，然后从所发现的结果位置偏移x行和x列。例如：
=FindOffset($A$1:$E$10,”Dog”,2,3)
在$A$1:$E$10区域查找“Dog”，2(可选的)告诉该函数从内容为“Dog”的单元格处偏移2列，3(可选的)告诉该函数从内容为“Dog”的单元格处偏移3行。因此，如果“Dog”在单元格B5，那么该函数将返回单元格D8(离B5单元格右侧2列下方3行)中的值。
代码如下：
Function FindOffset(LookInRange As Range, FindVal, _
Optional ColOffset As Long, Optional RowOffset As Long)
Dim lCount As Long, lRow As Long
On Error Resume Next
For lCount = 1 To LookInRange.Columns.Count
lRow = Application.WorksheetFunction.Match _
(FindVal, LookInRange.Columns(lCount), 0)
If lRow > 0 Then
FindOffset = LookInRange.Cells(lRow, lCount) _
(RowOffset + 1, ColOffset + 1)
Exit For
End If
Next lCount
On Error GoTo 0
End Function |
说明：
For循环的循环次数为Range变量LookInRange的列数，即
LookInRange.Columns.Count |
在每次循环中，WorksheetFunction.Match用于查找FindVal代表的值在单元格区域LookInRange中的列数，如果找到，即lRow > 0，将返回真并进入If语句。If语句里面的代码指定开始偏移的单元格位置，即
LookInRange.Cells(lRow, lCount) |
变量lRow包含要偏称的行数，lCount为要偏移的列数。因此，
=FindOffset($A$1:$E$10,”Dog”,2,3)
如果“Dog”在单元格B5中，LookInRange.Cells(lRow, lCount)将为LookInRange.Cells(5, 2) ，与LookInRange ($A$1:$E$10)相关的单元格为B5，即$A$1:$E$10区域的第5行和第2列。
接着使用Item属性从该单元偏移，也就是(RowOffset + 1, ColOffset + 1)。加1是因为Offset属性的Item方法基于0，即基点单元格。(注：Offset属性可以接受负值，而Item属性不能)
一旦找到单元格，Exit For及时退出该函数并显示结果。
使用这些技术，可以将VBA和Excel公式与函数联合，使得更为强大和灵活。
进一步阅读：在VBA中使用R1C1样式的公式
参考资源：
How to Use an Excel Formula from VBA
http://www./News/excel-evaluate-formula-VBA.htm
http://msdn.microsoft.com/en-us/library/aa223886(v=office.11).aspx
Excel 2007 VBA参考大全
