Programming Components within Excel

Not everything of interest to the VBA programmer can be found in the VBA IDE. There are a few programming-related components that you can access from the Excel application. The components I am referring to are the Macro items found under the Tools menu, and three of the available toolbars—Visual Basic, Control Toolbox, and Forms—found in the View menu in Excel.

Macro Selection

Now that you've had an introduction to the VBA IDE, it's time to look at development tools accessed directly from Excel. To begin, take a closer look at the Macro selection from the Tools menu, shown in Figure 1.3. Notice two other items displayed in Figure 1.3 that I have not yet discussed: Macros and Record New Macro. Essentially the Record Macro tool will allow you to create a VBA program by simply selecting various tasks in Excel through the normal interface. The Record Macro tool is quite helpful, as you will see in Chapter 4 when I discuss it in detail. The Macros menu item will simply display a dialog box with a list of some or all of the currently loaded VBA programs. Again I will explain the Macro menu item in more detail later in the book, but for now, remember that it is one way to access and run desired VBA programs. Figure 1.9 shows the Macro dialog box.

Currently selected^ Macro

List of available-Macros

The Macro dialog box displaying the available VBA programs.

Currently selected^ Macro

List of available-Macros

The Macro dialog box displaying the available VBA programs.

Hi^T-v Macros typically refer to programs that are recorded as the user executes a series of tasks from the normal application interface. They are useful when a user repeatedly performs the same tasks in Excel. Instead of having to repeat tasks, the user can simply record his/her actions once, then "play back" the macro when he/she needs to repeat the same series of tasks. However, it is possible to access programs that were not recorded through the Macro menu item, thus I will use the term macro to refer to both recorded programs and those programs written from scratch.

Hi^T-v Macros typically refer to programs that are recorded as the user executes a series of tasks from the normal application interface. They are useful when a user repeatedly performs the same tasks in Excel. Instead of having to repeat tasks, the user can simply record his/her actions once, then "play back" the macro when he/she needs to repeat the same series of tasks. However, it is possible to access programs that were not recorded through the Macro menu item, thus I will use the term macro to refer to both recorded programs and those programs written from scratch.

The Visual Basic Toolbar

The Visual Basic toolbar shown in Figure 1.4 provides another set of tools for the VBA developer. You have already seen how selecting the Visual Basic Editor icon from this toolbar gives you access to the VBA IDE. There are several other useful items on the Visual Basic toolbar, including Run Macro, Record Macro, and Design Mode, that I will discuss later. Also included on the Visual Basic toolbar is an icon for the Control Toolbox, denoted by the crossed hammer and wrench. The Control Toolbox can also be accessed via the Toolbars item on the View menu.

The Control Toolbox (refer to Figure 1.10) provides you with ActiveX controls which are graphical tools, such as a Check Box or Command Button, that may be associated with a macro. The Text Box, Command Button, Label, and Image Control are just some of the ActiveX controls available and are specifically labeled in Figure 1.10. You place controls on a worksheet by first clicking on the desired control and then drawing it onto the worksheet. Start by selecting the Command Button control and drawing it on a worksheet as shown in Figure 1.11.

The Design v Mode toggle x

The Image control

The Label control ■

The Control Toolbox.

The Label control ■

The Control Toolbox.

The Properties Window toggle

View Code

The Command Button control

The Text Box control

The Properties Window toggle

View Code

The Command Button control

The Text Box control

The Command Button control placed on a worksheet.

The Command Button control placed on a worksheet.

After the Command Button is placed on the worksheet, you will notice that it is selected and the application is currently in Design Mode (check that the Design Mode icon in the upper left corner of the Control Toolbox appears "pressed in"). You can access the properties of the Command Button control while in Design Mode. With the Command Button control selected while in Design Mode, select the Properties icon from the Control Toolbox. A window much like the Properties window in the VBA IDE will appear. The Properties window lists all the attributes or properties used to describe the Command Button control. Figure 1.12 shows the Properties window.

The Properties window of the Command Button control.

The Properties window of the Command Button control.

Command Caption

