更改工作表代号运行时错误9:下标超出范围 [英] Changing sheet codename Run-Time Error 9: Subscript out of range

问题描述

我有一个代码，当我按下一个按钮时，它将在工作簿中添加一个新的工作表，并更改工作表的代号，以使以后在我的代码中更易于引用.

I've code which, when I press a button, will add a new sheet to a workbook and change the codename of the sheet to make it easier to refer to later in my code.

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"

这确实有效-但是-仅当我打开开发人员"窗口或之前已打开它时.

This does work - HOWEVER - only when I have the "Developer" window open or have previously had it open.

如果我在第一次打开Excel文档时(没有打开开发者"窗口)单击按钮，它将添加工作表，但是会出现以下错误，并且不会更改工作表的代号:

If I click the button when I first open the Excel document (having not opened the "Developer" window) it adds the sheet, however it comes up with the following error and does not change the codename of the sheet:

运行时错误'9':下标超出范围

Run-time error '9': Subscript out of range

只有当我再次按下Debug，然后在再次打开"Developer"窗口后运行代码时，它才会添加代号.

It is only when I press Debug and then run the code after the "Developer" window is open again that it adds the codename.

有没有解决的办法，以便用户不必打开开发人员窗口即可正常运行?

Is there any way around this so that the user does not have to have the developer window open in order for it to run correctly?

推荐答案

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

@Comintern already got you a working solution, but this code doesn't pollute your Immediate Window, and uses the hidden _CodeName property to change the sheet name instead of accessing the vbComponents collection.

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

It also uses an early-bound Worksheet assignment to wks, and then a With block because it is accessing more than 1 member of wks.

有趣的是，VBProject成员用法的 placement 很重要.

Interestingly, the placement of the VBProject member usage is important.

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

这篇关于更改工作表代号运行时错误9:下标超出范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！