I learned some things today. Apparently I’ve never used Application.Caller in a procedure called from a custom CommandBarButton. Or if I have, and have since forgot about it. As it turns out, Application.Caller returns a Variant array with two elements. The first element seems like it should be the index number of the CommandBarButton from which the procedure was called. That is, if the second button on the CommandBar runs the procedure, the first element would contain a 2. The second element contains string that is the Caption of the CommandBar.
I say “seems” when I refer to the first element, because my CommandBar only contains four buttons.
4
Yet the first element of Application.Caller returns a seven. All of the controls are CommandBarButtons. There are no popups or any other kind of control. I can’t imagine where it’s getting 7. Does anyone know what I’m missing here?
If you’re as surprised as I am that I didn’t know Application.Caller returned an array, then you may be equally surprised at lesson #2 of the day. I can use a For Each construct to access the elements of an array as long as the control variable is Variant. I’ve always looped through arrays with For Next constructs like this
Debug.Print arr(x)
Next x
Now I know I could use this
Debug.Print x
Next x
as long as x is declared as a Variant. I don’t really plan to use this as it seems like an unnecessary use of a Variant.
Finally, an old nugget that I just don’t use enough: multiple statements in the Immediate Window. In VBA procedures, you can put multiple statements on the same line by separating them with a colon. Since statements in the Immediate Window are executed as soon as you enter them, it’s sometimes necessary to employ this technique. When I was trying to figure out what in tarnation Application.Caller was doing, I used a loop in the Immediate Window. To wit:
If you didn’t know you could loop in the Immediate Window, then maybe you learned something today too.
You can get properties of the actioncontrol directly. I use that in my central menu handling routine:
Dim sCommandCaption as String
sCommandCaption = Application.CommandBars.ActionControl.Caption
Select Case sCommandCaption
Case “Settings”
ChangeSettings
Case “StartMe”
StartMe
End Select
Yes, ActionControl. That’s just what I need. Thanks.
Dick,
In similar fashion to JKP I use:
Dim ctl
Set ctl = CommandBars.ActionControl
Select Case ctl.Tag
Case “ShowAllIssues”: MultiFilter(ctl.Parameter)
Case “ShowOpenIssues”: MultiFilter2(ctl.Parameter)
End Select
I like to load extra info into the .Parameter of the button.
Alex
I was aware of using the ‘:’ to produce inline statements. However, they ‘seemed’ to be problematic in the immediate window. (Now for the embarrasing part) You’re example demonstrated to me I was misplacing the print (?) command. :rolleyes:
It’s amazing how simple things are when performed correctly – thanks Dick.
Hmmmm. I’ve never used “?” in an immediate window compound statement, I always have typed out “debug.print”. I’ll have to remember that.
Dick, thanks for the increased volume and interesting variety of posts lately. I didn’t know that about multiple lines in immediate, it will come in handy.
I think I’ve used ActionControl once – in your Self Deleting Commandbar Buttons post. I’ve always liked the concept though.
Have a good flight. I flew first-class for the first time last week – just one way on one leg of my flight, because that’s all there was. Definitely more pleasant. We had turbulence all the way and the flight attendant was keeping a list of who got to go to the bathroom next – I don’t think they had that in coach :).
BTW, Dick,
I always use the Tag parameter of Command Buttons rather than the Caption. I often change my mind about the caption text. And what if I needed to change languages? Sacre Bleu!
Dick –
What you may be missing:
From an old(!) bookMicrosoft Excel 5 Visual Basic ReferenceApplication.Caller
“returns information about how Visual Basic was called, as shown in the following
table.”
CallerA command on a menu
ReturnAn array of three elements specifying the command’s position number,
the menu number, and the menu bar number.
Happy new year.
…Michael
like Peltier I always Debug.Print everything
Until today!!!
It kind of reminds me a few years back I always used to type “? Search terms” into my IE address bar. Nowadays it doesn’t seem to work, plus we have these fancy instant search bars right next to the address bar.
Thanks for a 2006 full of useful tips and tricks!
Michael… I remeber that book….its where I leart about VBA….I kind of liked that book..I remember it came free with the “legal” Excel 5 CD….
I was hoping we would get something similar when we upgraded to 97…. but the book never came with 97 or with 2000 or XP….
Later learnt that Microsoft stopped giving these books free and you had to pay extra for the book….
I always enjoyed learning from a book that from a help file….
Hi Sam –
I got my copy as a “Closeout Book” from CompUSA. The price tag of $4.99 is still on it.
The back of the book says $24.95 ;-)
…Michael
Being the Excel 97-using freak that I am (don’t blame mee, my job requires it!), Application.Caller is documented in the VBA help files. The text of the help file looks very similar to what Michael quotes from the book, but updated.
Since I don’t have a copy of Excel 2000 or above handy, is this no longer the case? And am I the only person who reads of a command on the Daily Dose and immediately types it into the VBE and hits F1?
Stew –
The state of help in Office has continually degraded since ’97. I used to use help all the time, but recently I try, get frustrated, and go to MSDN or Google. The help for VBA isn’t actually all that bad, although it’s hard to get what you want on the first search. Sometimes typing the exact title of an article in quotes will not bring up that article. I don’t yet have a feel for how good Excel 2007’s help is.
Jon –
I see. Looks like my cue to make sure that I have a backup of the help files before we’re upgraded to Office 2003.
Stew –
If you use Google, you can make up for much of the deficiency in online help.