PDA

View Full Version : Scroll to a named macro in the VBE



xltrader100
10-21-2012, 07:51 PM
In order to scroll to the macro named myMacro in a VBE Standard Module, I can write code like this:

Sub scrollToMyMacro()
Application.GoTo "myMacro"
End Sub
I can assign this Sub to a button on any sheet and it will instantly teleport to myMacro. The VBE will be opened if it's not open, and the Standard Module containing myMacro will be brought to the front with the cursor positioned at the start of Line1.

This works amazingly well for such a simple command, but I can't get it to work if myMacro lives in a Class Module or in an Addin.

I'd like to be able to jump to a macro that lives in a Userform Module. Is that possible?

Aussiebear
10-21-2012, 07:55 PM
Have you tried to identify the object (your userform by name) and then use the Application.GoTo?

xltrader100
10-21-2012, 09:33 PM
Well, I tried, but I couldn't find any syntax that worked. When GoTo is used like this it's pretty restrictive about what it will accept for an argument.

This is the error I get with that procedure when myMacro is in a Userform module.
"The text you entered is not a valid reference or a defined name."

Aflatoon
10-22-2012, 05:54 AM
A userform (or regular class module) does not exist as an object until you load it so the reference is not valid. For a worksheet module you can simply use "sheet_codename.routine_name"

xltrader100
10-22-2012, 07:19 AM
The Userform is loaded, so that's not the reason why the reference is invalid.

As for "sheet_codename.routine_name", have you actually tried that with GoTo?

Aflatoon
10-22-2012, 07:44 AM
Yes, I have.

Aflatoon
10-22-2012, 07:51 AM
It may be the fact that a userform is not an excel object - why are you trying to jump to code in a running userform instance?

xltrader100
10-22-2012, 08:51 AM
This is a tool for code development. I can trace the program flow through many different modules without having to know which module the procedure lives in. This works extremely well in Standard Modules.

Could you share the code you used to jump to a procedure in a Worksheet Module?

Aflatoon
10-22-2012, 09:06 AM
Shift+f2 takes you straight to the definition of a routine from its name. ;)

I simply used:
application.goto "sheet1.mysub"

xltrader100
10-22-2012, 12:07 PM
Thanks, Aflatoon. Application.GoTo does indeed work for Worksheet Modules when the procedure name is qualified with the sheet's code name, but Shift-f2 still seems to work only for Standard Modules.

So, is there any way I can achieve the same effect of jumping to a procedure in a Form Module, Class Module or Add-in, possibly even using something other than GoTo?

Aflatoon
10-23-2012, 02:25 AM
Shift+f2 (or right-click, Definition) should work with classes and forms if the highlighted routine name is properly qualified - so for example if you declare a variable as Object then Shift+f2 won't work to jump to the code in its class but if you declare it as Class1 for example, it will work.