VBA保存工作簿后赋值变量报‘Out of subscription’错误求助

错误原因 #

Workbooks集合的索引参数只能是**工作簿的文件名（不含路径）**或者索引序号，直接传入完整文件路径会导致集合找不到对应项，触发下标越界（Out of subscription）错误。

修复方案 #

方案1：直接复用原Workbook对象（最简洁）

执行SaveAs后，原wb对象已经指向新保存的工作簿，无需重新查找：

' 删掉无效的Declare语句
Dim wb As Workbook
Dim newwb As Workbook

' 假设wb已经指向要保存的工作簿
wb.SaveAs Filename:="C:\Users\XXXX\Desktop\Workspace\XXXX Tool\XXXX Workbook " & strYear & ".xlsm", FileFormat:=52
Set newwb = wb ' 直接复用原对象

方案2：用文件名引用工作簿

提取完整路径中的文件名部分，作为Workbooks的索引：

Dim wb As Workbook
Dim newwb As Workbook
Dim fileName As String
Dim fullPath As String

fileName = "XXXX Workbook " & strYear & ".xlsm"
fullPath = "C:\Users\XXXX\Desktop\Workspace\XXXX Tool\" & fileName

wb.SaveAs Filename:=fullPath, FileFormat:=52
Set newwb = Workbooks(fileName) ' 仅用文件名作为索引

方案3：显式打开文件（适合需确保文件处于打开状态的场景）

如果需要确认文件被正确打开，可先检查是否已在Workbooks集合中，未打开则调用Open：

Dim wb As Workbook
Dim newwb As Workbook
Dim fullPath As String

fullPath = "C:\Users\XXXX\Desktop\Workspace\XXXX Tool\XXXX Workbook " & strYear & ".xlsm"
wb.SaveAs Filename:=fullPath, FileFormat:=52

' 检查工作簿是否已打开
On Error Resume Next
Set newwb = Workbooks(Dir(fullPath))
On Error GoTo 0

' 未打开则手动打开
If newwb Is Nothing Then
    Set newwb = Workbooks.Open(fullPath)
End If

额外注意事项 #

• 代码中Declare workbooks是无效语句，直接删除即可，VBA无需声明Workbooks集合。
• 确保strYear变量已正确赋值，避免生成的文件名或路径有误。

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