Get list of available macros?

Martin Dubaj

unread,

Mar 21, 2002, 6:07:57 AM3/21/02

to

Hi everyone,

I'd love any help I can get with this:

How do I obtain a list of all the macros available within a particular
woorkbook at runtime through VBA?

I'm writing a program to perform data analysis on several files and I
would like to let the user select which .xls file contains the macro
he/she wants to apply to the data files ( that part is easy - done ) AND
also let the user select ONE macro from a list of macros available
within that .xls file. (Hmmm, no idea how to do that yet.)

Thanks for any help.

Martin Dubaj.

Jim Rech

unread,

Mar 21, 2002, 7:03:34 AM3/21/02

to

Perhaps?:

Application.Dialogs(xlDialogRun).Show

--
Jim Rech
Excel MVP

Tim Childs

unread,

Mar 22, 2002, 3:56:54 AM3/22/02

to

Jim

PMFJI (again?)

How do you grab the data displayed, please?

Thanks

Tim

Dave Peterson

unread,

Mar 22, 2002, 6:31:17 PM3/22/02

to

Chip Pearson has some code that you could modify that uses msgbox to display the
procedures in a module at:

http://www.cpearson.com/excel/vbe.htm

look for: Listing All Procedures In A Module

=======
I modified slightly to just take the procedures from the standard modules. You
could do more to get the sheet, workbook, class stuff.

Option Explicit

Sub ListProcedures()

Dim VBComp As VBComponent
Dim StartLine As Long
Dim Msg As String
Dim ProcName As String

For Each VBComp In ThisWorkbook.VBProject.VBComponents
With VBComp.CodeModule
If VBComp.Type = vbext_ct_StdModule Then
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
Msg = Msg & .ProcOfLine(StartLine, vbext_pk_Proc) & vbLf
StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
MsgBox VBComp.Name & vbLf & Msg
End If
End With
Next VBComp

End Sub

--

Dave Peterson
ec3...@msn.com

Tim Childs

unread,

Mar 25, 2002, 8:25:52 AM3/25/02

to

Dave

thanks for this - the code (as is) runs fine for me but I am struggling as I
don't know the whereabouts of the object for the shortcut letter and so
cannot progress it further

thanks

Tim

Dave Peterson

unread,

Mar 25, 2002, 5:49:10 PM3/25/02

to

Unless you assigned a shortcutkey to the macro (tools|macros|macros|options
stuff), then there won't be a shortcutkey for this macro.

What am I missing?

--

Dave Peterson
ec3...@msn.com

Tim Childs

unread,

Mar 26, 2002, 3:16:37 AM3/26/02

to

Dave

For each proc. returned, I want to know what the shortcut key (if any) is.
(Not the shortcut key for the list proc itself)

Sorry for any confusion

Tim

Dave Peterson

unread,

Mar 26, 2002, 7:06:20 PM3/26/02

to

That's what I was afraid of.

I don't know a way. I searched google and there was a least one post that said
that it's not possible.

Another post suggested that if the shortcutkey were added in the code (not the
UserInterface), you could read the code and look for "shortcutkey" and pick the
pieces out. Ick!

--

Dave Peterson
ec3...@msn.com

Tim Childs

unread,

Mar 27, 2002, 3:44:59 AM3/27/02

to

Dave

Thanks for this confirmation - surprisingly useful to have a negative
confirmed. Saves a load of frustration

I am going to try using Send keys (when I have a moment spare!)

Tim

Dave Peterson

unread,

Mar 27, 2002, 6:10:03 PM3/27/02

to

Well, post back when you have success (or give up!).

--

Dave Peterson
ec3...@msn.com

Tim Childs

unread,

Mar 28, 2002, 3:09:09 AM3/28/02

to

Dave

gauntlet laid down....

will have to succeed!

Tim

Tim Childs

unread,

Mar 29, 2002, 2:33:02 AM3/29/02

to

Dave

it won't get more kludgy than this (see code below) but it appears to work
on XL2002.

on XL2000, I received the beeping when column B was being filled with blanks
(any advice welcome)

you may also be able to see that I was hoping to use arrays but I could not
get Chip Pearson's GetOffClipboard to work in this context (any advice
welcome here, too)

