Adding the ActiveX Controls

The program design calls for one Command Button and five Option Button controls. You should be quite familiar with the Command Button control. The Option Button control is similar to a Check Box except that a user can only select one button from a group. A group of Option Button controls is defined by their container—in this case, a worksheet. It does not matter how many Option Button controls I add to a worksheet, the user will only be able to select one. Table 4.3 shows selected properties of the ActiveX controls for the Math Game program that I changed at design time. (Size and display properties are not shown in the table, but, as is usually the case were edited from their default values.)

Addition is set as the default operator for the game by setting the Value property of optAdd to true. The controls are initially enabled so that the user may choose an operator and start the game. The entire program is contained in the object module of the formatted worksheet that contains the ActiveX controls.

Several module level variables are declared, including three dynamic arrays (mathQuestions, mathOperators, and userAnswers) for storing the questions, operators, and the user's answers. The variable opType will tell the program what mathematical operation is currently being used in the question. The variables numQuestions, curDate, and gameRunning store the number of questions asked, the current date and time (used later to set the timer), and a Boolean value used by the program to know whether or not the Change() event of the worksheet should be ignored. These variables are declared at module level because more than one procedure in the program needs to access and/or manipulate them. The module level variable declarations and the Click() event procedures of the Option Button controls follow:

Keep the number of module level variables in your program to an absolute minimum. A common trap novice programmers make is to create most of the variables module level (and later global when you include more modules in your program). Although module level variables may seem convenient, they can make your program prone to logic errors that are difficult to debug.

Table 4.3 Selected Properties of the ActiveX Controls Used in the Math Game r

Table 4.3 Selected Properties of the ActiveX Controls Used in the Math Game

ActiveX Control

Property

Value

Command Button

Name

cmdBegin

Caption

Begin

Option Button

Name

optAdd

Value

True

Caption

+

Option Button

Name

optSubtract

Value

False

Caption

-

Option Button

Name

optMultiply

Value

False

Caption

x

Option Button

Name

optDivide

Value

False

Caption

/

Option Button

Name

optAny

Value

False

Caption

Private mathQuestions() As Integer Private mathOperators() As String Private userAnswers() As Integer Private opType As Integer Private numQuestions As Integer Private curDate As Date Private gameRunning As Boolean

Private Sub optAdd_Click()

Range("Operator").Value = "+" opType = 1 End Sub

Private Sub optSubtract_Click() Range("Operator").Value = "-" opType = 2 End Sub

Private Sub optMultiply_Click() Range("Operator").Value = "x" opType = 3 End Sub

Private Sub optDivide_Click()

Range("Operator").Value = "/" opType = 4 End Sub

Private Sub optAny_Click()

Range("Operator").Value = "" End Sub

The purpose of these Click() event procedures is to write the operator (+, -, x, and /) to the appropriate worksheet cell (merged cells H8:H9). Please note: I defined a named range for cells H8:H9 in the Excel application calling it Operator. I can now use this name in my program to refer to the range. This makes the code easier to read (otherwise known as self-documenting). The module level variable opType is assigned a designated integer (1 = addition, 2 = subtraction, 3 = multiplication, and 4 = division) with each click of an Option Button control. The program will need to read the value of opType when storing each question.

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