Excel VBA通过变量激活工作表时出现运行时错误9的技术问询

我正在开发一个用于投资组合管理的中型Excel电子表格，目的是减少手动复制粘贴操作。为模拟多年赤字情况，创建了多个以年份命名的工作表（例如：2025），目标是遍历全部65个年份工作表，将从其他工作表存入数组的值粘贴进去，生成年度投资组合汇总。

但使用循环索引生成的变量动态引用工作表时，脚本抛出以下错误：

运行时错误9

直接硬编码年份（如Sheets("2025").Activate）可以正常激活工作表，调试显示所有变量值均符合预期。尝试过将变量以整数、字符串、拼接字符串、强类型传递等方式处理，仅硬编码工作表名称的方式有效，希望实现通过变量动态值调用Activate方法激活对应工作表。

相关VBA代码如下：

Sub create_yearly_sheets()
    Dim current_year_string As String
    Sheets("supplemental needs").Activate
    from_year = Range("C2").Value
    to_year = Range("C3").Value
    
    Dim copying_array(0 To 100) As Variant
    
    Dim city_array(0 To 50) As String
    
    ' 获取所有城市名称用于工作表选择
    Sheets("unit allocations").Activate
    For i = 2 To 100 Step 1
        If Range("A" & i).Value <> "" Then
            city_array(i - 2) = Range("A" & i).Value
        Else
            Exit For
        End If
    Next i
    
    For k = from_year To to_year Step 1
        current_year = k
        For i = 0 To UBound(city_array) Step 1
            If city_array(i) <> "" Then
                Sheets(city_array(i)).Activate
                For j = 1 To 50 Step 1
                    If Cells(5, j).Value = current_year Then
                        current_year_column = j
                        Exit For
                    End If
                Next j
                
                For h = 6 To 100 Step 1
                    If Cells(h, current_year_column) <> "" Then
                        copying_array(q) = Cells(h, current_year_column).Value
                        q = q + 1
                    End If
                Next h
                
                ' 切换到年份工作表并粘贴数据
                current_year_string = Str(current_year)
                ThisWorkbook.Worksheets(current_year_string).Activate
                i_last_empty_cell = Cells(Cells.Rows.Count, "B").End(xlUp).Row ' 找到最后一个有数据的单元格行号
                For p = 2 To UBound(copy_array) + 2 Step 1
                    If copy_array(p - 2) <> "" Then
                        Cells(i_last_empty_cell, p).Value = copy_array(p - 2)
                    Else
                        Exit For
                    End If
                Next p
            End If
        Next i
    Next k
End Sub

问题根源 #

Str(current_year)函数会在转换后的字符串前添加前导空格，例如Str(2025)返回的是" 2025"，而你的工作表名称是无空格的"2025"，导致找不到对应工作表，触发下标越界的运行时错误9。

修复方案 #

1. 修正年份字符串转换方式 #

替换Str(current_year)为不会添加前导空格的转换方法：

' 方法1：使用CStr函数
current_year_string = CStr(current_year)
' 方法2：直接拼接空字符串
current_year_string = current_year & ""

2. 额外优化建议（提升代码稳定性与效率） #

• 避免依赖Activate/Select：直接通过工作表对象引用单元格，减少切换工作表的开销与错误概率，示例：

  With Sheets(city_array(i))
      ' 查找年份对应的列，用Find替代循环更高效
      Dim yearFound As Range
      Set yearFound = .Rows(5).Find(current_year, LookIn:=xlValues, LookAt:=xlWhole)
      If Not yearFound Is Nothing Then
          current_year_column = yearFound.Column
      End If
      ' 后续操作均用.前缀引用该工作表的单元格
  End With
  

• 统一数组命名：代码中存在copying_array和copy_array命名不一致的问题，需统一为同一个名称，避免运行时找不到数组。
• 初始化变量：变量q未显式初始化，建议在循环前添加q = 0，避免意外取值。
• 简化单元格引用：操作年份工作表时，使用With语句封装，避免反复调用Activate：

  current_year_string = CStr(current_year)
  With ThisWorkbook.Worksheets(current_year_string)
      i_last_empty_cell = .Cells(.Rows.Count, "B").End(xlUp).Row
      For p = 2 To UBound(copying_array) + 2 Step 1
          If copying_array(p - 2) <> "" Then
              .Cells(i_last_empty_cell, p).Value = copying_array(p - 2)
          Else
              Exit For
          End If
      Next p
  End With
  

内容的提问来源于stack exchange，提问作者Tyler Minix Glenn