Constructing THe Colorful Stats Program

When starting a project, programmers often compile a list of specific requirements, then refer to this list while designing the algorithm(s) that will be followed when writing the program.

The advantage you have when compiling a requirement list is that the source document can be used to build a protocol for testing the program. I will follow this procedure for the chapter projects including the Colorful Stats project that follows.

Requirements of the Colorful Stats Program

The purpose for the Colorful Stats program (as it relates to this book) is to give you a demonstration of ActiveX controls, event procedures, and using VBA to interact with an Excel worksheet. The practical purpose of the Colorful Stats program is to allow a user to immediately calculate basic statistics for a selected set of data. I've defined a few specific requirements for the Colorful Stats program and they are listed as follows:

1. The program shall calculate the following statistics for a selected data set—the number of data elements selected by the user, the minimum value, the maximum value, the sum total, the average value, and the standard deviation.

2. The program shall use Excel worksheet formulas to calculate the statistical parameters listed in Requirement 1.

3. The program shall write the formulas for the statistical parameters to the worksheet cells D2 through D7. Corresponding labels shall be written to cells C2 through C7.

4. The program shall change the interior color of cells C2 through D7 to green.

5. The program shall change the border color of cells C2 through D7 to red.

6. The program shall format the font of cells C2 through D7 to Arial, 16 pt, bold, and blue.

7. The program shall be initiated from a mouse click of a Command Button control placed on the worksheet.

Designing the Colorful Stats Program

When designing a program, I consider the user interface, program inputs and outputs, the location of the code (for example, event procedures of ActiveX controls), and the use and configuration of other programming components that I have not yet discussed. Since this is the first chapter project, I have kept it short and simple to make it easier to follow the design procedure.

I start by making the very simple user interface for the Colorful Stats program. The interface will use a single Command Button control placed on a worksheet to activate the program. I'm assuming that the data will be entered in column A of the worksheet (although this is not required) so I will place the Command Button control in columns C and D, close enough to the top of the worksheet so it is likely to be seen by the user when opened, but below row 7 to avoid masking the statistical values (refer to Figure 1.17). Note that I have altered the Name, Caption, and Font properties of the Command Button control.

Figure 1.17)

The user interface for the Colorful Stats program.

Figure 1.17)

The user interface for the Colorful Stats program.

Excel Vba Design

All program inputs and outputs are from, and to, the current active worksheet. The data used in the calculation of the statistical values must come from the cells that are selected by the user. I will write the program to output cell formulas to the desired worksheet cells so that Excel calculates the statistical values. I must also output labels to the cells adjacent to the statistical values for clarity. I will also format all output as described in the requirements. Finally, the program is to be initiated from a user's click of the Command Button control, so I will enter all programming statements in the Click() event procedure of the Command Button control.

Ideally, the Colorful Stats program would be activated from an interface independent of the worksheet that contains the data (i.e., using an ActiveX control on the worksheet containing the data is not the best solution). The program should also write the statistics to a new worksheet rather than risk overwriting data in the active worksheet. However, this requires a little more programming than I should show you right now.

At this point in the book, the only tool I've shown you for running a loaded macro that may be independent of the selected worksheet is the Macro dialog box (refer to Figure 1.9). As you proceed through this book you will learn other methods for initiating macros and how to create new worksheets.

Coding the Colorful Stats Program

As stated in the previous section, all of the code is to be placed in the Click() event procedure of the Command Button control. The code window can be accessed via the VBA IDE by double clicking on the Command Button control while in Design Mode. You can also select the appropriate object (cmdCalculate) from the object dropdown list in the code window for the worksheet on which the ActiveX control was placed (refer to Figure 1.18).

VBA IDE showing the code window for the worksheet containing the ActiveX Controls of the Colorful Stats project.

VBA IDE showing the code window for the worksheet containing the ActiveX Controls of the Colorful Stats project.

As you can see, the following code was placed in the Click() event procedure of the cmdCalculate Command Button control. Now let's take a closer look at each line of code.

The very first and last lines define the type of procedure as a Click() event, as described earlier in this chapter. Immediately following the opening line of code is a comment.

Comments (or remarks) are notes left in the code by the programmer to help describe the function of the program. Comments make it easier to find problems with the code, or add different features to the code at a later time. Enter comments (also known as remarks) into the code by beginning the line with an apostrophe (or Rem). You must enter another apostrophe for each new line; the VBA text editor will color each comment line green (default color; change by selecting Tools, Options, Editor Format, and Comment Text from the list of Code colors). Comments are not part of the program, and are ignored when the program runs; thus, comments do not decrease the execution speed of a program.

Private Sub cmdCalculate_Click()

'Add formulas for summary stats

With ActiveSheet

'These formulas are entered into the new worksheet.

.range("D2").Formula = "=COUNT(" & ActiveWindow.Selection.Address & ")"

.range("D3").Formula = "=MIN(" & ActiveWindow.Selection.Address & ")"

.range("D4").Formula = "=MAX(" & ActiveWindow.Selection.Address & ")"

.range("D5").Formula = "=SUM(" & ActiveWindow.Selection.Address & ")"

.range("D6").Formula = "=AVERAGE(" & ActiveWindow.Selection.Address & ")"

.range("D7").Formula = "=STDEV(" & ActiveWindow.Selection.Address & ")"

'Add labels and stats

.range("C6").Value = "Average:"

.range("C7").Value = "Stan Dev:" .range("C2:D7").Select End With

'Format the labels and stats.

With Selection

.Font.Size = 16 .Font.Bold = True .Font.Color = vbBlue .Font.Name = "Arial" .Columns.AutoFit .Interior.Color = vbGreen .Borders.Weight = xlThick .Borders.Color = vbRed End With range("A1").Select End Sub

I will discuss code structures, Excel objects, and object syntax in subsequent chapters. If you are even somewhat familiar with Excel, however, you probably have a pretty good idea as to what's happening in the above code. First, the cell formulas are written to the indicated cells (D2 through D7) using the range selected by the user as the parameter for each worksheet function. Next, the statistical labels are written to the corresponding cells in the adjacent columns (C2 through C7). The last part of the program formats the font, border, and color of cells C2 through D7 before selecting cell A1. Another example of the worksheet after some arbitrary data has been entered in column A and the program run is shown in Figure 1.19.

The Colorful Stats program after running.

The Colorful Stats program after running.

That's all there is to it! This code will run once each time the Command Button control is clicked (don't forget to exit Design Mode and select some data first).

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