EXCELVBA学习笔记

For i = 2 To 8

Cells(i, 4) = Cells(i, 3) + Cells(i, 2) Next End Sub

'on error goto 当错误的时候去哪儿？ Sub onerrorgoto() On Error GoTo 100 For i = 2 To 8

k = Cells(i, 2) + Cells(i, 3) Next 100:

MsgBox \对不起，错误发生在第\行\End Sub 1-25 with语句

'with语句，当对某个对象执行一系列的语句时，不用重复指出对象的名名称。 Sub with语句()

a = Range(\ b = Range(\ Range(\End Sub

Sub with语句2()

17 / 62

With Range(\ a = .Address b = .Parent.Name .Value = \ End With End Sub

Sub with嵌套()

Range(\？\

Range(\ Range(\ Range(\End Sub

Sub with嵌套2() With Range(\

.Value = \

.Parent.Name = \ With .Font .Size = 20 .Bold = True End With End With End Sub

18 / 62

1-26 VBA与公式

'在VBA中也可以像在工作表中一样录入公式 Sub vba中的做法() Dim i%

For i = 1 To 10

Range(\i) Next End Sub Sub 普通公式()

Sheets(1).Cells(1, 3) = \End Sub Sub 批量计算() Dim i As Integer For i = 1 To 10

Sheets(1).Cells(i, 4) = \Next i End Sub Sub 数组公式()

Range(\End Sub

Sub 带工作表函数的计算()

19 / 62

Dim i As Integer For i = 1 To 10

Sheets(1).Cells(i, 3) = \i & \Next i End Sub

Sub 公式带引号的计算()

Cells(2, 5) = \Cells(3, 5) = \End Sub '借用工作表函数 Sub 运用工作表函数() MsgBox

Application.WorksheetFunction.CountIf(Range(\:a10\钢笔\MsgBox

WorksheetFunction.CountIf(Range(\钢笔\

MsgBox Application.CountIf(Range(\钢笔\End Sub 'VBA函数

20 / 62