更改工作表代码名称时出现“运行时错误9：下标超出范围”的问题

Question

我有一段代码，当我按下一个按钮时，会向工作簿中添加一个新的工作表，并更改工作表的代码名称，以便在日后的代码中更容易引用它。

Dim wbk As Workbook
Dim wks As Worksheet

Set wbk = ThisWorkbook

wbk.Sheets.Add.Name = "Admin - Save Log"
Set wks = wbk.Worksheets("Admin - Save Log")
wks.Parent.VBProject.VBComponents(wks.CodeName).Name = "wksAdminSaveLog"

这确实可以工作 - 但是 - 只有当我打开“开发人员”窗口或以前曾经打开过它时才能运行。

如果我在第一次打开Excel文档时单击按钮（没有打开“开发人员”窗口），它会添加该工作表，但是会出现以下错误，并且不会更改工作表的代码名称：

运行时错误“9”：下标超出范围

只有当我按下调试并在再次打开“开发人员”窗口后运行代码时，它才会添加代码名称。

是否有任何方法可以解决这个问题，使用户不必打开开发人员窗口即可正确运行？

- Fu113R

---

你应该先执行 Set wks = wbk.Sheets.Add，然后再设置它的 .Name。这样你就不需要两次拼写字符串字面量了。 - Mathieu Guindon

你可以尝试一两次 DoEvents 调用。不过，如果可能的话，最好使用 Application.Ontime 调用来运行更改代码名称（以及发生的任何其他事情）的代码。 - Rory

你正在添加并重命名VBProject中当前正在执行的VBComponents。你很幸运整个IDE和主机应用程序尚未完全崩溃 - 我怀疑必须已经加载了VBE，以便在幕后涉及P-Code编译器或VBA解释器的某些事情有机会获取新组件；如果VBE还没有加载，则您正在执行编译的代码，其中wks.CodeName不存在作为组件。这是棘手的问题。有趣的问题。 - Mathieu Guindon

尽管我仍然想知道这个特定问题的答案——你不能在工作簿中已经有正确代码名称的表格，但在不需要时将其可见性设置为“xlVeryHidden”吗？ - SierraOscar

我的怀疑（仅供参考）是VBComponents集合在运行时开始初始化，但打开IDE会触发刷新（可能在单独的线程中触发，但仍然访问该集合的同一实例），因为此时必须显示所有可用组件。在打开IDE之前，该特定索引不存在，因为它在运行时开始时不存在。不过我不知道你如何测试 - * 瞪着@Mat'sMug * - SierraOscar

Answer 1

@Comintern已经为您提供了一种可行的解决方案，但是这段代码不会污染您的即时窗口，并使用隐藏的_CodeName属性来更改工作表名称，而不是访问vbComponents集合。

它还使用早期绑定的Worksheet分配给wks，然后使用With块，因为它正在访问wks的多个成员。

有趣的是，VBProject成员使用的位置很重要。

Dim wbk As Workbook
Dim wks As Worksheet

Set wbk = ThisWorkbook
Set wks = wbk.Worksheets.Add

'Oddly, this statement MUST appear AFTER the Worksheets.add
Debug.Assert wbk.VBProject.Name <> vbNullString 'Don't pollute the Immediate window

With wks
  .Name = "Admin - Save Log"
  .[_CodeName] = "wksAdminSaveLog"
End With

- ThunderFrame

---

我一点也不觉得这很奇怪：在项目中所有VBComponents存在之后，你才“创建”VBE。 - Mathieu Guindon

@Mat'sMug - 实际上这根本没有取消引用VBE。我想知道我是否一直在寻求最好的解决方案，只需取消引用wbk.Project即可。@ThunderFrame - 在取消引用项目后，您是否必须使用隐藏的_CodeName？顺便说一句，.Assert做得好。 - Comintern

我没有尝试过原始的CodeName，因为我知道Rubberduck的功能。在意识到vbProject的位置很重要之前，我以为在with块中使用[_CodeName]时出现了一个bug。 - ThunderFrame

@ThunderFrame 当我尝试使用这个方法时，它在Debug.Assert那一段出现了以下错误...运行时错误 '1004: 对Visual Basic项目进行编程访问没有得到信任...有什么办法可以解决这个问题吗？ - Fu113R

我不得不前往文件>选项>信任中心>信任中心设置>宏，然后勾选底部的框"信任对 VBA 项目对象模型的访问" - 一旦我完成这个步骤，您建议的代码就完美运行了！非常感谢您! :) - Fu113R

Answer 2

如果您需要打开VBE，可以“欺骗”调试环境来为您完成此操作。这似乎是通过强制 VBE.MainWindow 的存在来更新索引所需的任何项目。

Dim wbk As Workbook
Dim wks As Worksheet

Set wbk = ThisWorkbook

Set wks = wbk.Sheets.Add
wks.Name = "Admin - Save Log"
Debug.Print wbk.VBProject.VBE.MainWindow.Caption   '<--Force the VBE to exist.
wbk.VBProject.VBComponents(wks.CodeName).Name = "wksAdminSaveLog"

编辑：

看起来只需获取对VBE.MainWindow的引用即可（请参阅注释）。 这也可以：

Dim editor As Object
Set editor = wbk.VBProject.VBE.MainWindow   '<--Force the VBE to exist.

- Comintern

---

仅仅引用 wbk.VBProject.VBE.MainWindow.Caption 是否足够，还是必须显式地使用 Debug 对象？（我知道 Debug.Print 对于“一次性”代码更好 - 只是好奇） - SierraOscar

@MacroMan - 不是完全确定。我只测试了一个Debug.Print，但什么也没发生。打印Caption是我想到的第一件能够将属性请求传递到MainWindow的事情。设置对象引用似乎也可以工作。我会更新答案。 - Comintern

Answer 3

解决此类VBComponent相关代码问题的一般方法是使用此处给出的VbeInit过程，该过程具有灵活性，可被多次调用，用于每个新打开的工作簿，这些工作簿可能尚未进行"VBComponent初始化"。

- Andreas Covidiot

Answer 4

强制刷新VBComponents集合的另一种简单方法：

PropertyGetDiscard ThisWorkbook.VBProject.VBComponents.Count

 

Private Sub PropertyGetDiscard(AnyPropertyGet): End Sub

PropertyGetDiscard 过程用于避免污染立即窗口或使用多余的变量。

- hymced