VBA中输入公式和使用函数

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方法添加函数的帮助分为两类:普通工作簿和加载宏。基于普通工作簿与加载工作簿的特性不同,在设置函数说明时需要区别对待。