调用公共函数时属性误用：Excel VBA单元格着色函数求助

我接触VBA才几周，正在编写一个Excel公共函数CellColorer，核心功能是：

• 检查用户指定的日期是否处于某项目的起止日期区间内，若是则将当前单元格的颜色设为对应表格行的颜色
• 若恰好是项目起始或结束日期，同时显示对应提示文本

目前调用函数时弹出错误提示Invalid Use of Property when Calling public Function，且只能通过创建子过程jili勉强调试，断断续续开发很难排查问题，求帮助。

原代码 #

Public Function CellColorer(ByRef dayNo As Range)
Dim checkDate As Date
Dim table1 As ListObject

    table1 = ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1")
    checkDate = CDate(CStr(dayNo.Range(1, 1).Value) + CStr(Evaluate(ActiveWorkbook.Names("MoMonth"))) + CStr(Evaluate(ActiveWorkbook.Names("MoYear"))))
    
    Dim i As Integer
    For i = 1 To table1.ListRows.Count
        If table1.DataBodyRange(i, 1) < checkDate & table1.DataBodyRange(i, 2) > checkDate Then
            Application.ThisCell.Interior.Color = table1.DataBodyRange(i, 1).Interior.Color
            CELLCOLOURER = ""
            Return
        ElseIf table1.DataBodyRange(i, 1) = checkDate Then
                Application.ThisCell.Interior.Color = table1.DataBodyRange(i, 1).Interior.Color
                CELLCOLOURER = table1.DataBodyRange(i, 0) + "START!"
                Return
        ElseIf table1.DataBodyRange(i, 2) = checkDate Then
                Application.ThisCell.Interior.Color = table1.DataBodyRange(i, 1).Interior.Color
                CELLCOLOURER = table1.DataBodyRange(i, 0) + "DUE!"
                Return
    Next i

End Function
Sub jili() 'I'm using this code to debug I couldn't figure out how to do so without it
CellColorer (ActiveWorkbook.Worksheets(2).Range("E6"))
End Sub

错误修正点 #

• 对象赋值必须用Set：ListObject是对象，直接赋值table1 = ...会触发属性错误，需改为Set table1 = ...
• 日期构造逻辑优化：用DateSerial直接构造日期，避免字符串拼接导致的格式错误，替代原有的CStr+CDate逻辑
• 逻辑运算符错误：VBA中逻辑与是And，不是字符串连接符&，原条件中的&会导致逻辑判断失效
• 函数返回值规范：函数名统一为CellColorer（原代码中出现大写的CELLCOLOURER），且显式声明返回类型为String
• 单元格索引修正：ListObject的DataBodyRange列索引从1开始，i,0会越界，改为对应列的索引（比如项目名称列用i,1，需根据实际表格调整）
• 退出函数语句错误：VBA函数中退出需用Exit Function，不是Return
• 函数调用方式修正：子过程中调用函数时，去掉不必要的括号（或用Call语句），避免将Range对象转为Variant
• 补充无匹配场景的处理：循环结束后添加默认返回值，避免函数无返回的情况

修正后的代码 #

Public Function CellColorer(ByRef dayNo As Range) As String
    Dim checkDate As Date
    Dim table1 As ListObject
    Dim moMonth As Integer, moYear As Integer
    Dim dayVal As Integer
    
    ' 获取命名区域的值
    moMonth = ActiveWorkbook.Names("MoMonth").RefersToRange.Value
    moYear = ActiveWorkbook.Names("MoYear").RefersToRange.Value
    dayVal = dayNo.Value
    
    ' 构造检查日期
    checkDate = DateSerial(moYear, moMonth, dayVal)
    
    ' 赋值ListObject对象
    Set table1 = ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1")
    
    Dim i As Integer
    For i = 1 To table1.ListRows.Count
        Dim startDate As Date, endDate As Date
        startDate = table1.DataBodyRange(i, 1).Value
        endDate = table1.DataBodyRange(i, 2).Value
        
        ' 检查日期是否在区间内
        If startDate < checkDate And endDate > checkDate Then
            Application.ThisCell.Interior.Color = table1.DataBodyRange(i, 1).Interior.Color
            CellColorer = ""
            Exit Function
        ' 检查是否为起始日期
        ElseIf startDate = checkDate Then
            Application.ThisCell.Interior.Color = table1.DataBodyRange(i, 1).Interior.Color
            ' 假设第1列是项目名称，根据实际表格调整列索引
            CellColorer = table1.DataBodyRange(i, 1).Value & " START!"
            Exit Function
        ' 检查是否为结束日期
        ElseIf endDate = checkDate Then
            Application.ThisCell.Interior.Color = table1.DataBodyRange(i, 1).Interior.Color
            CellColorer = table1.DataBodyRange(i, 1).Value & " DUE!"
            Exit Function
        End If
    Next i
    
    ' 无匹配时的默认返回值
    CellColorer = ""
End Function

Sub jili()
    ' 调用函数时去掉括号，或使用Call语句
    CellColorer ActiveWorkbook.Worksheets(2).Range("E6")
    ' 也可以写成：Call CellColorer(ActiveWorkbook.Worksheets(2).Range("E6"))
End Sub

内容的提问来源于stack exchange，提问作者DWaterston