Greetings,
Can somebody out there give me a VBA example of how to get the names of macros in a particular workbook?
Thanks
Greetings,
Can somebody out there give me a VBA example of how to get the names of macros in a particular workbook?
Thanks
Re: Programatically generate a listing of macros
Welcome to Ozgrid Forum
Try this code (see attach document)
Re: Programatically generate a listing of macros
Cool jetted! It works. Thanks a million.
Re: Programatically generate a listing of macros
Well it looks like I jumped the gun.
It worked in the example you sent, but when I tried to use it in my application I got an error because the workbook that I want to scan for macros is protected.
Any other suggestions (other than the obvious, unprotecting the workbook)?
Re: Programatically generate a listing of macros
Dwayno,
You will either have to accept to have the VB-project protected or make it available to retrieve the wanted info by unprotecting it.
Re: Programatically generate a listing of macros
I find that strange.
I mean, the workbook allows me to go into the Tools->Macro->Macros menu where it lists all the macros. It even alows me to run the macros.
But you are saying that there is no programatic way to pull the same information if the workbook is protected?
Re: Programatically generate a listing of macros
Quote
But you are saying that there is no programatic way to pull the same information if the workbook is protected?
Two aspects to pay attention to:
#1 Protection exist for one reason and when applying the protection then the purpose of its core functionality is achieved, i e to protect the content.
When applying the protection for VBA then Your protect the VB-project itself.
#2 Here at OzGrid we don't deal with hacks to get around protection as this is considered to be unethcial.
Do You still find it strange?
Re: Programatically generate a listing of macros
Can't you see the macros by going Tools | Macro | Macros?? There is already a list there.
Re: Programatically generate a listing of macros
From XL-Dennis:
Two aspects to pay attention to:
#1 Protection exist for one reason and when applying the protection then the purpose of its core functionality is achieved, i e to protect the content.
When applying the protection for VBA then Your protect the VB-project itself.
#2 Here at OzGrid we don't deal with hacks to get around protection as this is considered to be unethcial.
Do You still find it strange?
First of all, I am not attempting to hack the protection of the workbook.
I work in a QC department and one of the things we do here is to compare the final report data to the data pulled from our database before we release it to our customer. The data displayed in the report file changes as the viewer makes certain selections from the combo boxes on the sheet.
We create a QC work file and utilize a pivot table to pull the same view of the data that is represented in the final report file. The pivot table page fields typically have a direct correlation to the combo boxes in the report.
As a QC analyst, my job would be to execise as many different combinations of the combo box selections and compare the report data to the corresponding pivot table data. This is tedious, so I have attempted to automate this process.
I created an application that scans information about both the combo boxes in the report file, and the pivot tables in the QC file. It presents the data it finds and provides an interface to map the pivot table page fields to corresponding combo boxes. I write this mapping to another worksheet in the QC workbook and whenever I change the pivot table selections I want to call a macro to change the data in the report.
Basically this macro forces the combo boxes to change to the corresponding values of the pivot table page fields by setting the combo box's linked cell. Thus, the combo box selection value is changed to match the pivot table selection. Unfortunately just forcing the combo box linked cell value to change does not change the data being dispayed as this is controlled by macros in the report that fire off when the combo boxes are clicked on.
I have hard coded the report macro that is used to update the displayed data within my macro, and this works just fine. But for resuability, I was hoping to be able to capture this information at the same time I capture the pivot field/combo box associations. That way after I synchronize the combo box selections with the pivot table selections, the macro can look up what report macro to call in order to update the displayed data in the report.
Like I said, the protection on the file does not stop me from making the combo box changes, nor does it stop me from calling the macros, or even seeing them in the Run Macro dialog box. So yes it does seem strange to me that I cannot obtain this information since it is readily available though other manual means.
So why then is there no way to obtain this information without doing something that violates the protection setting of the file?
Re: Programatically generate a listing of macros
firefytr,
Yes I can see the list from Tools -> Macro -> Macros. But I wanted to be able to be able to put the list of macros on a userform that I have generated. So that the user can select from this list and have this selection saved and used in an Application.Run statement later on.
Re: Programatically generate a listing of macros
Call me stupid, but it still sounds like you are trying to duplicate a native functionality that Excel already offers. If nothing else, you can launch the Macros dialog box from a userform.
You'll never beat the speed and efficiency of this native functionality.
Re: Programatically generate a listing of macros
Quote from firefytrCall me stupid, but it still sounds like you are trying to duplicate a native functionality that Excel already offers. If nothing else, you can launch the Macros dialog box from a userform.
You'll never beat the speed and efficiency of this native functionality.
OK, let's say I have two workbooks. Workbook A contains multiple worksheets. Each worksheet has a pivot table on it.
Workbook B contains multiple worksheets too. Each worksheet has combo boxes on it. These combo boxes are assigned a macro when they change values to update the data displayed on the worksheet.
Now the worksheets in A and B have a direct correspondence, and what's more, the pivot table page fields on a worksheet in A also have a direct correspondence to the combo boxes on the corresponding worksheet in B.
I can create a lookup table that has the associated correlations between the worksheets in A to the worksheets in B, and the pivot table page fields in A to the combo boxes in B, and also the macro in B that updates the worksheet displayed values.
I can also create a macro that will synchronize combo box selections in a particular worksheet in B to the pivot table values in the corresponding sheet in A. After this, I would still need to call the macro from B that corresponds to that same worksheet to update the display.
This way, I can put a button on each worksheet in A that calls the same code. The code knows what worksheet invoked it, and can loop through the pivot table page fields on that sheet, using the lookup table, set the combo box values, and also using the lookup table call the appropriate macro the display.
The need for the macro listing is on the userform which is the user interface for defining the correlations between the sheets in A and B, and the Pivot Fields to the Combo Boxes, and the display update macros. I have a nice interface that makes building that lookup table easy, and this is the last piece of the puzzle that I need to fit in.
Re: Programatically generate a listing of macros
Don't remember where I got this, but it lists all the routines in the activeworkbook. You can change this to any workbook you want ...
Option Explicit
Sub ListOfMacros()
On Error Resume Next '< error = reference already set
'set reference to Microsoft Visual Basic for Applications
'Extensibility 5.3
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 3
'now get the list of macros
Call GetTheList
End Sub
Private Sub GetTheList()
Dim n&, Count&, MyList(200), List$
Dim Component As VBComponent
For Each Component In ActiveWorkbook.VBProject.VBComponents
With Component.CodeModule
Count = .CountOfDeclarationLines + 1
Do Until Count >= .CountOfLines
MyList(n) = .ProcOfLine(Count, _
vbext_pk_Proc)
Count = Count + .ProcCountLines _
(.ProcOfLine(Count, vbext_pk_Proc), _
vbext_pk_Proc)
Debug.Print MyList(n)
List = List & vbCr & MyList(n)
If Count < .CountOfLines Then n = n + 1
Loop
End With
n = n + 1
Next
MsgBox List, , "List of Macros"
End Sub
Display More
Re: Programatically generate a listing of macros
Thanks firefytr. Somebody has already sent that to me. It works, but the problem I am having now, is that the VB project is protected, and that code fails because of this.
I still find it hard to believe that there is no kind of class or module or something that has a propety or method that can be invoked to list the names of macros.
Re: Programatically generate a listing of macros
You can't access a protected project!
Re: Programatically generate a listing of macros
Hi,
For coding in the VBE you need a reference to "Microsoft Visual Basic for Applications Extensibility". It's not an adding just a reference to the library.
In VBE use the menu Tools > References. The entry should already be in the list and just requires ticking.
Re: Programatically generate a listing of macros
Please read all of the code and the comments in it ...
Sub ListOfMacros()
On Error Resume Next '< error = reference already set
'set reference to Microsoft Visual Basic for Applications
'Extensibility 5.3
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 3
'now get the list of macros
Call GetTheList
End Sub
Note this is setting the reference and callin the GetTheList routine. It's doing so programmatically. You can set this manually as Andy explains.
Re: Programatically generate a listing of macros
I did as directed and ticked off the reference to "Microsoft Visual Basic for Applications Extensibility"
I no longer get an error but I also get no results.
When it hits the line of code
it does not enter. I have several sheets and several modules in the excel file yet it seems to me that it thinks there are no "components" since it does not enter the code within the For.
Re: Programatically generate a listing of macros
Can you post an example workbook with all your code in it.
Don’t have an account yet? Register yourself now and be a part of our community!