Excel 2003 - VBA to get a list of Module names in the project

[stainless]

We have Excel VBA templates that produce reports for customers when our code opens these reports. They have to enable macros and on completion, they save the results to their laptops. The issue is that they open these later and are still in the habit of enabling macros for the saved report. As the data has been fully formatted already, the macros fail the second time around (as we would expect).

Given this issue, I am looking at adding code to delete macro modules at the end of the initial report formatting and am ok with this up to a point.

I would, however, like the code to retrieve the list of modules and only delete those that will ensure that no code is attempted that ill fail.

For example:

AutoOpen module - standard for all our templates and just has 2 commands, 1to run a procedure called Main_Process in a module called MainProcess and one to run th Kill_VBCode procedure in the modVBKillCode module. I will simply have code to comment out the Main_Process and Kill_VBCode calls and am ok with this

MainProcess module - want to delete this in the Kill_VBCode

AnotherModule module (could be any name) - want to delete this in the Kill_VBCode

modVBKillCode - leave alone

Thus, in short, I need a piece of code within modVBKillCode that gets a list of modules, runs the "comment out" code for AutoOpen, leaves modVBKillCode alone and deletes all other modules. So all I need is the loop that will give me the module names and then I can call the delete/comment code accordingly. It is just getting this list that is the issue. Code for deleting and commenting out is elsewhere on the web.

 

[Ben McClave]

Hello,

Try this code. It should be easy to adapt it to your needs.

Sub GetModules()
Dim modName As String
Dim wb As Workbook
Dim l As Long

Set wb = ThisWorkbook

For l = 1 To wb.VBProject.VBComponents.Count
With wb.VBProject.VBComponents(l)
modName = modName & vbCr & .Name
End With
Next

MsgBox "Module Names:" & vbCr & modName

Set wb = Nothing

End Sub

 

[stainless]

Hi

thanks for this. However, it does find worksheets and workbooks as well. Is there a means of testing whether the component is a module?

Cheers

 

[Ben McClave]

Yes, you can use the .Type property. For example:

Sub GetModules()
Dim modName As String
Dim wb As Workbook
Dim l As Long

Set wb = ThisWorkbook

For l = 1 To wb.VBProject.VBComponents.Count
With wb.VBProject.VBComponents(l)
If .Type = 1 Then _
modName = modName & vbCr & .Name
End With
Next

MsgBox "Module Names:" & vbCr & modName

Set wb = Nothing

End Sub

 

[stainless]

Thanks Ben

That is exactly what I need

Cheers