Today’s VBA Productivity Tip introduces the editor window’s procedure list. This list serves two purposes: help find existing subroutines, or access built-in subroutines.
The procedures list exists as a drop-down in the upper-right corner of the coding window, active whenever a module is open. This list can be useful in one of two ways: searching for a subroutine or function within a module, or finding built-in worksheet, workbook, or ActiveX control procedures.
Searching for a Subroutine
The procedures list’s most basic use is to search for an existing subroutines or functions. If a module contains many subroutines and functions, the drop-down list will display the name of each of them.
Selecting a specific procedure from the list will move the cursor, as well as the window view, to the selected procedure. This is effectively a “jump-to” list. The subroutines and functions within the list will always be displayed in alphabetical order, even if procedures are not in that order in the editor window main editor window.
The procedure list will only display available procedures in the currently active module. The procedure list does not offer any method of accessing all procedures within a project.
Finding Built-In Subroutines
As discussed on the previous post Calling Subroutines with Worksheet and Workbook Events, there are a number of built-in subroutine names which can be used to automatically initiate a procedure. The previous post highlighted some common subroutines used with worksheet and workbook events. There is also a library available for ActiveX controls and classes.
The procedure list can act as a reference for finding available events. There are three important behaviors to point out when using the procedure list to find options available for events and controls.
- Given that event procedures cannot exist in basic modules, the reference lists will only exist in the worksheet, workbook or class modules.
- The list will only be accessible if one built-in procedure already exists in the module, and the text cursor must be focused either within or after the subroutine. If a non-built-in subroutine or function is included in the module, the text cursor must be within the event subroutine to see the event procedures list.
- As long as at least one event exists within a worksheet or workbook module, all available events will be displayed in the procedures list. This is where the procedure list can be used as a reference, providing a list of all available events.
After manually creating a simple event, such as Worksheet_Change, the procedure list will be populated by all available built-in events available for worksheets. It’s important to remember that if you initially set up a worksheet event, the procedures list will only display other worksheet events (not Workbook or ActiveX events) while the cursor is in that event’s namespace.
Clicking on an event will set up the procedure name and available parameters. Given that Worksheet events impact specific Worksheets, the events can only be created within specific Worksheet modules within the Microsoft Excel Objects folder branch. For example, an event present in the Sheet1 module will only be in effect within Sheet1. More information on worksheet events can be found here.
Almost identical to the method for generating the list of worksheet events, a list of workbook events will be available upon manually creating an initial event.
Clicking on a event will set up the procedure name and available parameters. Given that workbook events impact the entire workbook, the events can only be created within the ThisWorkbook module within the Microsoft Excel Objects folder branch. More information on workbook events can be found here.
ActiveX Control Events
Several built-in events exist for ActiveX controls. The events vary depending on the control type. Gaining access to the list of available events is quite easy — in addition to initially typing one event to activate the list, the control can be double clicked in the worksheet, opening up a “Change” or “Click” event depending on the control type. A quick summary on how to creative ActiveX controls and a simple event can be found here.
ActiveX events will always be associated with the worksheet module tied to the worksheet containing the ActiveX control. If the control exists on a worksheet which already has worksheet events, the list presented in the procedures list will depend on the namespace the cursor is in. If the cursor is within or between two worksheet events, only the list of available workbook events will be presented. Likewise, if the cursor is within or between two ActiveX control events, the list of ActiveX control events will be available.
Two exception to note with ActiveX controls:
- The reference list will vary depending on the specific control the event is associated with. For example, while there is some overlap in event types, the TextBox ActiveX control has unique events, such as TextBox_Change, which will fire a script whenever the value in the textbox changes.
- Each event will only impact a specific control within a worksheet. If two buttons exist, each will need to be assigned its own event procedure and script. This is dependent on the subroutine name, which is associated with the control name within the control’s properties window. In the picture above, the first ActiveX button is given the default name CommandButton1, which associates with the CommandButton1_Click event. If a second ActiveX button were created, its default name would be CommandButton2, which would be associated only with the CommandButton2_Click event, requiring its own procedure and script. If both buttons need to run the same script, it’s worthwhile to initiate a call to a specific subroutine to prevent duplication, as was discussed in these previous posts.
The Class_Initialize and Class_Terminate events will be made available upon a listing of one of the two. These built-in subroutines are only available in class modules, and hence, the procedure list reference will only be available in a class module.
Similar to worksheet, workbook, and ActiveX events, the reference list will only be available within the namespace of the Initialize or Terminate procedures. If custom subroutines and functions exist within the class and the text cursor is within their namespace, the procedure list will revert back to be a jump-to link for the custom procedures. This is quite useful for complex classes, as the procedures list will display all custom procedures and properties (with PropertyGet or PropertyLet in square brackets]
- The procedures list is limited for Class Collections. It will not display the Item, Count, or Add procedures.