r1c：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引用VBA内置函数

.Range("A14") = InStr(ActiveWorkbook.Name, ".")

(在VBE的代码窗口中输入VBA.，可以得到内置函数的提示；)

2.3 自定义函数

函数过程的标志以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预定义的相同功能的函数有细微区别）。

3 综合实例

有如下工作表：

运行以下VBA：

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

效果如下：

对应的公式如下：

－End－