VBA学习09_面向对象初显威力 WorkSheets玩转表单

1. Set 变量赋值

为代表对象的变量赋值时,必须使用SET关键字

Sub test()
	Dim w1 As WorkSheet
	Set w1  = WorkSheets(3)  '为代表对象的变量赋值时,必须使用SET关键字
	w1.Cells(5,3) = 100
End Sub

Sub test()
	Dim w1 As WorkSheet
	Dim i 
	For i = 1 To 10
		Set w1  = WorkSheets.Add  '为代表对象的变量赋值时,必须使用SET关键字
		w1.Cells(5,3) = 100
	Next i
End Sub

WorkSheets引用方式

用worksheet引用工作表的两种形式

1. 根据工作表的显示位置: WorkSheets(5)
2. 根据工作表的名称: WorkSheets(“总分表”)

代码示例

Sub 个人成绩汇总()
Dim i,r
Dim w1 As WorkSheet

For i = 1 to WorkSheets.Count
	s = 0
	Set w1 = WorkSheets(i)
	For r =2 to 10
		s = s + w1.cells(r,2)
		w1.Cells(2,3) = s
	Next r 
Next i 

End Sub 	

Sub 提取all_登记总榜单()									'提取个人表的姓名和成绩到总分表
    Dim i, k									' 定义变量
    Dim wPerson As Worksheet, wAll As Worksheet ' 个人表,总分表
    
    Set wAll = Worksheets("总分榜")				'设置变量
    
    k = 2										' 变量赋初始值
    
    For i = 1 To Worksheets.Count  				'当前工作簿内所有工作表循环
        
        Set wPerson = Worksheets(i)				'设置WPerson=对应每个工作表
        If wPerson.Name <> "总分榜" Then			'区分个人成绩表和总分表
        wAll.Cells(k, 1) = wPerson.Cells(1, 2)	'  将个人名字提取到总分表的对应位置
        wAll.Cells(k, 2) = wPerson.Cells(2, 3)	'	将个人成绩提取到总分表的对应位置
        
        k = k + 1
        End If
    Next i
    
End Sub

上述两段代码进行合并

    Dim i, j, k
    Dim wPerson As Worksheet, wAll As Worksheet
    
    Set wAll = Worksheets("总分榜")  '总表
    
    k = 2 '总表行量编号
     
    For i = 1 To Worksheets.Count  ' 逐表汇总数据
    
        s = 0                       '初始化每张表的分数数据
        
        Set wPerson = Worksheets(i) '个人成绩表变量
         
        If wPerson.Name <> "总分榜" Then
        
            For j = 2 To 10         ' 求和
                s = s + wPerson.Cells(j, 2)
                wPerson.Cells(2, 3) = s
            Next j
                                    ' 提取
            wAll.Cells(k, 1) = wPerson.Cells(1, 2)
            wAll.Cells(k, 2) = wPerson.Cells(2, 3)
            
            k = k + 1
        
        End If
        
    Next i
    
End Sub