VBA Code to get list of functions in a module.
 

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("A ddInCode").Functions.List

obviously thats garbage, but you get the idea

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

--
CalDud
-----------------------------------------------------------------------
CalDude's Profile: http://www.excelforum.com/member.php...fo&userid=3662
View this thread: http://www.excelforum.com/showthread.php?threadid=56375

VBA Code to get list of functions in a module.
 

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").Code Module
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


"CalDude" wrote:


I have an excel xla add-in called EconLibrary.xla, in it there is a
single module which stores all my code, it's called 'AddInCode'.
AddInCode contains several functions, most of which are functions to
use within cells in excel. I need some VBA code that will give me a
list (an array would be nice) of all the functions currently contained
in the module. A list like the one in the drop down box is what I'm
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("A ddInCode").Functions.List

obviously thats garbage, but you get the idea!


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

--
CalDude
------------------------------------------------------------------------
CalDude's Profile: http://www.excelforum.com/member.php...o&userid=36628
View this thread: http://www.excelforum.com/showthread...hreadid=563755