Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving List of Macros in Workbook
Is there a way to retreive a list of macros stored in the VBA modules
of an excel workbook? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving List of Macros in Workbook
Hi,
This and more... http://exceltips.vitalnews.com/E089_...ng_Macros.html Cheers "NumberCruncher13" wrote: Is there a way to retreive a list of macros stored in the VBA modules of an excel workbook? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving List of Macros in Workbook
Get hold of MZ Tools for VBA, it has a Statistics option that lists of the
procedures and gives you ... statistics on them. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NumberCruncher13" wrote in message ... Is there a way to retreive a list of macros stored in the VBA modules of an excel workbook? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving List of Macros in Workbook
On Dec 4, 11:20 am, Ashish Dutt Sharma
wrote: Hi, This and more...http://exceltips.vitalnews.com/E089_...ng_Macros.html Cheers "NumberCruncher13" wrote: Is there a way to retreive a list of macros stored in the VBA modules of an excel workbook?- Hide quoted text - - Show quoted text - Ashish, Thanks for the link. I copied the code from here http://exceltips.vitalnews.com/Pages...of_Macros.html But when I run the macro, it looks like my computer stalls, or the macro is taking too long to run. Have you used this before? Please let me know if it works for you. In the directions it states to make sure that the "Microsoft Visual Basic for Applications Extensibility" is selected. Using my version of MS Excel 2003, I checked "Microsoft Visual Basic for Applications Extensibility 5.3"... not sure if that has anything to do with the problem. Thanks for you help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving List of Macros in Workbook
This work fine for me, no need to worry about references
Sub ListMacros() Const vbext_pk_Proc = 0 Dim VBComp As Object Dim VBCodeMod As Object Dim oListsheet As Object Dim StartLine As Long Dim ProcName As String Dim iCount As Integer Application.ScreenUpdating = False On Error Resume Next Set oListsheet = ActiveWorkbook.Worksheets.Add iCount = 1 oListsheet.Range("A1").Value = "Macro" For Each VBComp In ThisWorkbook.VBProject.VBComponents Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).C odeModule With VBCodeMod StartLine = .CountOfDeclarationLines + 1 Do Until StartLine = .CountOfLines oListsheet.[a1].Offset(iCount, 0).Value = _ .ProcOfLine(StartLine, vbext_pk_Proc) iCount = iCount + 1 StartLine = StartLine + _ .ProcCountLines(.ProcOfLine(StartLine, _ vbext_pk_Proc), vbext_pk_Proc) Loop End With Set VBCodeMod = Nothing Next VBComp Application.ScreenUpdating = True End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NumberCruncher13" wrote in message ... On Dec 4, 11:20 am, Ashish Dutt Sharma wrote: Hi, This and more...http://exceltips.vitalnews.com/E089_...ng_Macros.html Cheers "NumberCruncher13" wrote: Is there a way to retreive a list of macros stored in the VBA modules of an excel workbook?- Hide quoted text - - Show quoted text - Ashish, Thanks for the link. I copied the code from here http://exceltips.vitalnews.com/Pages...of_Macros.html But when I run the macro, it looks like my computer stalls, or the macro is taking too long to run. Have you used this before? Please let me know if it works for you. In the directions it states to make sure that the "Microsoft Visual Basic for Applications Extensibility" is selected. Using my version of MS Excel 2003, I checked "Microsoft Visual Basic for Applications Extensibility 5.3"... not sure if that has anything to do with the problem. Thanks for you help! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving List of Macros in Workbook
On Dec 5, 4:13 am, "Bob Phillips" wrote:
This work fine for me, no need to worry about references Sub ListMacros() Const vbext_pk_Proc = 0 Dim VBComp As Object Dim VBCodeMod As Object Dim oListsheet As Object Dim StartLine As Long Dim ProcName As String Dim iCount As Integer Application.ScreenUpdating = False On Error Resume Next Set oListsheet = ActiveWorkbook.Worksheets.Add iCount = 1 oListsheet.Range("A1").Value = "Macro" For Each VBComp In ThisWorkbook.VBProject.VBComponents Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).C odeModule With VBCodeMod StartLine = .CountOfDeclarationLines + 1 Do Until StartLine = .CountOfLines oListsheet.[a1].Offset(iCount, 0).Value = _ .ProcOfLine(StartLine, vbext_pk_Proc) iCount = iCount + 1 StartLine = StartLine + _ .ProcCountLines(.ProcOfLine(StartLine, _ vbext_pk_Proc), vbext_pk_Proc) Loop End With Set VBCodeMod = Nothing Next VBComp Application.ScreenUpdating = True End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NumberCruncher13" wrote in message ... On Dec 4, 11:20 am, Ashish Dutt Sharma wrote: Hi, This and more...http://exceltips.vitalnews.com/E089_...ng_Macros.html Cheers "NumberCruncher13" wrote: Is there a way to retreive a list of macros stored in the VBA modules of an excel workbook?- Hide quoted text - - Show quoted text - Ashish, Thanks for the link. I copied the code from here http://exceltips.vitalnews.com/Pages..._List_of_Macro... But when I run the macro, it looks like my computer stalls, or the macro is taking too long to run. Have you used this before? Please let me know if it works for you. In the directions it states to make sure that the "Microsoft Visual Basic for Applications Extensibility" is selected. Using my version of MS Excel 2003, I checked "Microsoft Visual Basic for Applications Extensibility 5.3"... not sure if that has anything to do with the problem. Thanks for you help! Bob - I copied your code into the vba editor and noticed there's a syntax error for the following line: "Set VBCodeMod =" Did that work for you? Thanks again for you help. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving List of Macros in Workbook
Yes, that is not a coding error, but is caused by the NG wrapping the text.
One of the lines of code was split into two, causing the problem. Hopefully this is better Sub ListMacros() Const vbext_pk_Proc = 0 Dim VBComp As Object Dim VBCodeMod As Object Dim oListsheet As Object Dim StartLine As Long Dim ProcName As String Dim iCount As Integer Application.ScreenUpdating = False On Error Resume Next Set oListsheet = ActiveWorkbook.Worksheets.Add iCount = 1 oListsheet.Range("A1").Value = "Macro" For Each VBComp In ThisWorkbook.VBProject.VBComponents Set VBCodeMod = _ ThisWorkbook.VBProject.VBComponents(VBComp.Name).C odeModule With VBCodeMod StartLine = .CountOfDeclarationLines + 1 Do Until StartLine = .CountOfLines oListsheet.[a1].Offset(iCount, 0).Value = _ .ProcOfLine(StartLine, vbext_pk_Proc) iCount = iCount + 1 StartLine = StartLine + _ .ProcCountLines(.ProcOfLine(StartLine, _ vbext_pk_Proc), vbext_pk_Proc) Loop End With Set VBCodeMod = Nothing Next VBComp Application.ScreenUpdating = True End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NumberCruncher13" wrote in message ... On Dec 5, 4:13 am, "Bob Phillips" wrote: This work fine for me, no need to worry about references Sub ListMacros() Const vbext_pk_Proc = 0 Dim VBComp As Object Dim VBCodeMod As Object Dim oListsheet As Object Dim StartLine As Long Dim ProcName As String Dim iCount As Integer Application.ScreenUpdating = False On Error Resume Next Set oListsheet = ActiveWorkbook.Worksheets.Add iCount = 1 oListsheet.Range("A1").Value = "Macro" For Each VBComp In ThisWorkbook.VBProject.VBComponents Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).C odeModule With VBCodeMod StartLine = .CountOfDeclarationLines + 1 Do Until StartLine = .CountOfLines oListsheet.[a1].Offset(iCount, 0).Value = _ .ProcOfLine(StartLine, vbext_pk_Proc) iCount = iCount + 1 StartLine = StartLine + _ .ProcCountLines(.ProcOfLine(StartLine, _ vbext_pk_Proc), vbext_pk_Proc) Loop End With Set VBCodeMod = Nothing Next VBComp Application.ScreenUpdating = True End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "NumberCruncher13" wrote in message ... On Dec 4, 11:20 am, Ashish Dutt Sharma wrote: Hi, This and more...http://exceltips.vitalnews.com/E089_...ng_Macros.html Cheers "NumberCruncher13" wrote: Is there a way to retreive a list of macros stored in the VBA modules of an excel workbook?- Hide quoted text - - Show quoted text - Ashish, Thanks for the link. I copied the code from here http://exceltips.vitalnews.com/Pages..._List_of_Macro... But when I run the macro, it looks like my computer stalls, or the macro is taking too long to run. Have you used this before? Please let me know if it works for you. In the directions it states to make sure that the "Microsoft Visual Basic for Applications Extensibility" is selected. Using my version of MS Excel 2003, I checked "Microsoft Visual Basic for Applications Extensibility 5.3"... not sure if that has anything to do with the problem. Thanks for you help! Bob - I copied your code into the vba editor and noticed there's a syntax error for the following line: "Set VBCodeMod =" Did that work for you? Thanks again for you help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retrieving last actual value in a list | Excel Discussion (Misc queries) | |||
Calling up the macro dialog to list stored macros in a workbook-VB | Excel Programming | |||
Saving and Retrieving Macros | Excel Discussion (Misc queries) | |||
callin the macro dialog to list all stored macros in a workbook VB | Excel Programming | |||
Retrieving data from a database list | Excel Discussion (Misc queries) |