Parameters with Event Procedures

Parameters are the list of one or more variables passed to the event procedure when it is triggered. The values of the parameters passed to the event procedure contain information related to the event. A comma separates multiple variables, and the variable data type is also declared. VBA defines everything about the parameters passed to the event procedure; including the number of parameters, the name of each parameter and their data types, and the method in which they are passed. Although it is possible to change the name of the variables in the parameter list under certain circumstances, I do not recommend editing the event procedure definition in any way.

The following example shows the MouseDown() event procedure of a Command Button control. This procedure triggers when the user clicks on the Command Button control with the mouse. The first and last lines of the procedure are automatically created by VBA. I added the four lines of code within the procedure.

Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) Range("A2").Value = Button Range("B2").Value = Shift Range("C2").Value = X Range("D2").Value = Y End Sub

There are four parameters passed to the MouseDown() event procedure: Button, Shift, X, and Y; they have all been declared as numerical data types. These parameters contain numerical information describing the event that just occurred, and they can be used as variables within the procedure because they have already been declared. The ByVal keyword will be discussed later in this chapter, so just ignore it for now. The previous code was added to the MouseDown() event procedure of a Command Button control placed on a worksheet with a few column headers as shown in Figure 3.3.

Parameter values of the

MouseDown() event procedure.

Parameter values of the

MouseDown() event procedure.

Attending And Planned Ignoring Worksheet

The values of the parameter variables are copied to the appropriate cells in this worksheet when the user clicks on the Command Button control. The variable Button represents the mouse button that was clicked—a value of 1 for the left mouse button, 2 for the right mouse button, and 3 for the middle mouse button (if it exists). The variable Shift represents the combination of Shift, Ctrl, and Alt keys held down while the mouse button was clicked. Since there are eight possible combinations of these three keys, the variable Shift can hold an integer value between zero and seven. The variables X and Y represent the location of the mouse cursor within the Command Button control when the mouse button was clicked. The values of X and Y fall within zero to the value of the Width property of the Command Button control for X, and zero to the value of the Height property for Y. The upper left corner of the Command Button control is X = 0, Y = 0.

You now see how helpful the information within these parameters can be. For example, a programmer might use the MouseDown() and MouseUp() event procedures of an ActiveX control to catch a right click of the mouse button on the control. The MouseDown() event procedure might be used to display a menu with various options, and the MouseUp() event procedure would then be used to hide the menu. Does this sound familiar?

It is both impractical and unnecessary to discuss all of the event procedures of all Excel objects and ActiveX controls in this book. The examples you have seen so far are a good representation of how to use event procedures in VBA. In order to establish which event procedures (if any) should be used in your program, do the following:

• Ask yourself, "When should something happen?"

• Search for the event procedure(s) that will be triggered by the answer to the question, "When should something happen?" The event procedures have sensible names related to the action that triggers them; however, it may be useful to look up the description of the event procedure in the online help.

• If you cannot find an event procedure that triggers when desired, redesign your program with ActiveX controls that do contain a useful event procedure. If you still can't find anything, then there are probably errors in the logic of your algorithm.

• Test possible procedures by writing simple programs such as the one for the MouseDown() event procedure listed earlier.

• Insert the code that carries out the tasks you want once you recognize the proper event procedure.

Was this article helpful?

0 0
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment