1 VBA在单元格中输入公式
在公式中一般会出现对单元格地址的引用,引用的方式有绝对引用(A1方式)和相对引用(R1C1)方式,同样的,在用VBA输入公式时,也会有两种方式。
1.1 输入A1格式的公式
向单元格输入公式,实际上就是输入公式的字符串。这时采用Range的value属性或Formula属性均可。
.Range("E11").Formula = "=sum(E2:E10)"
1.2 输入R1C1公式
使用R1C1格式向单元格输入公式,实际上是录制宏的方式。是一种地址相对引用的方式,这里的相对引用的基准地址就是公式所在的地址, 以此地址为基准,偏移行R和列C得到相对引用位置。有些人可能喜欢这种方式,不过这种方式不像A1方式那样直观和容易理解。
(如果不是很熟悉VBA的语法格式,可以采取录制宏的方式去得到代码;如果觉得R1C1格式不习惯,可以在在Excel,设置公式的引用方式为A1(在Excel选项的公式项中设置),输入公式后再复制到VBA代码中。)
.Range("G11").FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)";
[]中的数据代表单元格相对于当前单元格的行列偏移。
1.3 输入数组公式
向单元格或单元格区域输入数组公式,需要使用FormulaArray属性。
Range("E1:E11")FormulaArray = "=C2:C10*D2:D10"
2 使用函数
2.1 VBA引用Excel内置函数
.Range("A16") = "=find(""."",A13,1)"
.Range("A18") = Application.WorksheetFunction.Find(".", fname, 1)
(在VBE的代码窗口中输入Application.WorksheetFunction.可以得到引用Excel内置函数的提示;)
2.2 VBA引用内置函数
如Left(字符串,字符数);
(在VBE的代码窗口中输入VBA.,可以得到内置函数的提示;)
2.3 自定义函数:Function
函数过程的标志以Function开头,定义好以后,可以像调用Excel已定义的函数一样通过等于号去使用它。
如:
Function 及格率(cell As Range)
及格率=WorksheetFunction.CountIf(cell, ">=60") / WorksheetFunction.CountIf(cell, ">0")
及格率=Format(及格率, "0.00%")
End Function
自定义函数可以必须有返回值,所以有函数体中必须至少被赋值一次,也因此在Function后跟数据类型定义;
Function过程通常三种方式调用:
(1)在工作表中通过公式调用:像内部函数一样在工作表中使用,也可以与其它函数嵌套。使用方法如下:
公式→插入函数→类别:用户定义→选择函数;
(2)在VBA代码中被其它过程调用。
(3)递归:Function过程和Sub一样可以实现递归。如果不是刻意地、有计划地进入递归状态,可以会造成资源耗尽或者溢出堆栈空间。例如下例函数的调用:
在VBA语言中,也有预定义一些函数,与Excel预定义的相同功能的函数有细微区别)。 Sub formulaTest()
With ActiveWorkbook.Sheets("使用公式和函数")
For i = 2 To 10
.Range("E" & i).Value = "=sum(A" & i & ":D" & i & "2)"
Next
.Range("E11").Formula = "=sum(E2:E10)"
.Range("G11").FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
.Range("G2:G10").FormulaArray = "=E2:E10*F2:F10"
.Range("A13") = ActiveWorkbook.Name
Dim fname As String
fname = Range("A13").Value
.Range("A14") = InStr(ActiveWorkbook.Name, ".")
.Range("A15") = InStr([A13], ".")
.Range("A16") = "=find(""."",A13,1)"
.Range("A17") = InStr(fname, ".")
.Range("A18") = Application.WorksheetFunction.Find(".", fname, 1)
.Range("A19") = Application.WorksheetFunction.Find(".", [A13], 1)
'[A13]相当于range("A13")
End With
'Instr([start,]string1,string2[,compare])
End Sub
VBA中用于指定函数说明的是Application.MacroOptions方法。
Application.MacroOptions方法的基本语法是:
Application.MacroOptions(Macro,Description,HasMenu,MenuText,?HasShortcutKey,ShortcutKey,Category,StatusBar,HelpContextID,HelpFile)
Application.MacroOptions?Macro:="大写",?Description:="将阿拉伯数字转为人民币金额大写",?Category:="andy专用"
使用MacroOptions方法添加函数的帮助分为两类:普通工作簿和加载宏。基于普通工作簿与加载工作簿的特性不同,在设置函数说明时需要区别对待。