In the Properties window of the Command Button control change the Caption property to Click Me and then notice how the new caption is displayed on the control. You should also change the Name property to something like cmdColorChange. The prefix cmd references the type of control (Command Button) and the rest of the name refers to the function of the program that is triggered when the button is pressed. You can also play with some of the other properties such as Font, ForeColor, BackColor, Width, and Height to change the appearance of the control. You can even display a picture within the Command Button control through the Picture property, and then select an image file from your computer.

The Name property is an important property of any ActiveX control. The value of the Name property should be changed to something meaningful as soon as the control is added to the worksheet. Typically, an abbreviated word telling us the type of control (the cmd at the beginning of the name above denotes a Command Button) and its function in the program will work well. The Name property of an ActiveX control should be changed if you refer to it in your program. A meaningful name will help you remember it, as well as make the code more readable.

Once the appearance of your Command Button control is to your liking, select the View Code icon from the Control Toolbox, or double click on the Command Button control to access the code window. You will be taken immediately to the VBA IDE. Now it's time to make the Command Button control functional, and you can only do that by adding code to its code window. Figure 1.13 shows the code window for the Command Button control.

The title bar~ The object list -The procedure list The Editor

The VBA IDE showing the code window for the worksheet named

Sheet1.

The title bar~ The object list -The procedure list The Editor

The VBA IDE showing the code window for the worksheet named

Sheet1.

The title bar tells us the object to which this code window belongs. In this case, the code window belongs to the worksheet named Sheet1 in the workbook named Book1. This is because I placed the Command Button control on Sheet1 of Book1 in the Excel application. You may recall that I changed the name of the worksheet in Excel to MySheet, but the name of the worksheet as it will have to be referenced in code is still Sheet1. In the upper left corner of the code window is a dropdown list box containing the names of all objects contained within the selected worksheet. The name of the Command Button control is displayed because the cursor in the editor is within an event procedure of this Command Button control.

Event procedures are self-contained blocks of code that require some type of stimulus in order to run. The stimulus often comes directly from the user (for example, a mouse click), but may also result from another piece of code.

Event procedures are predefined for ActiveX controls and other Excel objects, such as workbooks and worksheets. All event procedures for the selected object are listed in the upper right corner of the code window in a dropdown list box. I will discuss event procedures in more depth in Chapter 3. For now, just take a look at the Click() event. The Click() event is a very common event procedure that is built into most ActiveX controls. Any code placed within the predefined procedure will trigger when the user clicks once on the object—in this case, the Command Button control named cmdColorChange. The procedure is defined as listed in Figure 1.13 with the following two lines of code:

Private Sub cmdColorChange_Click() End Sub

The name of the procedure will always be the name of the object with an underscore followed by the name of the event. You cannot change the name of a predefined event procedure without changing the Name property of the object. If you do change the name of the event procedure, the code within the procedure will not run when you want it to. The keyword Sub is required and is used as the defining opening of any procedure—event-type or programmer-defined. Private is an optional keyword; I'll discuss it in Chapter 3. The second line End Sub is always used to close a procedure. Now type the following lines of code within the Click() event procedure of the Command Button control named cmdColorChange.

Range("A1").Select

Cells.Interior.Colorlndex = Int(Rnd * 56) + 1

These two lines will select cell A1 on the worksheet and set the fill color of all cells in the worksheet to one of fifty-six possible colors. This is the equivalent of a user first selecting all the cells in a worksheet and then changing the fill color from the formatting toolbar in the Excel application. The color of the cells is chosen randomly and will change with each click of the Command Button control because the above code will run once with each click event. So the entire procedure now looks like the following.

Private Sub cmdColorChange_Click() Range("A1").Select

Cells.Interior.Colorlndex = Int(Rnd * 56) + 1 End Sub

Return to the Excel application and exit Design Mode by toggling the icon on the Control Toolbox (refer to Figure 1.10). Now test the program by clicking on the Command Button control. The color of all cells in the worksheet will change color with each click. Figure 1.14 shows an example of my worksheet after one click on the Command Button control.

You can save the workbook as you would an Excel workbook. The Command Button control and event procedure code will be saved with the workbook.

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