Question on myMacroLstToSh()

[Woodstock66]

Hi
Some time ago I found this Excel macro on MrExcel: myMacroLstToSh().
It lists all projects and macro's you have in your Excel workbook,
It is rather old, but works fine.
I was wondering whether it is possible to add a hyperlink of some kind that 'brings' you to the macro in the project instead of running it

Thanks in advance.

Sub myMacroLstToSh()
'You will need to create a reference to MS VBA Extensibilty 5.3
'(in VBE go to Tools>References) to work this code, it was set in this version.
'Standard module code, like: Module1.
Dim VBCodeMod As CodeModule
Dim StartLine&, nRow&

'Row to add list labels in [Top of List Row]!
nRow = 5

'My list's column labels.
Sheets("Sheet2").Select
ActiveSheet.Cells(nRow, 1) = "Module's"
ActiveSheet.Cells(nRow, 1).Font.Bold = True
ActiveSheet.Cells(nRow, 1).HorizontalAlignment = xlCenter

ActiveSheet.Cells(nRow, 2) = "Macro's"
ActiveSheet.Cells(nRow, 2).Font.Bold = True
ActiveSheet.Cells(nRow, 2).HorizontalAlignment = xlCenter

'Get Modules.
For Each projMod In Application.VBE.ActiveVBProject.VBComponents
Set VBCodeMod = Application.ActiveWorkbook.VBProject.VBComponents(projMod.Name).CodeModule

With VBCodeMod
'Index the module's name!
StartLine = .CountOfDeclarationLines + 1

Do Until StartLine >= .CountOfLines
'Increase Row for each listing!
nRow = nRow + 1
'Load cell with name!
Sheets("Sheet2").Cells(nRow, 1) = projMod.Name
Sheets("Sheet2").Cells(nRow, 2) = .ProcOfLine(StartLine, vbext_pk_Proc)

StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Next projMod
End Sub

 

[Micron]

At first I wondered if there would be a way to replicate ctrl+F, pass the search term and replicate the dialog button push but then figured it would never work because code would be running. Tested ctrl+f while stepping through a sub and guess what? Dialog opened and I could find the search term in code.
Then, apparently it's possible to run code from a hyperlink so maybe make use of its descriptive text or something (the name of the sub), and pass that to a sub that scans through the project modules and opens that procedure. I have very little experience with the vb editor object model so I couldn't even begin to suggest how to do that.
HTH

 

[Woodstock66]

At first I wondered if there would be a way to replicate ctrl+F, pass the search term and replicate the dialog button push but then figured it would never work because code would be running. Tested ctrl+f while stepping through a sub and guess what? Dialog opened and I could find the search term in code.
Then, apparently it's possible to run code from a hyperlink so maybe make use of its descriptive text or something (the name of the sub), and pass that to a sub that scans through the project modules and opens that procedure. I have very little experience with the vb editor object model so I couldn't even begin to suggest how to do that.
HTH

Hi Micron
Thanks for sharing your thoughts. It's a nice idea, but unfortunately I'm also not that experienced.
"Google" did not give me a solution so far, that is why I try here.
Hopefully others have some ideas as well, or may be it is just not possible.

 

[Micron]

The vb editor (or perhaps more accurately, a vb project) is another world of objects that I'm not real familiar with. I have researched and have seen that there is a VBIDE object model with things like CodeModule and CodePane objects, but when I browse the object model documentation it just makes my head spin! I gathered from your post that maybe you knew more about that than I did. I find the whole object model somewhat daunting. If you want to go down that rabbit hole into Wonderland you might start with

Visual Basic Add-in Model reference

Office VBA reference topic

docs.microsoft.com

Maybe you'll meet Alice or the Cheshire Cat in there!

 

[MARK858]

@Micron if you want to learn a bit about programming the editor you are probably better off looking at the article Chip Pearson wrote

Programming In The VBA Editor

www.cpearson.com

 

[Woodstock66]

@MARK858 : thanks for the reference to the article.
I'll have a look whether this brings me further.

 

[Woodstock66]

Hi
I found a solution in this post: Macro to call Visual Basic Editor?

Sub GoToMacro(macroName As String)
Application.Goto Reference:=macroName
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Value <> "" And ActiveCell.Column = 2 Then
Call GoToMacro(ActiveCell.Value)
End If
End Sub

 

[Micron]

@MARK858; thanks - I'll bookmark that. It looks like a much better resource than the cryptic M$ documentation.