在VBA中也可以像在工作中一样录入公式

nnsea

'在VBA中也可以像在工作中一样录入公式
Sub VBA中的做法()
Dim i%
For i = 1 To 10
    Range("c" & i) = Range("a" & i) + Range("b" & i)
Next
End Sub

Sub 普通公式()
Sheet1.Cells(1, 3) = "=a1+b1"
End Sub

Sub 批量计算()
Dim i As Integer
For i = 1 To 10
    Sheet1.Cells(i, 4) = "=a" & i & "+b" & i
Next i
End Sub

Sub 数组公式()
Range("e1:e10").FormulaArray = "=a1:a10+b1:b10"
End Sub

nnsea

Sub 带工作表函数的计算()
Dim i As Integer
For i = 1 To 10
    Sheet1.Cells(i, 4) = "=sum(a" & i & ":b" & i & ")"
Next i
End Sub
Sub 公式带引号的计算()
Cells(12, 1) = "=COUNTIF(A1:A10,"">9"")"
Cells(12, 2) = "=sum(INDIRECT(""a1:a10""))"
End Sub

nnsea

'借用工作表函数
Sub 运用工作表函数()
MsgBox Application.WorksheetFunction.CountIf(range("a1:a10"), "钢笔")
MsgBox WorksheetFunction.CountIf(range("a1:a10"), "钢笔")
MsgBox Application.CountIf(range("a1:a10"), "钢笔")
End Sub

'VBA函数
Sub VBA函数()
MsgBox VBA.Format(range("b1"), "yyyy年m月d日")
MsgBox Format(range("b1"), "yyyy年m月d日")
End Sub

'自定义函数
Function SEX(rng As range)
SEX = IIf(Mid(rng, 15, 3) Mod 2, "男", "女")
End Function