VBA添加数组元素时出现Runtime Error 9（下标越界）问题求助

问题分析 #

你遇到的下标越界错误，核心原因有两点：

• 动态数组haystack()未初始化就直接赋值，VBA无法识别数组的大小范围
• 未处理工作表名称中不含(或)的情况，会导致Mid函数参数异常，间接引发后续赋值问题

修复方案 #

方案1：使用动态数组（适配工作表数量变化） #

先通过ReDim根据工作表总数初始化数组，同时添加括号存在性判断，避免Mid函数报错：

Dim num As Integer
Dim newEntry As String
Dim haystack() As String
Dim wsCount As Integer
Dim openPos As Integer, closePos As Integer

wsCount = ActiveWorkbook.Worksheets.Count
' 初始化动态数组，下标从1到工作表总数
ReDim haystack(1 To wsCount)

For num = 1 To wsCount
    openPos = InStr(Worksheets(num).Name, "(")
    closePos = InStr(Worksheets(num).Name, ")")
    
    ' 先判断括号是否存在且位置合理
    If openPos > 0 And closePos > openPos Then
        newEntry = Mid(Worksheets(num).Name, openPos + 1, closePos - openPos - 1)
        If IsNumeric(newEntry) Then
            haystack(num) = newEntry
        End If
    End If
Next num

方案2：使用固定大小数组（已知工作表数量时） #

如果确定工作簿固定111个工作表，直接声明数组大小，同时保留括号判断逻辑：

Dim num As Integer
Dim newEntry As String
' 直接声明固定大小数组
Dim haystack(1 To 111) As String
Dim wsCount As Integer
Dim openPos As Integer, closePos As Integer

wsCount = ActiveWorkbook.Worksheets.Count
For num = 1 To wsCount
    openPos = InStr(Worksheets(num).Name, "(")
    closePos = InStr(Worksheets(num).Name, ")")
    
    If openPos > 0 And closePos > openPos Then
        newEntry = Mid(Worksheets(num).Name, openPos + 1, closePos - openPos - 1)
        If IsNumeric(newEntry) Then
            haystack(num) = newEntry
        End If
    End If
Next num

关键说明 #

• 动态数组必须通过ReDim指定大小后才能赋值，否则VBA无法分配内存空间
• 添加括号位置的有效性判断，避免因工作表名称格式不符合预期导致的Mid函数参数错误（比如右括号在左括号之前，或其中一个括号不存在）

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