在VBA中使用Excel工作表公式

在VBA中使用Excel工作表公式在VBA中使用Excel工作表公式VBA和Excel公式与函数联合，使得应在VBA中可以利用Excel工作表函数的强大功能，将 用程序更为强大和灵活。使用 WorksheetFunction寸象WorksheetFunction对象的成员就是 Excel内置函数，因此，利用该对象可以直接在 VBA中使 用这些函数。例如，求单元格区域 A1:A5的数值之和，假如将该区域命名为"myRange”则可以在 VBA中使用下列代码：Applicatio n. WorksheetF un ctio n.Sum(Ra nge("myRa nge")Applicatio n. WorksheetFu nctio n.Sum(Ra nge("A1:A5")注意，在VBA中当输入 WorksheetFunction后再输入一个句点时，就会显示所有的函数列表, 如下图1所示。-# -fanjy_EH在VBA中使用Excel工作表公式-# -fanjy_EH在VBA中使用Excel工作表公式application.WcurksheetFuncti oil.-# -fanjy_EH在VBA中使用Excel工作表公式hju 殆 Accrlntflld Acosh图1:显示VBA可用工作表函数列表Evaluate方法使用Worksheet对象或Application对象的Evaluate函数。该函数接受字符串参数，返回引用的公式的值(字符串为公式)或者引用的单元格区域(字符串代表单元格或单元格区域，A1样式)。其语法为：表达式.Evaluate(Name)将Excel名称转换为对象或值，其中：表达式 对于Application ,表达式 可选；对于Chart、DialogSheet 和 Worksheet,表达式必需。Name 必需的字符串。满足 Excel命名规范的对象的名称，包括：(1) A1样式引用。可以以 A1样式的引用方式使用任意对单个单元格的引用，引用都 被考虑是绝对引用。(2) 单元格区域。可以使用区域、交叉和联合操作符(冒号、空格和逗号)引用单元 格区域。(3) 定义的名称。(4) 外部引用。可以使用！操作符引用另一工作簿中的单元格或定义的名称。例如Evaluate("BOOK1.XLSSheet1!A1")。(5) Chart对象。可以指定任意图表对象名称，例如“图例” 、“绘图区”或“系列1 ”来访问该对象的属性和方法。例如，Charts("Chart1").Evaluate("Legend").Font.Name返回图例中使用的字体名。也可以使用方括号代替evaluate函数，使用方括号(例如，"A1:C5")与调用带字符串参数的Evaluate方法是相同的。例如，下面的代码等价：a1.Value = 25Evaluate("A1").Value = 25trigVariable = SIN(45) trigVariable = Evaluate("SIN(45)")Set firstCellI nSheet = Workbooks("BOOK1.XLS").Sheets(4).A1Set firstCellI nSheet = Workbooks("BOOK1.XLS").Sheets (4) .Evaluate("A1")使用方括号的优势是代码更简短。使用Evaluate方法的的优势是参数是字符串，因此可以在代码中构造字符串或使用变量。例如，下面的代码将 Sheet1中单元格A1加粗:Worksheets("Sheet1").ActivateboldCell = "A1"Applicati on .Evaluate(boldCell).F on t.Bold = True更简短一些，如设置当前工作表中单元格B1为加粗:B1.Fo nt.Bold = True下面的代码返回当前工作表中单元格A1的值：Evaluate("A1")F面的代码返回当前工作表中单元格区域A1:A5的值之和:又例如：Evaluate("SUM(A1:A5)")图2:示例工作表For i = 2 To 10Debug.Print Evaluate("A" & i), Evaluate("COUNTIF(A1:A" & (i - 1) & ”,A" & i & ")")Next i将打印出A1:A10区域内，某单元格的值在其上面的单元格中出现的次数。输出结果为：图3 :运行后的结果可以将上述代码稍作变化，以统计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 IsActiveCellEmptyODim strF un ctio nN ame As String, strCellRefere nee As Stri ngstrF un ctio nN ame = "ISBLANK"strCellRefere nee = ActiveCell.AddressMsgBox Evaluate(strFu nctio nN ame & "(” & strCellRefere nee & ")")End Sub但是，如果只是使用方括号，则不能够在其中包含变量表达式。示例1首先，创建隐藏的名称来保存密码。(在名称定义对话框中看不到隐藏的名称，因此可以非常方便地存储信息)Names.Add Name:="PassWord", RefersTo:="Bazo nkas", Visible:=False接着，在表达式中使用隐藏的数据：strUserInput = InputBox("请输入密码:")If strUserI nput = Password Then示例2下面的表达式生成一个二维的Variant型数组，100行1列，值为101到200。vRowArray = Row(101:200)下面的代码将101至200的值赋给单元格区域B1:B100:B1:B100 = Row(101:200)以上都比Fol Next循环更有效率。示例3 :自定义函数FindOffset函数该函数将在某区域内搜索某值，然后从所发现的结果位置偏移x行和x列。例如：=Fi ndOffset($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 Fi ndOffset(Look InRange As Ran ge, Fin dVal, _Optio nal ColOffset As Long, Optio nal RowOffset As Long)Dim lCo unt As Lon g, lRow As LongOn Error Resume NextFor lCo unt = 1 To Look InRan ge.Colu mn s.Co untlRow = Applicatio n.WorksheetF un ctio n.Match _(Fi ndVal, Look In Ra nge.Colu mn s(lCou nt), 0)If lRow > 0 The nFin dOffset = LookI nRan ge.Cells(IRow, ICou nt) _(RowOffset + 1, ColOffset + 1)Exit ForEnd IfNext lCou ntOn Error GoTo 0End Function说明：For循环的循环次数为Range变量Look In Ra nge的列数，即Look InRan ge.Colu mn s.Co unt在每次循环中，WorksheetFu nctio n.Match 用于查找 Fin dVal代表的值在单元格区域Look InRange中的列数，如果找到，即lRow > 0,将返回真并进入If语句。If语句里面的代码指定开始偏移的单元格位置，即LookI nRan ge.Cells(lRow, lCou nt)变量lRow包含要偏称的行数，lCount为要偏移的列数。因此，=Fi ndOffset($A$1:$E$10,"Dog",2,3)如果“ Dog”在单元格 B5 中，LookInRange.Cells(lRow, lCount)将为 LookInRange.Cells(5, 2), 与Look InRange ($A$1:$E$10)相关的单元格为 B5,即$A$1:$E$10区域的第5行和第2列。接着使用Item属性从该单元偏移，也就是 (RowOffset + 1, ColOffset + 1)。力口 1是因为 Offset属性的Item方法基于0，即基点单元格。（注：Offset属性可以接受负值，而Item属性不能） 一旦找到单元格，Exit For及时退出该函数并显示结果。示例4：在VBA中使用工作表公式查找值ACDEGH 1具体项目表顶目清单袤2砖基础挖基础土方3接桩砖基础JIT4实心砖墙平整场地矿S预制钢筋磋桩根6接桩个7磋灌注桩幷8双排脚手架100m:9实心砖墙Hi310铝台金门檔11预制构件钢筋t问题：在G列中查找A列中的值，如果找到则将G列中相应行对应的 H列中的值复制到该值在A列中相应行的B列中。图4:示例工作表ABCDEFGH 111具体吹目表顶目清单表2砖基砒14?挖基础土方IL3g接桩个砖基础IL3电实心