如何修复VBA Dim变量赋值时出现的「编译错误：需要对象」问题

核心错误原因 #

• VBA中Set关键字仅可用于给对象类型变量（如Worksheet、Range、Workbook等）赋值，普通数据类型（字符串、数值、日期、布尔值等）赋值无需加Set

你最初触发报错的代码行，本质是对普通数据类型错误使用了Set关键字：

' 以下两行属于错误写法，strbranch是字符串、ddate是日期/数值，都不是对象
Set strbranch = report.Cells(4, 3).Value
Set ddate = report.Cells(2, 3).Value

另外你调整后的代码仍存在一处同类隐患：val1/val2/val3是普通字符串，赋值也不需要加Set，否则运行时仍会触发相同报错。 #

原始工作表计算逻辑 #

你之前在工作表中实现的正常计算函数如下：

=COUNTIFS(Test!$G:$G;Лист1!C4;Test!$I:$I;Page1!K2)
    +COUNTIFS(Test!$G:$G;Page1!C4;Test!$I:$I;Page1!L2)
    +COUNTIFS(Test!$G:$G;Page1!C4;Test!$I:$I;Page1!M2)

最终可正常运行的宏代码 #

Sub count_if()
    Dim rngstatus As Range
    Dim rngbranch As Range
    Dim rngdate As Range

    Dim report As Worksheet
    Dim lib As Worksheet

    Dim result1 As Integer
    Dim result2 As Integer
    Dim strbranch
    Dim ddate  As Double
    Dim val1
    Dim val2
    Dim val3

    ' 对象类型变量赋值用Set
    Set lib = Worksheets("Test")
    Set report = Worksheets("Report")

    Set rngstatus = lib.Range("$I:$I")
    Set rngbranch = lib.Range("$G:$G")
    Set rngdate = lib.Range("$F:$F")

    ' 普通数据类型赋值不用Set
    strbranch = report.Cells(4, 3).Value
    ddate = report.Cells(2, 3).Value2
    
    ' 字符串属于普通数据类型，赋值不用Set
    val1 = "Word1"
    val2 = "Word2"
    val3 = "Word3"
    
    result1 = WorksheetFunction.CountIfs(rngbranch, strbranch, rngstatus, val1) + _
    WorksheetFunction.CountIfs(rngbranch, strbranch, rngstatus, val2) + _
    WorksheetFunction.CountIfs(rngbranch, strbranch, rngstatus, val3)
    
    result2 = WorksheetFunction.CountIfs(rngdate, "<" & ddate, rngbranch, strbranch, rngstatus, val1) + _
    WorksheetFunction.CountIfs(rngdate, "<" & ddate, rngbranch, strbranch, rngstatus, val2) + _
    WorksheetFunction.CountIfs(rngdate, "<" & ddate, rngbranch, strbranch, rngstatus, val3)
    
    MsgBox "Actual " & result1 & ", Failed deadline" & result2
    
    report.Cells(6, 3) = result1
    report.Cells(7, 3) = result2
End Sub

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