Convert to a Function - Open the VBA Editor to Module and Sub Procedure Name

[dj44]

Hi folks,

Good Friday the 13th :)

This is complex as I may preface.

I am needing some help and advice on how I may be able to convert something to a function.

I have spent a lot of time and just got no where on my function writing escapade so I have stripped back to basics.

The function opens to a specific module and sub procedure.

So as is it works normally.

But I shudder to have to repeat the same code for 20 macros so I rememebred you can make a function but that’s where I am stumped.





Sub OpenVBA1()

'GoToVBA("Module2","TestMacro")


End Sub


'Function GoToVBA( ## As Range -something here)

End Function




Sub OpenVBEGoToSpecificMacro()



Dim lStartLine As Long
Application.VBE.MainWindow.Visible = True
ThisDocument.VBProject.VBComponents("Module2").Activate

With Application.VBE.ActiveCodePane.CodeModule
lStartLine = .ProcStartLine("Test2", 0)
.CodePane.SetSelection lStartLine, 1, lStartLine, 1
End With
'https://www.pcreview.co.uk/threads/programmatically-open-vbe-and-go-to-a-specified-procedure-in-target-module.982766/
End Sub










Please take a look and advise.

It would be great if the function could just go to the sub name but you cant do that in word, in excel you can write the sub name and use application go to but not with word.

So im not sure what is best a class module?

Many thanks for your time

[gmaxey]

What exactly are you trying to do?

[dj44]

Hello Greg,


Nice to see you


I was trying to cut down on the code.


So I can call 1 function every time for different modules and subs I need to go to


Example






I will make a list of these calling the main function

Sub OpenVBA1()
GoToVBA("Module2","Macro2")
End Sub



Sub OpenVBA2()
GoToVBA("Module3","Macro3")
End Sub



Sub OpenVBA3()
GoToVBA("Module3","somemacro")
End Sub






Otherwise I can just repeat the main big block each time, and that will make a lot of code for me.



So I believe I need to pass some variables into a function and parameters, but I havent got far in the past 3 days.

[gmaxey]

So what is GoToVBA

[dj44]

Hi Greg,
I thought i would name my function GoToVBA

So the name of the function can be anything , I just thought GoToVBA, for lack of imagination

hope that names not already taken by vba

[dj44]

I see what you mean, I was trying to convert the macro in the original post
Sub OpenVBEGoToSpecificMacro() into a function and name the function GoToVBA

:doh:

[gmaxey]

So do you have your answer or are you looking for something like this:


Sub Demo()
OpenVBEGoToSpecificMacro "Module2", "Test2"
End Sub
Sub OpenVBEGoToSpecificMacro(strMod As String, strProc As String)
Dim lStartLine As Long
Application.VBE.MainWindow.Visible = True
ThisDocument.VBProject.VBComponents(strMod).Activate
With Application.VBE.ActiveCodePane.CodeModule
lStartLine = .ProcStartLine(strProc, 0)
.CodePane.SetSelection lStartLine, 1, lStartLine, 1
End With
lbl_Exit:
Exit Sub
End Sub

OpneVBEGoToSpecificMacro is a procedure that takes arguments. A function returns a value. You haven't indicated what value you are after.

[dj44]

Hi Greg,

You did it - happy days !

No I still have not been able to fix this, despite yesterday looking and looking for more information that took me all together the wrong way.


You are the function master, you always make lots of them so eloquentially.


My foray into function writing is still abysmally poor as, these parameters things I cant get my head around.

(strMod As String, strProc As String) << I couldn’t find or fix at all



the reason I needed this is because i was repeating the same block and i ended up deleting the wrong macros and that was a big head ache because i had to then go and find my original ones a long story for another day

Thanks for your code writing skills in helping me and have a great weekend

I really reaaaaaaaaaaaaalllly apreciate your talent thank you

All folks too

good weekend :)

[gmaxey]

Maybe this will help:


Option Explicit
Sub Demo()
ProcedureThatTakesArguments 2, 2
MsgBox FunctionsReturnAValue
MsgBox FunctionsReturnAValueAndTakeArguments(5, 5)
End Sub
Sub ProcedureThatTakesArguments(lngA As Long, lngB As Long)
MsgBox lngA + lngB
End Sub
Function FunctionsReturnAValue() As Long
FunctionsReturnAValue = ActiveDocument.Words.Count
End Function
Function FunctionsReturnAValueAndTakeArguments(lngA As Long, lngB As Long) As Long
FunctionsReturnAValueAndTakeArguments = lngA + lngB
End Function

[dj44]

Thank you Greg,
I will put that in my testing document book to test and tweak!