VBA Code to get list of functions in a module.

C

CalDude

I have an excel xla add-in called EconLibrary.xla, in it there is
single module which stores all my code, it's called 'AddInCode'.
AddInCode contains several functions, most of which are functions t
use within cells in excel. I need some VBA code that will give me
list (an array would be nice) of all the functions currently containe
in the module. A list like the one in the drop down box is what I'
after. I imagine there is a built in function for getting this array
but I can't find it. Please help! Thank you.

IE:

Dim funclist() As Variant
funclist
Workbooks("EconLibrary.xla").VBProjects.Modules("AddInCode").Functions.List

obviously thats garbage, but you get the idea

+-------------------------------------------------------------------
|Filename: vbaquestion.JPG
|Download: http://www.excelforum.com/attachment.php?postid=5070
+-------------------------------------------------------------------
 
C

crazybass2

To use this code you need to have a reference to "Microsoft Visual Basic For
Applications Extensibility 5.3." This will give you a Messagebox of the
procedures, but you could use the msg variable or mod the code a bit to put
the procedures into an array.


Option Explicit

Sub ListProcedures()

Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim Msg As String
Dim ProcName As String

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
Msg = Msg & .ProcOfLine(StartLine, vbext_pk_Proc) & Chr(13)
StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
MsgBox Msg

End Sub


Credit to Pearson Software Consulting, LLC (www.cpearson.com)

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top