Finding VBA Definitions - Excel Tips - Excel TV
Excel TV
Share The LOVE

Finding VBA Definitions – Excel Tips

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

bullet step 1Define a Random Function in any Sheet

Jordan chose to define a Sub called ‘HereIam’ in Module 1 as can be seen from the picture.

jordantip1

bullet step 2In 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.

jordantip2

2014-10-28_17-20-23The 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.

jordantip3

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.

  • Mike Benstead says:

    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 🙂

  • >