list of vb projects

[lior03]

hello
i found the following macro at http://www.puremis.net/excel/tips.shtml
i allow the user get a list of vb projects
in a workbook.
my question: how to add a hyperlink to the name of each project in column c so a click on the procedure name will open it.
Option Explicit

'Need the declaration for the following constants or
'check the reference to Microfost Visual Basic for Applications Extensbility x.xx

Const vbext_pp_none As Long = 0
Const vbext_pk_Proc As Long = 0

Dim x As Long
Dim aList()

Sub GetVbProj()
Dim oVBC As Object
Dim Wb As Workbook
x = 2
For Each Wb In Workbooks
For Each oVBC In Workbooks(Wb.Name).VBProject.VBComponents
If Workbooks(Wb.Name).VBProject.Protection = vbext_pp_none Then
Call GetCodeRoutines(Wb.Name, oVBC.Name)
End If
Next
Next
With Sheets.Add
.[A1].Resize(, 3).Value = Array("Workbook", "Module", "Procedure")
.[A2].Resize(UBound(aList, 2), UBound(aList, 1)).Value = _
Application.Transpose(aList)
.Columns("A:C").Columns.AutoFit
End With
End Sub

Private Sub GetCodeRoutines(wbk As String, VBComp As String)
Dim VBCodeMod As Object
Dim StartLine As Long

On Error Resume Next
Set VBCodeMod = Workbooks(wbk).VBProject.VBComponents(VBComp).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
ReDim Preserve aList(1 To 3, 1 To x - 1)
aList(1, x - 1) = wbk
aList(2, x - 1) = VBComp
aList(3, x - 1) = .ProcOfLine(StartLine, vbext_pk_Proc)
x = x + 1
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
If Err Then Exit Sub
Loop
End With
Set VBCodeMod = Nothing
End Sub


thanks

[matthewspatrick]

i found the following macro at http://www.puremis.net/excel/tips.shtml (http://www.puremis.net/excel/tips.shtml)
i allow the user get a list of vb projects in a workbook.


That is not what that code does. What it really does is:


Loop through each member of the Workbooks collection
In each member, loop through VB components
In each component, identify each sub and function
I make the distinction "members of Workbooks collection" and not "all open workbooks" because currently open add-ins are not members of the Workbooks collection, and so those add-in workbooks will be skipped.

Digression over. To change the sub/function name into a hyperlink, change this line in GetCodeRoutines:

aList(3, x - 1) = .ProcOfLine(StartLine, vbext_pk_Proc)

to:


aList(3, x - 1) = "=HYPERLINK(""" & Workbooks(wbk).Path & "\" & Workbooks(wbk).Name & _
""",""" & .ProcOfLine(StartLine, vbext_pk_Proc) & """)"



Patrick

[matthewspatrick]

Note that the hyperlink is to the workbook, and not to the sub/function. As far as I know, it is not possible to hyperlink directly to the VB Editor.

[lior03]

hello

can a procedure be activated directly from the list?.
thanks

[johnske]

Hi moshe,

This doesn't use hyperlinks, you just click the cell you want...

First, delete the Worksheets.Add and replace it with
With Sheet1 '< or whatever sheet you choose
Cells.ClearContents


Then place this in the Sheet1 module (or whatever other sheet you're going to use)Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("C")) Is Nothing Then
On Error GoTo IsClassModule
Application.Goto Target.Value
End If
Exit Sub

IsClassModule:
ThisWorkbook.VBProject.VBComponents(Target.Offset(0, -1).Value) _
.CodeModule.CodePane.Show
End SubRegards,
John

[matthewspatrick]

can a procedure be activated directly from the list?.


Yes, it can, but IMHO it is a terrible idea :whip unless we make big changes to the underlying code:


The code currently lists all subs and functions; executing a function like this, unless it is a function that takes no arguments, makes no sense
The code will expose subs in object modules (ThisWorkbook, SheetN, UserForms) that may have no context at all if they were executed in this fashion
If you want to give your users a convenient way to launch the subs, you should give them "real" UI elements like shortcut keys, toolbar buttons, menu buttons, controls, etc.

Regards,

Patrick

[Norie]

hello

can a procedure be activated directly from the list?.
thanks
What exactly do you mean by 'activated'?

Do you mean execute the code or open the VBA editor with focus on where the code is?

[johnske]

...so a click on the procedure name will open it.


Oops, :doh: I took this to mean "open the VBE window to view the procedures code". If you mean to 'Call' or 'Run' the procedure in this way, I agree with matthewspatrick, it's not the best way to do it.

Regards,
John :)