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.
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
Now I know I could use this
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.