VBA 常用知识点

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

巴特西

VBA 常用知识点

VBA对象传参

单元格背景色对应的索引值

最新文章

热门文章