VBA如何将工作表名称设为变量？修改代码后运行报错如何解决

错误原因 #

• 直接触发报错的原因：你将变量SheetName用双引号包裹后，VBA会将其识别为固定字符串"SheetName"，会去查找名称为「SheetName」的工作表，而非读取你提前赋值的SheetName变量存储的目标工作表名，工作簿不存在该工作表就会触发下标越界错误。
• 代码还存在其他隐藏问题：
  • 条件判断行残留了硬编码的Worksheets("0618")，没有替换为动态变量，会导致判断逻辑错误
  • 输出逻辑中用到的变量M没有提前定义和赋值，运行到超过5条数据的分支时也会触发报错

修正后的完整代码 #

Sub WriteToZeroBasedArray()
    
    Dim SheetName As String
    SheetName = Worksheets("Sheet3").Cells(3, 10).Value
    Dim V() As Variant ' Note the parentheses!
    Dim P() As Variant
    Dim N() As Variant
    Dim Index As Long
    Dim Index_2 As Long
    Dim Index_3 As Long
    ' 去掉SheetName的双引号，读取变量值
    Dim cCount As Long: cCount = Worksheets(SheetName).Cells.SpecialCells(xlLastCell).Column ' e.g.
    
    Dim c As Long
    For c = 7 To cCount
        ' 替换硬编码的0618为动态变量
        If 0 < Worksheets(SheetName).Cells(9, c).Value And Worksheets(SheetName).Cells(9, c).Value < 100 Then
            ReDim Preserve V(Index)
            ' A safer way (Option Base related):
            'ReDim Preserve V(0 To Index)
            V(Index) = Worksheets(SheetName).Cells(2, c).Value
            Index = Index + 1
            
            ReDim Preserve P(Index_2)
            P(Index_2) = Worksheets(SheetName).Cells(4, c).Value
            Index_2 = Index_2 + 1
            
            ReDim Preserve N(Index_3)
            N(Index_3) = Worksheets(SheetName).Cells(9, c).Value
            Index_3 = Index_3 + 1
        End If
    Next c
    
     
    Dim K As Integer
    Dim L As Integer
    Dim M As Long ' 补充定义M变量
    
    K = UBound(V)
    For L = 0 To K
      If L < 5 Then
        Worksheets("sheet3").Cells(L + 6, 1).Value = V(L)
        Worksheets("sheet3").Cells(L + 6, 2).Value = P(L)
        Worksheets("sheet3").Cells(L + 6, 3).Value = N(L)
      Else
        M = L - 5 ' 给M赋值，保证超过5条的数据从E列第6行开始排列
        Worksheets("sheet3").Cells(M + 6, 5).Value = V(L)
        Worksheets("sheet3").Cells(M + 6, 6).Value = P(L)
        Worksheets("sheet3").Cells(M + 6, 7).Value = N(L)
      End If
      
    Next
    
    If Index = 0 Then Exit Sub
    
    Debug.Print Join(V, vbLf)
    

End Sub

内容的提问来源于stack exchange，提问作者김다영