如何正确重命名ThisWorksheet的CodeModule [英] How to properly rename CodeModule of ThisWorksheet

问题描述

我正在尝试使用VBA扩展性重命名Excel工作表的 ThisWorkbook 代码模块。

I使用

  ThisWorkbook.VBProject.VBComponents（ThisWorkbook）。<= p> 
 
 
  

我可以看到VB编辑器中的更改，还可以访问工作簿的属性，例如调试？ wb.Name 。

但是：如果我保存并关闭该文件，然后重新打开它，我会得到奇怪的行为。如果代码模块在重命名之前为空，它将恢复为旧的空的 ThisWorkbook 名称。

如果在保存之前不是空的或者是填充的，我现在有两个，一个空的 ThisWorkbook 模块（如果有的话将会触发事件） - 填充的$ code> wb 模块 - 不触发工作簿事件：

/ p>

详细答案

当我添加对 Microsoft Visual Basic应用程序可扩展性5.3 并运行您的行

  ThisWorkbook.VBProject.VBComponents（ThisWorkbook ）.Name =wb
  

ThisWorkbook 名称属性实际上没有被修改

保存并重新打开文件会导致 ThisWorkbook object

这几乎意味着现在我有一个工作簿中的两个工作簿对象，它们都命名为 ThisWorkbook

---

解决方法是重命名 ThisWorkbook 到 wb 使用属性窗口或

ThisWorkbook。[_ CodeName] =newName因为 ThisWorkbook.CodeName 是只读的。

I'm trying to rename the ThisWorkbook code module of an Excel worksheet using VBA Extensibility.

I accomplish this with the line

ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Name = "wb"

I can see the change in the VB editor and also access the workbook's properties, e.g. Debug.? wb.Name.

However: If I save and close the file and then reopen it, I get strange behavior. If the code module was empty before renaming it, it reverts back to the old empty ThisWorkbook name.

If it was not empty or was populate before the saving, I now have both, an empty ThisWorkbook module (that would fire events if there were any) - and the filled wb module - which does not fire workbook events:

Has anyone seen this behavior - and knows a fix/workaround?

解决方案

Quick answer: ThisWorkbook.[_CodeName] = "newName"

Detailed answer

When I add references to the Microsoft Visual Basic For Applications Extensibility 5.3 and run your line

ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Name = "wb"

The ThisWorkbook Name property isn't actually being modified

Saving and re-opening the file causes a duplication of the ThisWorkbook object

Which pretty much means now I have two Workbook objects within one workbook and both are named ThisWorkbook

---

The workaround is to rename the ThisWorkbook to wb using the Properties window or

ThisWorkbook.[_CodeName] = "newName" because ThisWorkbook.CodeName is read-only.

这篇关于如何正确重命名ThisWorksheet的CodeModule的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！