如何使用VBA识别ThisWorkbook模块 [英] How to identify ThisWorkbook module using VBA

问题描述

使用Excel 2010。

Using Excel 2010.

我需要将代码添加到已重命名ThisWorkbook模块的远程Excel文件中，就是说DashboardWorkbook。我不知道这个新的名字，除了我需要以编程方式识别这个模块，以便为Sub Workbook_Open（）添加更多的代码。

I need to add code to a remote Excel file where ThisWorkbook module has been renamed, let's say to "DashboardWorkbook". I don't know however the new name, can be anything but I need to identify this module programmatically in order to add more code to Sub Workbook_Open().

我打开这个远程文件，然后我经历了所有的组件：

I am opening this remote file, then I go through all it's components:

Private Sub AddProcedureToWorkbook(wb As Workbook)
  Dim VBProj As VBIDE.VBProject
  Dim oComp As VBIDE.VBComponent
  Dim oCodeMod As VBIDE.CodeModule

  Set VBProj = wb.VBProject 
  For Each oComp In VBProj.VBComponents
    If *[check here if oComp was formerly ThisWorkbook but now renamed]* Then
      Set oCodeMod = oComp.CodeModule
        'add new code here
      ...etc, etc
    End If
  Next

End Sub

在Excel界面中，ThisWorkbook有一个不同的图标，所以它似乎是一个不同的模块类型，但我无法弄清楚为了识别它要读取什么特定的属性？

In Excel interface, ThisWorkbook has a different icon so it seems to be a different module type but I could not figure out what specific property to read in order to identify it?

许多事情更多，有时Sub Workbook_Open（）不存在，因此我需要添加到正确的地方...

To complicate things even more, sometimes Sub Workbook_Open() doesn't exist, therefore I need to add it at the right place...

谢谢，

MR

推荐答案

表格和图书可以直接从代码中通过他们的 CodeName （不同于显示名称也就是名称）。

这也是他们的VBComponent名称。 / p>

Sheets and books can be accessed directly from code by their CodeName (different from display name aka just Name).
This is also their VBComponent name.

Private Sub AddProcedureToWorkbook(wb As Workbook)
  Dim VBProj As VBIDE.VBProject
  Dim oComp As VBIDE.VBComponent
  Dim oCodeMod As VBIDE.CodeModule

  Set VBProj = wb.VBProject
  Set oComp = VBProj.VBComponents(wb.CodeName)
  Set oCodeMod = oComp.CodeModule

  oCodeMod.AddFromString "sub Hi()" & vbNewLine & "msgbox ""Hi.""" & vbNewLine & "end sub"
End Sub

这篇关于如何使用VBA识别ThisWorkbook模块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！