Excel VBA: Embarrassing Missed Info I Just Now Found 3

[kjv1611]

First, I imagine if SkipVought sees this, he'll probably remember some time 10 years ago that he told me about one of them, and say, "I told you so!"

So I got to thinking about how the other day, Skip mentioned to someone about the Watch window, and pointed to an FAQ he had put together years ago. Honestly, I remembered about that one, but had never really used it. So I'm trying to make myself start to use stuff like that (but it's hard teaching old dogs new tricks).

Anyhow, I thought this morning when trying to figure something out, I'd simply select the name of a procedure in it's calling statement within another procedure, and right-click it to see what options I got. And immediately, it's like I've uncovered a whole new brave world I never knew existed! Just never even looked under that rock! And of course you know I never took the time to read the help file unless I was searching something specific.

So here are a couple of items I found interesting so far:
[ol 1]
[li]Toggle - Bookmark (also found in the Edit menu)[/li][ul][li]This is just so cool. In the past, I've used a BreakPoint for this and for what the BreakPoint is built for. So now I'll try to remember to just set Bookmarks, b/c apparently with those, you won't mess-up the running execution of code, it just marks spots you want to look at later. Would be nice if we could label them, but I have a separate idea for that: Just add in some old-fashioned comments or line... "title" maybe? Basically instead of a "Line Number", you use a word, such as ExitSub:.[/li][/ul]
[li]Definition[/li][ul][li]So far when I want to "find" a function or procedure, I either just know where it is and go to it or I use Ctrl+F to find it. But this is SO SO much better. You just right-click on the Function or Procedure - I bet it even works for variables - and click Definition, and it'll take you right to the function/procedure... or yep, the variable declaration. I cannot tell you HOW MANY minutes added up to hours I could have saved if I had looked and found that option. Man![/li][/ul]
[li]LastPosition[/li][ul][li]Another item I didn't know existed. And at first, I thought it perhaps meant the last position of the function/procedure/variable you clicked on. Instead, it goes to the last place you did anything in the code! Man, that could be SO helpful at times. And apparently, it saves the last 8 lines that you accessed![/li][/ul][/ol]

So anyway, those were just 3 that really POPPED OUT to me when I pulled up the right-click pop-up menu.

Lesson learned: If working with anything, never assume you know it all (honestly, I didn't on this one), and never stop poking around and exploring, AND never stop learning!

Also, for anyone else interested, here's a list of keyboard shortcuts that cover the right-click menu items and more, I'm sure. It's not the only list available, just one I happened upon when searching for more info:

http://www.techrepublic.com/blog/mi...for-moving-faster-in-the-visual-basic-editor/

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[dhookom]

kjv1611,
Thanks for sharing. Some great functionality that this "old dog" doesn't take advantage of.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

ribbit, ribbit.
I toad you so.

Thx for also sharing these other short-cuts. I’ve never used them either. How many hours could I have saved?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

Man, Skip, if you didn't use those, I don't know if Excel could calculate your time savings.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

To calculate the hours I lost, you’d have to use √-1, (i). Its complex.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

There are a lot of useful functionalities in VBA IDE:
- View > Call stack... in break mode, see the sequence of calls,
- select your vba project in object browser, select module, right-click any of its members and add description to it,
- select your vba project in object browser, select module, double-click any of its members and you are moved to its code.

combo

 

[DjangMan]

Since we're talking about helpful things that we didn't know existed I wanted to point out MZ-Tools (

https://www.mztools.com/)

It's a great collection of utilities for VB/VBA and VS. Find functions that show you all of the results in a separate window, one-button click to add error trapping code, sorting of subs and functions, code indenting, add/remove line number (great for error trapping), etc. etc.

 

[strongm]

Shame that MZ-Tools is now a paid-for product these days

 

[Andrzejek]

2. Definition
I did not see the keyboard shortcut in your link, but it is Shift-F2
I use it all the time. And impress others when I can navigate in their code faster than people who wrote it and work on it.
Ctrl-Shift-F2 gets you back to where you were.

And yes, "it even works for variables "

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[kjv1611]

I'm working now to get these different shortcuts in my everyday work. The only way to start remembering this stuff is to force myself to use it. It's not hard, though. I actually get a thrill out of doing it this way: Shift-F2 and Ctrl-Shift-F2. That's an awesome combo there!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

If you get the trill from using keyboard shortcuts in VBA's IDE, you also may be interested in keyboard shortcuts in Windows
My desktop is pretty much empty since I can get whatever I need from those keyboard shortcuts. Especially useful (at least for me) are Windows logo key + E, Windows logo key + L, Windows logo key + D, etc. Very few people (I know) use the Windows logo key on the keyboard. It is right there at your finger tips, use it!

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[combo]

List of VBA keyboard shortcuts seems not to be published by microsoft (I only found shortcurts in code window). Chip Pearson publishes more complete list here:

http://www.cpearson.com/excel/vbashortcutkeys.htm

combo

 

[kjv1611]

I shoulda thought to look at Chip Pearson's site. Thanks for posting the link, combo!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[strongm]

https://msdn.microsoft.com/en-us/library/da5kh0wa(v=vs.100).aspx