VBA对象传参
- 首先主函数中必须定义参数的类型
- 函数调用语法为
函数名 参数1 参数2
- 被调用函数中定义传参是否引用(byref)还是重新建立一份数据(byval)
代码示例
Sub auto_count()
Dim wb_obj As Workbook
Dim wsh_obj As Worksheet
filepth = get_file_pth()
Set wb_obj = GetObject(filepth)
Set wsh_obj = wb_obj.Worksheets("工作博1")
total_check wsh_obj '对象传参看这步
'GetObject 默认保存后文件内容不可见,要设置可视为真才能正常浏览
Application.Windows(wb_obj.Name).Visible = True
' 文件保存设置为真
wb_obj.Close True
End Sub
Sub total_check(ByRef wst As Worksheet)
'表中E列总金额和F-I列的4个季度收费金额总和做比对
'
With wst
max_row = .Range("a:a").Rows.Count
last_row = .Range("a" & max_row).End(xlUp).Row
For i = 6 To last_row
total_money = .Range("e" & i).Value
first_quarter = .Range("f" & i).Value
second_quarter = .Range("g" & i).Value
three_quarter = .Range("h" & i).Value
four_quarter = .Range("i" & i).Value
'判断第一到第四季度和E列总金额是否相等,不相等,E列单元格底色变红色
If total_money <> (first_quarter + second_quarter + three_quarter + four_quarter) Then
.Range("E" & i).Interior.ColorIndex = 6
.Range("E" & i).ClearComments
.Range("E" & i).AddComment ("金额有误")
Else
.Range("E" & i).Interior.ColorIndex = 0
.Range("E" & i).ClearComments
End If
If .Range("k" & i).Value > 0 Then
a = .Range("k" & i)
rate_set .Range("k" & i)
End If
Next
End With
End Sub
单元格背景色对应的索引值
Range("E" & i).Interior.ColorIndex = 6