VBA For循环操作工作表数组时IF语句触发下标越界错误求助

VBA下标越界错误原因及修正方案 #

错误原因

• 多余使用Array()函数包裹Range对象：VBA中直接把UsedRange赋值给Variant类型变量时，会自动转换为1基的二维数组（行、列分别对应第一、第二维度）。套了Array()后，arr和CAR变成了一维数组，数组内只有1个元素就是原本的二维区域数组，此时直接访问arr(i,5)相当于给一维数组传了两个下标，直接触发越界。
• 数组声明写法错误：你声明了Dim arr() As Variant，这是显式声明动态数组，但直接接收Range赋值不需要声明为数组形式，直接写Dim arr As Variant即可，否则赋值逻辑不符合时也会引发下标异常。
• 边界校验缺失：如果Assignments表的已用区域列数小于7，或者CARMA2表的已用区域列数小于3，访问第5、7、3列时也会触发越界。
• 条件判断逻辑写错：你写的arr(i, 7) = """"是判断该位置值是否等于单个英文双引号，如果你是要判断单元格为空，应该写arr(i,7) = ""或者arr(i,7) = vbNullString。

修正后代码

Dim CARMA2 As Worksheet
Dim Assignments As Worksheet
Sub data2()
    Dim arr As Variant
    Dim CAR As Variant
    ' 直接赋值UsedRange的值到Variant变量，自动生成二维数组
    arr = Worksheets("Assignments").UsedRange.Value
    CAR = Worksheets("CARMA2").UsedRange.Value
    Dim x As Long, i As Long
    
    ' 边界校验，避免列数不足越界
    If UBound(arr, 2) < 7 Or UBound(CAR, 2) < 3 Then
        MsgBox "工作表已用区域列数不足，请检查数据"
        Exit Sub
    End If
    
    For x = LBound(CAR, 1) To UBound(CAR, 1)
        For i = LBound(arr, 1) To UBound(arr, 1)
            ' 判断为空用""即可
            If arr(i, 5) = CAR(x, 1) And arr(i, 7) = "" Then
                arr(i, 7) = CAR(x, 3)
            End If
        Next i
    Next x
    
    ' 需要把修改后的数组写回工作表则保留下面这行
    Worksheets("Assignments").UsedRange.Value = arr
End Sub

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