info
Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Excel 2003 - VBA to get a list of Module names in the project
2,517 views
Skip to first unread message
stainless
Dec 19, 2012, 4:46:47 AM12/19/12
to
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, 1 to 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.
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, 1 to 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
Dec 19, 2012, 7:47:12 AM12/19/12
to
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
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
Dec 19, 2012, 10:02:30 AM12/19/12
to
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
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
Dec 19, 2012, 10:54:01 AM12/19/12
to
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 _
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)
stainless
Dec 20, 2012, 3:05:41 AM12/20/12
to
Thanks Ben
That is exactly what I need
Cheers
That is exactly what I need
Cheers
0 new messages