Excel VBA定义Range触发'Application defined或对象定义错误'求助

问题描述 #

在Excel VBA中定义多个Range对象时，触发Application defined or object defined error错误，报错行位于：

Set r = SCH22.Worksheets("CONTFRM22-23").Range("A" & i, "B" & i)

同时希望了解定义多参数Range的更优写法。相关代码如下：

Dim r As Range
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim r4 As Range
Dim r5 As Range
Dim r6 As Range
Dim r7 As Range
Dim r8 As Range
Dim multiplerange As Range


Set SCH22 = Workbooks.Open("G:\CONTRFRM 22-23.xlsm")


Set r = SCH22.Worksheets("CONTFRM22-23").Range("A" & i, "B" & i)
Set r1 = SCH22.Worksheets("CONTFRM22-23").Range("C" & i, "D" & i)
Set r2 = SCH22.Worksheets("CONTFRM22-23").Range("E" & i, "F" & i)
Set r3 = SCH22.Worksheets("CONTFRM22-23").Range("H" & i, "J" & i)
Set r4 = SCH22.Worksheets("CONTFRM22-23").Range("K" & i, "L" & i)
Set r5 = SCH22.Worksheets("CONTFRM22-23").Range("M" & i, "P" & i)
Set r6 = SCH22.Worksheets("CONTFRM22-23").Range("Q" & i, "R" & i)
Set r7 = SCH22.Worksheets("CONTFRM22-23").Range("S" & i, "Y" & i)
Set r8 = SCH22.Worksheets("CONTFRM22-23").Range("AD" & i)

Set multiplerange = Union(r1, r2, r3, r4, r5, r6, r7, r8)

报错原因分析 #

• 变量i未定义或值无效：如果i没有提前声明并赋值，或者赋值为非正整数（比如0、负数、字符串），会导致生成的单元格地址无效，触发错误。
• 工作表不存在：确认目标工作簿中是否存在名为CONTFRM22-23的工作表，名字大小写、特殊符号必须完全匹配。
• 文件路径或权限问题：Workbooks.Open可能未成功打开文件（比如路径错误、文件被占用、没有读取权限），导致SCH22对象无效，后续引用工作表自然报错。
• 单元格地址超出范围：如果i的值超过工作表的最大行数（Excel 2007及以后是1048576行），生成的地址无效也会报错。

多Range定义的优化实现方式 #

1. 使用With语句减少重复引用 #

通过With绑定目标工作表，避免重复书写长路径，提升代码可读性和效率：

Dim multiplerange As Range
Dim SCH22 As Workbook
Dim ws As Worksheet
Dim i As Long ' 必须声明并赋值i，比如i=5

Set SCH22 = Workbooks.Open("G:\CONTRFRM 22-23.xlsm")
Set ws = SCH22.Worksheets("CONTFRM22-23")

With ws
    Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range
    Dim r5 As Range, r6 As Range, r7 As Range, r8 As Range
    
    Set r1 = .Range("C" & i, "D" & i)
    Set r2 = .Range("E" & i, "F" & i)
    Set r3 = .Range("H" & i, "J" & i)
    Set r4 = .Range("K" & i, "L" & i)
    Set r5 = .Range("M" & i, "P" & i)
    Set r6 = .Range("Q" & i, "R" & i)
    Set r7 = .Range("S" & i, "Y" & i)
    Set r8 = .Range("AD" & i)
    
    Set multiplerange = Union(r1, r2, r3, r4, r5, r6, r7, r8)
End With

2. 用数组存储区域地址，循环创建Range并合并 #

适合区域较多的场景，减少重复代码：

Dim multiplerange As Range
Dim SCH22 As Workbook
Dim ws As Worksheet
Dim i As Long
Dim areaAddresses As Variant
Dim addr As Variant

i = 5 ' 示例赋值，根据实际需求设置
areaAddresses = Array("C" & i & ":D" & i, _
                     "E" & i & ":F" & i, _
                     "H" & i & ":J" & i, _
                     "K" & i & ":L" & i, _
                     "M" & i & ":P" & i, _
                     "Q" & i & ":R" & i, _
                     "S" & i & ":Y" & i, _
                     "AD" & i)

Set SCH22 = Workbooks.Open("G:\CONTRFRM 22-23.xlsm")
Set ws = SCH22.Worksheets("CONTFRM22-23")

For Each addr In areaAddresses
    If multiplerange Is Nothing Then
        Set multiplerange = ws.Range(addr)
    Else
        Set multiplerange = Union(multiplerange, ws.Range(addr))
    End If
Next addr

3. 直接在Union中拼接Range（适合少量区域） #

如果区域数量不多，可直接在Union中一次性构建，减少中间变量：

Dim multiplerange As Range
Dim SCH22 As Workbook
Dim ws As Worksheet
Dim i As Long

i = 5
Set SCH22 = Workbooks.Open("G:\CONTRFRM 22-23.xlsm")
Set ws = SCH22.Worksheets("CONTFRM22-23")

With ws
    Set multiplerange = Union(.Range("C" & i, "D" & i), _
                              .Range("E" & i, "F" & i), _
                              .Range("H" & i, "J" & i), _
                              .Range("K" & i, "L" & i), _
                              .Range("M" & i, "P" & i), _
                              .Range("Q" & i, "R" & i), _
                              .Range("S" & i, "Y" & i), _
                              .Range("AD" & i))
End With

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