VBA-在工作簿中获取模块 [英] VBA - Getting the modules in workbook

问题描述

我正在尝试创建一个用于创建其他.xlsm工作簿的工作簿，但无法弄清楚如何获取所需的模块，因此可以添加它们.

I'm trying to create a workbook that is used for creating other .xlsm workbooks, but can't figure out how to get the modules I need so I can add them.

下面是我的代码(根据此处给出的答案进行了修改:如何以编程方式添加Excel 2010宏)

My code as it stands is below (modified from the answer given here: How to add excel 2010 macro programmatically)

我需要帮助的位置在ImportModules子目录中，并带有注释'LIST MODULES HERE

The place I need help is in the ImportModules sub, by the comment 'LIST MODULES HERE

如何获取当前工作簿中的一系列模块?

How can I get an array of modules that are in the current workbook?

Private Sub SVAmaker_Click()

    Dim file As String
    file = InputBox("SVA Planner file name", "Name", "Name")

    Application.DefaultSaveFormat = xlOpenXMLWorkbookMacroEnabled
    Workbooks.Add
    ActiveWorkbook.SaveAs filename:=file

    Dim WB As Workbook
    WB = ActiveWorkbook
    Call ImportModules(VBA.CStr(WB))

End Sub

Sub ImportModules(sWorkbookname As String)

    Dim cmpComponents As VBIDE.VBComponents
    Dim wbkTarget As Excel.Workbook

    Set wbkTarget = Workbooks.Open(sWorkbookname)

    If wbkTarget.VBProject.Protection = 1 Then
        Debug.Print wbkTarget.Name & " has a protected project, cannot import module"
    GoTo Cancelline
    End If

    Set cmpComponents = wbkTarget.VBProject.VBComponents

    Dim vModules As Variant
    'LIST MODULES HERE

    Dim i As Integer
    For i = LBound(vModules) To UBound(vModules)
        cmpComponents.Import vModules(i)
    Next i

Cancelline:

    If wbkTarget.FileFormat = xlOpenXMLWorkbook Then
        wbkTarget.SaveAs wbkTarget.Name, xlOpenXMLWorkbookMacroEnabled
        wbkTarget.Close SaveChanges:=False
    Else
        wbkTarget.Close SaveChanges:=True
    End If

    Set wbkTarget = Nothing

End Sub

推荐答案

JChristen要求提供这些模块的列表

JChristen asked for a list of those modules

我将根据gizlmo的建议创建一个收藏集:

I'd create a collection, based on gizlmo's proposal:

    Dim vbcomp As VBComponent
    Dim modules as Collection

    set modules = new Collection
    For Each vbcomp In ThisWorkbook.VBProject.VBComponents

        'if normal or class module
        If ((vbcomp.Type = vbext_ct_StdModule) _
             Or _
            (VBComp.Type = vbext_ct_ClassModule)) Then 

           modules.add VBcomp.name

        End If
    Next vbcomp

以后，您可以像这样使用此收藏集:

Later on you can use this collection like this:

    Dim module     as Variant
    for each module in modules
        ' e.g. importing the module 
        import module
    next module

希望有帮助

这篇关于VBA-在工作簿中获取模块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！