After having written thousands of lines of codes over multiple sheets or modules, it is already hard enough to remember which sub-procedure does what. On top of that, what if you want to use a particular sub-procedure but you don’t remember where it is defined? Surprisingly, you DO NOT have to Ctrl+F it in every sheet or module. Yes, there is a short cut!
Jordan Goldmeier (aka Option Explicit) is here with his extensive VBA knowledge to help you out. The following explanation uses a small example to illustrate the power of this tip.
So, let the show begin
Define a Random Function in any Sheet
Define a Random Function in any SheetJordan chose to define a Sub called ‘HereIam’ in Module 1 as can be seen from the picture.
In some other Sheet, you want to use ‘HereIam’
In some other Sheet, you want to use ‘HereIam’Now, let’s say you are in Sheet 1 writing up a Sub that does something. You suddenly remember that you might be able to use the sub-procedure ‘HereIam’ to ease your work. But what ‘HereIam’ does is all vague in your head. So, you decide to look its definition up.
Just write “HereIam” anywhere in your program. The result should look like something in this image.
The Final Step!
The Final Step!Right-click on HereIam and left-click on “Definition”.
And done! You must have been taken back to Module 1 where HereIam was defined. No need to waste unnecessary time using Ctrl+F repeatedly.
What’s next?
Use this whenever you are working through a large VBA program.
And, most importantly, do not forget to SHARE it!!
Have a good day.
- SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
- SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
- SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017
I’ve been doing that forever but with a twist; searching for hereiam(). The parentheses take you straight to the hereiam macro regardless if how many times hereiam may appear throughout your modules.
I read somewhere that the use of application.run is preferred to call but I don’t remember why and for this purpose it probably doesn’t matter.
I’m going to try it your way.
This is just more incidence of something being on a menu but when you’re focused on getting a job done you don’t even see, let alone learn how to use a feature until it’s pointed out to you and the light globe goes on. It’s like using Excel for a decade before you discover F4, Alt+enter or Alt+G/objects and go “Damn! I wish I’d have known that all those years.
Keep the tips flowing 🙂