Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
documenting all UDFs in an XLA
Hi, I've inherited a vast add-in and would like to list all UDFs in it. Does
anyone know of a way to do this? If possible, I'd like to make a distinction between visible functions and ones the user doesn't see in the Function Wizard/Paste Function (i.e. ones that are public as opposed to private). It would also be great to then document these UDFs alongside the internal functions they each call - that is, create a kind of tree so I can then try to audit this thing! That may be a tall order... Any help appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
documenting all UDFs in an XLA
This should help you out:
http://www.cpearson.com/excel/vbe.aspx -- -John Please rate when your question is answered to help us and others know what is helpful. "Smallweed" wrote: Hi, I've inherited a vast add-in and would like to list all UDFs in it. Does anyone know of a way to do this? If possible, I'd like to make a distinction between visible functions and ones the user doesn't see in the Function Wizard/Paste Function (i.e. ones that are public as opposed to private). It would also be great to then document these UDFs alongside the internal functions they each call - that is, create a kind of tree so I can then try to audit this thing! That may be a tall order... Any help appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
documenting all UDFs in an XLA
Thanks very much John - a very useful site for VBA Extensibility code.
In case anyone else is doing what I'm doing, I've adapted the "Listing All Procedures In A Module" code so that it documents all modules in a workbook/xla(I've included a minor fix - you'll see a "+ 1" has been remmed out as I found this was rolling forward an unnecessary line each time which finally resulted in some procedures being omitted. I'm not sure the ProcKind is being completely dealt with either - I didn't need to worry as I had no Property Let/Set/Gets I needed to document): Sub ListProceduresForAllModules() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Dim NumLines As Long Dim WS As Worksheet Dim Rng As Range Dim ProcName As String Dim ProcKind As VBIDE.vbext_ProcKind Dim intCtr As Integer intCtr = 1 Set VBProj = Workbooks("my workbook/xla").VBProject Set WS = Workbooks("my reporting workbook").Worksheets("Sheet1") Set Rng = WS.Cells(intCtr, 1) For Each VBComp In VBProj.VBComponents Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CountOfDeclarationLines + 1 ProcName = .ProcOfLine(LineNum, ProcKind) Do Until LineNum = .CountOfLines Rng(1, 1).Value = ProcName Rng(1, 2).Value = ProcKindString(ProcKind) Rng(1, 3).Value = VBComp.Name intCtr = intCtr + 1 Set Rng = WS.Cells(intCtr, 1) LineNum = LineNum + .ProcCountLines(ProcName, ProcKind) '+ 1 ProcName = .ProcOfLine(LineNum, ProcKind) Loop End With Next End Sub Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String Select Case ProcKind Case vbext_pk_Get ProcKindString = "Property Get" Case vbext_pk_Let ProcKindString = "Property Let" Case vbext_pk_Set ProcKindString = "Property Set" Case vbext_pk_Proc ProcKindString = "Sub Or Function" Case Else ProcKindString = "Unknown Type: " & CStr(ProcKind) End Select End Function "John Bundy" wrote: This should help you out: http://www.cpearson.com/excel/vbe.aspx -- -John Please rate when your question is answered to help us and others know what is helpful. "Smallweed" wrote: Hi, I've inherited a vast add-in and would like to list all UDFs in it. Does anyone know of a way to do this? If possible, I'd like to make a distinction between visible functions and ones the user doesn't see in the Function Wizard/Paste Function (i.e. ones that are public as opposed to private). It would also be great to then document these UDFs alongside the internal functions they each call - that is, create a kind of tree so I can then try to audit this thing! That may be a tall order... Any help appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Documenting spreadsheets | Excel Discussion (Misc queries) | |||
Documenting a macro | Excel Programming | |||
Documenting external query for PivotTables | Excel Programming | |||
Chart Documenting Via VBA Questions | Charts and Charting in Excel | |||
documenting a spreadsheet design | Excel Discussion (Misc queries) |