VBA代码编译错误（类型不匹配）：文本响应分组统计问题求助

问题背景 #

已有可按对照组（Control）/暴露组（Exposed）生成数值数据列表的VBA宏，现在需要扩展功能：生成两组受试者的文本响应列表（响应值为"NA"、"Never"、"Rarely"、"Sometimes"、"Frequently"），并统计每组各响应类型的总数。编写的新代码本应在"PRISM-Ready"工作表F163单元格输出对照组中"NA"的响应次数，但运行时触发Compile error: Type mismatch，高亮Cars变量，修改变量类型为String/Variant后仍报错。

原有数值处理代码：

Dim Subjects As Integer
Subjects = Sheets("Participant Info").Range("C4:C39").Count
        
Dim group As String, Rspns As Long
Dim r As Long, i As Long, j As Long
       
Dim arC, arE
ReDim arC(1 To Subjects)
ReDim arE(1 To Subjects)

With Sheets("Participant Info")
    For r = 4 To Subjects + 3
        group = .Cells(r, "E").Value
        Rspns = .Cells(r, "C").Value
        If group = "Control" Then
             i = i + 1
             arC(i) = Rspns
        End If
        If group = "Exposed" Then
             j = j + 1
             arE(j) = Rspns
        End If
    Next
End With
Sheets("PRISM-Ready").Activate
For i = 1 To i
    Sheets("PRISM-Ready").Cells(i + 2, 1).Value = arC(i)
Next
For j = 1 To j
    Sheets("PRISM-Ready").Cells(j + 2, 2).Value = arE(j)
Next

出错的文本统计代码：

Dim NAcount As String, Ncount As String, Rcount As String, Scount As String, Fcount As String
Dim arNA, arN, arR, arS, arF

Dim CHCars As String, Cars As String, Cars1 As String
    With Sheets("Participant Info")
        For r = 4 To Subjects + 3
            group = .Cells(r, "E").Value
            CHCars = .Cells(r, "F").Value
            If group = "Control" Then
                Cars = Cars + 1
                arC(Cars) = CHCars
            End If
            If group = "Exposed" Then
                Cars1 = Cars1 + 1
                arE(Cars1) = CHCars
            End If
        Next
    End With
    For Cars = 1 To UBound(arC)
         If arC(Cars) = "NA" Then
            NAcount = NAcount + 1
            arNA(NAcount) = CHCars
         End If
    Next
    Sheets("PRISM-Ready").Activate
        Sheets("PRISM-Ready").Cells("F163").Value = arNA(NAcount).Count

错误原因 #

1. 计数变量类型错误：Cars、Cars1、NAcount等用于计数的变量被定义为String类型，字符串无法执行数值加法操作（Cars = Cars + 1），直接导致类型不匹配。
2. 数组未初始化：arNA等数组未使用ReDim分配存储空间，直接赋值会触发错误。
3. 循环逻辑错误：循环中使用Cars作为循环变量，与之前的计数变量重名；且UBound(arC)对应的数组包含未赋值的空白元素（原有代码中arC按最大受试者数初始化，但实际对照组人数可能更少），遍历会导致无效判断。
4. 错误的统计结果输出：VBA数组没有Count属性，arNA(NAcount).Count是非法语法，统计结果直接用计数变量即可。
5. 变量未显式声明：r变量在新代码中未声明，虽VBA允许隐式声明，但易引发类型混乱。

修正后的完整代码 #

Sub CountTextResponses()
    Dim Subjects As Integer
    Subjects = Sheets("Participant Info").Range("C4:C39").Count
    
    Dim group As String, CHCars As String
    Dim r As Long, Cars As Long, Cars1 As Long
    Dim NAcount As Long, Ncount As Long, Rcount As Long, Scount As Long, Fcount As Long
    
    ' 初始化存储对照组/暴露组文本响应的数组
    Dim arC() As String, arE() As String
    ReDim arC(1 To Subjects)
    ReDim arE(1 To Subjects)
    
    ' 读取分组及文本响应
    With Sheets("Participant Info")
        For r = 4 To Subjects + 3
            group = .Cells(r, "E").Value
            CHCars = .Cells(r, "F").Value
            
            If group = "Control" Then
                Cars = Cars + 1
                arC(Cars) = CHCars
            ElseIf group = "Exposed" Then
                Cars1 = Cars1 + 1
                arE(Cars1) = CHCars
            End If
        Next r
    End With
    
    ' 统计对照组中各响应类型的数量
    For r = 1 To Cars ' 仅遍历实际有数据的元素
        Select Case arC(r)
            Case "NA"
                NAcount = NAcount + 1
            Case "Never"
                Ncount = Ncount + 1
            Case "Rarely"
                Rcount = Rcount + 1
            Case "Sometimes"
                Scount = Scount + 1
            Case "Frequently"
                Fcount = Fcount + 1
        End Select
    Next r
    
    ' 输出对照组"NA"的统计结果到指定单元格
    Sheets("PRISM-Ready").Range("F163").Value = NAcount
End Sub

关键修正点 #

• 将所有计数变量（Cars、NAcount等）改为Long类型，确保数值运算合法。
• 显式声明所有变量，推荐在模块顶部添加Option Explicit强制变量声明，避免隐式声明的类型问题。
• 遍历数组时仅遍历实际有数据的元素（For r = 1 To Cars），避免处理空白元素。
• 使用Select Case简化多条件判断，提升代码可读性。
• 直接用计数变量NAcount输出统计结果，替代错误的数组Count属性调用。

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