INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel VBA: Embarrassing Missed Info I Just Now Found

Excel VBA: Embarrassing Missed Info I Just Now Found

(OP)
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!" wink

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. blush

So here are a couple of items I found interesting so far:
  1. Toggle - Bookmark (also found in the Edit menu)
    • 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:.
  2. Definition
    • 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!
  3. LastPosition
    • 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!
So anyway, those were just 3 that really POPPED OUT to me when I pulled up the right-click pop-up menu. wink

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/microsoft-office/...


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

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

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

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

🐸 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

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

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

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

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

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

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.

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

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

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

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 " pc1

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

(OP)
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

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

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! pc2

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

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

RE: Excel VBA: Embarrassing Missed Info I Just Now Found

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close