I think I should be using the xldialogsshow construct, looking at someone
else's program which eliminates unused number formats, but was not sure
about how to do it - so have gone for "maximum kludge"!

HOWEVER, it worked for me!

Happy Easter

Tim

Sub foo()
Dim testing
Dim bFound As Boolean
Dim iCounter As Integer
Dim iTotalMacroNo As Integer
Dim MacroNames(12, 2)

If IsVBEActive Then Exit Sub

Workbooks.Add
Range("A9").Value = "Workbook"
Range("b9").Value = "Macro Shortcut Letter"
Range("A9:B9").Font.Bold = True

Range("A9").Select

'testing = GetOffClipboard
'MsgBox testing
DoEvents
Application.SendKeys ("%TMM{TAB 2}{UP 12}{TAB}{ESC 2}") 'set for All Open
Workboooks

iCounter = 1
Do While bFound = False
DoEvents
Application.SendKeys ("{DOWN}")
Application.SendKeys ("%TMM") 'start
Application.SendKeys ("{TAB}{DOWN " & iCounter - 1 & "}{TAB 8}")
'
Application.SendKeys ("{F2}{END}")
Application.SendKeys ("+{HOME}")
Application.SendKeys ("^c")
Application.SendKeys ("{ESC}") 'quit
Application.SendKeys ("^v")

'Range("A20").Value = GetOffClipboard

'Range("A9").Offset(iCounter, 0).Select
'Application.SendKeys ("^v{DOWN}")
'Range("A9")(2, 1) = Range("A1").Value '"test" '.Offset(iCounter, 1)
'Range("A9")(1 + iCounter, 1) = Range("A1") '.Select
'Application.SendKeys ("^v~")

'Cells(9 + iCounter, 2) = Range("a1")

'If iCounter = 1 Then bFound = True '200

'otherwise next statement did not seem to work properly - spreadsheeet
'did not update fully until next iteration
DoEvents
Application.SendKeys ("{DOWN}{UP}")

Debug.Print "iCounter = " & iCounter & " " &
Application.WorksheetFunction.CountIf(Range("A:A"), ActiveCell.Value)

If Application.WorksheetFunction.CountIf(Range("A:A"), ActiveCell.Value) > 1
Then
bFound = True
ActiveCell.ClearContents
iTotalMacroNo = iCounter
End If

iCounter = iCounter + 1
Loop

Range("B9").Select

For iCounter = 1 To iTotalMacroNo
Application.CutCopyMode = False
ActiveCell.Copy
DoEvents
Application.SendKeys ("{DOWN}")
Application.SendKeys ("%TMM") 'start
Application.SendKeys ("{TAB}{DOWN " & iCounter - 1 & "}%o")
Application.SendKeys ("{F2}{END}")
Application.SendKeys ("+{HOME}")
Application.SendKeys ("^c")
Application.SendKeys ("{ESC 2}") 'quit
Application.SendKeys ("^v")

Next iCounter
Range("A:B").Columns.AutoFit
Range("A8").Select

ActiveWorkbook.Saved = True 'so it can be closed easily

End Sub

Dave Peterson

unread,

Mar 29, 2002, 9:41:48 AM3/29/02

to

Hey, it works! (a lot of blink and flashing, but it worked. I use xl2002, so I
don't have any help for xl2k.)

(I guess the answer to this question becomes, it can be done, just not by me!!!)

Just to add, Tim used a function that Jan Karel Pieterse gave him in a different
thread.

Declare Function GetForegroundWindow Lib "user32.dll" () As Long

Function IsVBEActive() As Boolean
Dim hWndP1 As Long
Dim hWndP2 As Long
hWndP1 = Application.VBE.MainWindow.hwnd
'Find the active window
hWndP2 = GetForegroundWindow
IsVBEActive = (hWndP1 = hWndP2)
End Function

--

Dave Peterson
ec3...@msn.com

Tim Childs

unread,

Mar 29, 2002, 2:27:20 PM3/29/02

to

Dave

thanks for clarifying that point about the function from Jan - I'd kept that
in a different module..

Tim