VBA报错91：未设置对象变量/With块变量，Range需提前赋值？

问题描述 #

编写Excel VBA宏InfogaPrislista时触发错误91：Object variable or with block variable not set，困惑点在于不理解为何不能先让Range变量为Nothing再在代码中赋值。需求是遍历工作表列表查找三个特定表头，错误出现在For循环执行Find方法时。

原代码 #

Sub InfogaPrislista()
'
' InfogaPrislista Makro
' Adds a price list and updates the code in excel
'
    Dim sKolumn As Range 'Fuse Column
    Dim fKolumn As Range 'Fixed price kolumn
    Dim rKolumn As Range 'Variable price kolumn
    
    Dim Säkringar As ArrayList 'List with fuses
    Dim Blad As ArrayList 'List with sheets
    
    Set Säkringar = New ArrayList
    Set Blad = New ArrayList
    
    'Adding fuses to list
    Säkringar.Add prislistor.Cells(4, 1)
    Säkringar.Add prislistor.Cells(5, 1)
    Säkringar.Add prislistor.Cells(6, 1)
    Säkringar.Add prislistor.Cells(10, 1)
    Säkringar.Add prislistor.Cells(11, 1)
    Säkringar.Add prislistor.Cells(12, 1)
    Säkringar.Add prislistor.Cells(13, 1)
    Säkringar.Add prislistor.Cells(14, 1)
    Säkringar.Add prislistor.Cells(15, 1)
    Säkringar.Add prislistor.Cells(20, 1)
    Säkringar.Add prislistor.Cells(21, 1)
    Säkringar.Add prislistor.Cells(25, 1)
    Säkringar.Add prislistor.Cells(26, 1)
    
    'Adding sheets to list
    Blad.Add brududden
    Blad.Add rinnefors
    Blad.Add järpforsen
    Blad.Add rexed
    Blad.Add sälboda
    
    If prislistor.Range("B29") = "Enfas" Then
    
        ' Adds a specific fuse price list
        'Sometimes I get an error here, but I think it depends on where and what I'm doing in the Excel workbook
        prislistor.Range("A2:C7").Select
        Selection.Copy
        prislistor.Range("D2").Select
        Selection.Insert Shift:=xlToRight
        Application.CutCopyMode = False
        prislistor.Range("E4:F7").Select
        Selection.ClearContents
        prislistor.Range("C29").Select
        
        'This is supposed to search through the headings in every sheet in the sheets list for the different columns I set as ranges at row 6-8 in this code but I get error nr 91  
        Dim i as Long
        For i = 0 To 4
            sKolumn = Blad(i).Cells(1, 1).EntireRow.Find(What:="Säkring", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
            rKolumn = Blad(i).Cells(1, 1).EntireRow.Find(What:="Rörligtpris nät", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
            fKolumn = Blad(i).Cells(1, 1).EntireRow.Find(What:="Fastpris nät", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
        Next i  
     End if  
End Sub

错误原因及解决方法 #

1. 变量类型不匹配+Find结果未校验
   你声明sKolumn/fKolumn/rKolumn为Range类型，但直接赋值.Column（数字类型）给Range变量，本身就类型不匹配。更致命的是：如果Find方法未找到目标，会返回Nothing，此时直接访问.Column就会触发错误91。

2. 修改变量类型+先判断再赋值
   建议将列号存储为Long类型（列号是数字），而非Range。每次调用Find后，先检查结果是否有效，再提取列号：

   ' 把Range变量改成Long类型，存储列号
   Dim sKolumn As Long 
   Dim fKolumn As Long 
   Dim rKolumn As Long
   Dim findResult As Range ' 临时存储Find结果
   
   For i = 0 To 4
       ' 查找"Säkring"列
       Set findResult = Blad(i).Cells(1, 1).EntireRow.Find(What:="Säkring", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
       If Not findResult Is Nothing Then
           sKolumn = findResult.Column
       Else
           ' 没找到时的处理逻辑，比如提示
           MsgBox Blad(i).Name & "中未找到表头:Säkring"
           sKolumn = 0 ' 或其他默认值
       End If
       
       ' 查找"Rörligtpris nät"列
       Set findResult = Blad(i).Cells(1, 1).EntireRow.Find(What:="Rörligtpris nät", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
       If Not findResult Is Nothing Then
           rKolumn = findResult.Column
       Else
           MsgBox Blad(i).Name & "中未找到表头:Rörligtpris nät"
           rKolumn = 0
       End If
       
       ' 查找"Fastpris nät"列
       Set findResult = Blad(i).Cells(1, 1).EntireRow.Find(What:="Fastpris nät", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
       If Not findResult Is Nothing Then
           fKolumn = findResult.Column
       Else
           MsgBox Blad(i).Name & "中未找到表头:Fastpris nät"
           fKolumn = 0
       End If
   Next i
   

3. 关于Range变量为Nothing的疑问
   Range变量默认确实是Nothing，但你不能对Nothing的变量执行任何成员访问（比如.Column）。必须通过Set语句给它赋值一个有效的Range对象后，才能操作它的属性或方法。如果Find失败返回Nothing，直接访问.Column必然报错，所以必须先判断findResult Is Nothing。

4. 额外优化建议

   • 避免使用Select和Selection，直接操作Range对象可提升代码稳定性和速度，比如把复制插入代码改成：

     prislistor.Range("A2:C7").Copy
     prislistor.Range("D2").Insert Shift:=xlToRight
     Application.CutCopyMode = False
     prislistor.Range("E4:F7").ClearContents
     

   • 确保prislistor、brududden等工作表对象已正确定义，避免因工作表名称变更导致引用失败。

内容的提问来源于stack exchange，提问作者Tim AKA Kaspian