accessing and iterating through modules in excel 97 1

[tyhand]

hi all,

how can I access the names of all the modules I have in
excel 97.

I have a handful of modules in my VBAProject(Personal.xls) file and am trying to write a code that iterates and returns the name of each module.

I'm using
ThisWorkbook.VBProject.VBComponents
but am stuck as there no 'modules' object collection.

Any help greatly appreciated. Thanks. Peace!

 

[acron]

The collection you rae interested in is the VBComponents collection, which is comprised of General Modules, Class Modules, UserForms and the document components such as ThisWorkbook, all the worksheets and chartsheets.

The following code lists all the th ecomponents of the active VBProject to the mmediate window.

Dim VBC, VBComp
Set VBC = Application.VBE.ActiveVBProject.VBComponents
For Each VBComp In VBC
    Debug.Print VBComp.Name, VBComp.Type
Next VBComp

When new VBProjects are created they are assigned a default name of VBAProject, which if left unchanged (which is probably the norm) makes acessing them via code difficult. If you want to work with yur personal .xls without making it the ActiveVBProject, change the project name from VBAProject to say Personal in the properties window. You can then work with it as follows :

Dim VBC, VBComp
Set VBC = Application.VBE.VBProjects("Personal").VBComponents
For Each VBComp In VBC
    Debug.Print VBComp.Name, VBComp.Type
Next VBComp

If you run any of the above code fragments, you will see each VBComponent has it's own type identifier,
General Module = 1, Class Module = 2, UserForm = 3 and document components are type 100. This makes it easy to work with a specific type of component.

A.C.

 

[tyhand]

Thanks!