Excel VBA关闭VB窗口后新增工作表时报Subscript out of range错误

你遇到的下标越界错误，根源在往新工作表写入代码的两行逻辑：

ThisWorkbook.VBProject.VBComponents(ws_new_init.CodeName).CodeModule.AddFromString code_line
ThisWorkbook.VBProject.VBComponents(ws_new_fac.CodeName).CodeModule.AddFromString code_line

当VBA编辑器（VBE）窗口关闭时，Excel新创建工作表后，对应的工作表代码模块不会即时同步到VBProject.VBComponents集合中，此时用CodeName访问集合找不到对应元素，就会抛出下标越界错误。而VBE窗口打开时，Excel会实时同步所有工作表的代码模块信息，所以运行正常。

完全避免直接操作VBProject组件，改为直接复制模板工作表：你的第4、5张是站点评估模板表，直接复制模板表就能连内容、格式、工作表代码一起复制，不需要手动写代码到新表，从根源上避开这个问题。

核心修改点 #

1. 移除操作VBProject写入代码的相关逻辑
2. 把原来「新建空白表+复制单元格内容+写入代码」的逻辑，替换为复制模板表的逻辑
3. 建议把按索引取模板表的逻辑，改为按表名取，避免用户调整工作表顺序后逻辑失效

' 先定义模板表，建议用表名而非索引，更稳定
Set ws_init_template = ThisWorkbook.Sheets("Initial Assessment - Facility1")
Set ws_onsite_template = ThisWorkbook.Sheets("On-Site Assessment - Facility1")

' 生成新站点表的逻辑替换为：
For counter = LBound(facilities) To UBound(facilities)
    facility_name = facilities(counter)
    ' 复制初评模板
    ws_init_template.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set ws_new_init = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    ws_new_init.Name = CreateInitialTabName(facility_name)
    ' 复制现场评估模板
    ws_onsite_template.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set ws_new_fac = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    ws_new_fac.Name = CreateOnSiteTabName(facility_name)
Next counter

如果一定要保留原逻辑写入代码，可以在写入代码的行之前加DoEvents，让Excel完成VBComponent的同步，再执行写入操作，也可以解决问题。

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