Objects Defined
There is no need to get too abstract here with the definition of an object. It really is a pretty simple thing to understand. You can think of objects as separate computer programs with specific and often common functions that are available for repeated use in your programs. Objects are dynamic in that they can be easily manipulated in code with the various parameters used to define them. In one common analogy, objects are equated as nouns in the English language. A programming object can be...
A Style References
The A1 style uses the column and row headings letters and numbers, respectively as indices to reference a particular worksheet cell for example, A1, B5, C 2, etc. . Dollar signs in front of an index denote an absolute reference the lack of a dollar sign on an index denotes a relative reference. The A1 style reference is the preferred style of most Excel users. Creating a formula using VBA is easy. Instead of using the Value property of the range returned by the Cells property, you use the...
Chapter Project Excetris
How to play the Excetris game was described at the beginning of the chapter and the worksheet containing the game is shown again in Figure 10.10. My objective for this program is to demonstrate the use of the Shapes collection object and some of its component objects while creating a fun program. Excetris involves a minimal amount of animation involving a small group of Shape objects as they move down the area of the worksheet defined as the game board cells C3 L17 in Figure 10.10 . VBA is...
Coding Excetris
The entire program is entered into a single standard module. The general declarations section of the program contains just two module-level variable declarations and the definition of a custom data type ExcetrisShape . The variable gameShape is declared as type ExcetrisShape and will be used to define the properties of the active shape the shape that moves down the game board. The other module-level variable, numRotations tracks the number of 90 degree rotations the player selected for the...
Challenges Coa
1. Load the Wordfind.xls project and open the IDE. Find the PlaceWordO sub procedure and set a breakpoint on the statement If Not SelectionValid wordDirection Then. Return to Excel and run the program by clicking the Clear All button. Do not click refresh Next, select a cell in the puzzle grid and click on an arrow button. When the debugger is invoked follow the order of program execution to see which statement generates the runtime error and triggers the code in the error handler. 2. Load the...
Components of the UserForm Object
In the same manner as ActiveX controls, when you select a form, its properties appear in the Properties window in the VBA IDE see Figure 6.3 . Table 6.1 lists some of the properties of the UserForm object that you will commonly set at Design Time. Table 6.1 Selected Properties of the UserForm Object Name Sets the name of the UserForm object for use as a code BackColor Sets the background color of the form. Caption Sets the text displayed in the title bar. Height Sets the height of the form....
Showing and Hiding Forms
To display a form from the Excel application, call the Show method of the UserForm object in a procedure that can be triggered from Excel a public procedure in a standard module or an event procedure from an object module . The basic syntax follows To Load a form into system memory without displaying it, call VBA's Load method. The UserForm object and all of its components can be accessed programmatically after loading it into memory. Note that the Show method will also load a form if it has...
For Loops
When you know the number of iterations required from a loop, the For Next loop is the best choice of structures. The syntax is very simple. For variable start To end Step value The required keywords are For, To, and Next. To keep track of the number of iterations through the loop requires a counting variable as well as starting and ending values. The keyword Step is optional but if it's used, the value that follows it is used to denote the step size of the counting variable with each iteration...
RCStyle References
The R1C1 style uses the letters R for row and C for column followed by numbers to reference spreadsheet cells. For example, R -1 C 2 is a relative reference to the cell one row lower and two columns higher than the cell that contains this reference in a formula. To denote an absolute reference, leave off the brackets for example, R-1C2 . The R1C1 reference style can be turned on in the Excel application by clicking Tools, Options, General, and then clicking R1C1 reference style as shown in...
Designing Custom Dialog Boxes Using Forms
As mentioned earlier, forms are generally used as dialog boxes to collect user input relevant to the current application. You use ActiveX controls to expand the capabilities of forms well beyond that of the InputBox and MsgBox functions. Some of the ActiveX controls available for forms are identical to those used with an Excel worksheet, but there are also a few new controls as well as others I have not yet discussed therefore, with the aid of a couple of examples, I will illustrate the use of...
ByVal and ByRef
You should have noticed the ByVal keyword in the parameter list of the MouseDown event procedure shown earlier in the chapter. The ByVal keyword tells VBA to make a copy of the value stored in the accompanying variable. Thus, any manipulation of the copied value within the procedure does not affect the original variable. The alternative to passing a variable by value is to pass a variable to another procedure by reference the ByRef keyword is used to do so. When you pass by reference you are...
The VBA Integrated Development Environment IDE
Before learning how to program in VBA, you have to learn how to use the software required for creating your projects. The VBA development software is included with each component of the Microsoft Office suite of programs, including Excel. Starting the VBA development software places you in the VBA programming environment IDE, which provides you with a number of tools for use in the development of your project. An IDE is software used by programmers for rapid application development RAD . IDE's...
Opening and Importing XML Documents into an Excel Worksheet
To open an XML document from the Excel application, select File, Open and then choose the desired XML file .xml file extension from the Open dialog box. After selecting a file, you will be asked if you want to open the file as an XML list, read-only workbook, or to use the XML Source Task Pane see Figure 8.3 . Typically, you load the data into a worksheet as an Excel list in order to take advantage of the data management features a list provides. Please select how you would lite to open this...
Designing Excetris
I constructed Excetris from an Excel worksheet and added the code to a standard module, but the program could just as easily be entered into the code module for the worksheet take your choice. The worksheet cells that define the game board must be square and will match the size of the individual squares in a game shape. The game can easily be initiated from a form button or Command Button control by attaching the form button to a public procedure, or calling the same procedure from the Click...
VariabLEs Data Types and Constants
Since this book focuses on a spreadsheet application, it's only natural that I introduce variables by asking you to think about the following what types of values can be entered into a spreadsheet cell and how you might use them You know that you can enter numbers and text in any spreadsheet cell in Excel. Also, you may or may not know that the format of a spreadsheet cell can be changed to one of several possibilities. For example, a number can be formatted such that the value is displayed...
Manipulating a Shape Object
After a Shape object is selected from the Shapes collection object, you can edit the shape through its properties and methods. As always, the properties and methods available are specific to the type of Shape object. Also, there may be properties and methods of subordinate objects available for editing. The following example adds a rectangle to the active worksheet and manipulates a few of its properties the result is shown in Figure 10.4. 100, 100, 50, 50 .Select With Selection .Name Red...
Event Procedures
You have already seen a few examples of event procedures such as the Click event procedure of a Command Button control, and the SelectionChange event procedure of a worksheet. VBA predefines these procedures in the sense that you cannot change the name of the procedure, nor the object within Excel to which the procedure belongs, nor the conditions under which the procedure is triggered. For the most part, all you can do with these procedures is add the code to be executed when the event is...
The Immediate Window
Stepping through code one line at a time can be tedious if the error is not found quickly. The Immediate window allows you to test program variables and procedures under normal program execution. The Immediate window is displayed by selecting it from the View menu, the Debug toolbar refer to Figure 7.5 , or by pressing Ctrl G in the IDE. The Immediate window is often used to hold the value of a variable or variables written to it with debugging statements located at suspected trouble spots in...
P
Pages collection object, MultiPage control and, 223 PageSetup object, PrintArea property of, 324 Parameters, 40, 41 with event procedures, 57-59 Parent class, 151 Parentheses, order of operator execution and, 92 PassByRef procedure, 64 PassByVal procedure, 64 PasswordChar property, 299 Paste method, 121 Path property, of Workbook object, 281 Period . , avoiding in XML element names, 336 Picture property, 13, 79, 241, 317 Pie chart, 384 PieClock.xls workbook, 388 PlaceComputerShips sub...
In the Real World Klm
Many applications save data to a type of random access file that is more commonly referred to as a database. Database files such as those created by MS Access .mdb extension offer a lot more power to the programmer relative to the random access file created by VBA's Open statement. A single database file normally contains multiple tables of data that are linked together through related fields columns . Furthermore, it is usually possible to use a database's programming engine to link your VBA...
Table Dialog Types Used with the FileDialog Object
Dialog Type VBA Constant FileDialogType File Picker msoFileDialogFilePicker Folder The Execute method allows the user to carry out the specified action of the dialog box for files that are compatible with the Excel application for example, files of type .xls, .xlt, .csv, and so on . For example, the Open dialog box allows the user to select one or more files to open when the Execute method of the FileDialog object is invoked. When the following statement follows the Show method for the Open...
IfThenElse
There are several ways to implement this code structure. The most basic uses the two required keywords If and Then. In the example above, the code statement following Then will execute if condition evaluates as true otherwise code execution proceeds with the next statement. The entire structure takes just one line of code. It's convenient when you have just one brief code statement that needs to be executed if the condition is true. Multiple statements can be entered on the same line if you...
The Workbook and Window Objects
You have already seen in action, in some of the examples in this chapter, the Workbooks, and Worksheets collection objects, as well as the Workbook and Worksheet objects. The difference between collection objects and regular objects was discussed earlier. When working with these objects, keep in mind that the Workbook object is higher in the hierarchy than the Worksheet object. If you are familiar with Excel, this makes sense to you because a single workbook can hold multiple worksheets....
Using Excel Application Functions in VBA
Now that you know how to write functions in VBA and make them available to your spreadsheets, you are also aware that you can re-create any function already available in the Excel application. Although recreating Excel's functions would be a good way to improve your VBA programming skills, it's certainly not a practical use of your time. Why reinvent what's already been created for you It would be nice if you could use Excel's application functions in your VBA code, as they are mostly...
Defining Enumerated Types in VBA
Like custom data types, enumerated types contain multiple elements however, enumerated types are derived only from the integer data type. In an enumerated type, each integer is assigned an identifier called an enumerated constant. This allows you to use symbolic names rather than numbers, making your program more readable. You must define an enumerated type in the general declarations section of a module. Once an enumeration is defined, you can declare variables, parameters, or a procedure's...
Saving and Opening XML Documents
To save a workbook as an XML document use the SaveAs method of the Workbook object. The following example saves the workbook as an XML document with the name myFile.xml using two named arguments Filename and FileFormat with the SaveAs method. ActiveWorkbook.SaveAs Filename myFile.xml, FileFormat xlXMLSpreadsheet The constant xlXMLSpreadsheet assigned to the FileFormat argument specifies the Excel-XML format. To open an XML document previously saved with the Excel-XML structure use either the...
The Object Data Type
A chapter on Excel objects would not be complete without a discussion of the object data type. If you find multiple instances of the same object in your program, then you can use an object variable to handle the reference rather than constantly retyping the qualifiers. Also, variables can be assigned meaningful names, making the program easier to interpret. Object variable are similar to other VBA data types in that they must be declared in code. For example, declares an object variable named...
Chart Sheets 1
Chart events are automatically enabled with chart sheets. To catch events triggered by the user in a chart sheet, add code to an event procedure contained in the module associated with the chart sheet. The code window can be opened in the same manner as with a worksheet. Figure 9.10 shows the code window of a chart sheet selected from the project explorer. The active project displayed in Figure 9.10 is an Excel workbook containing several chart sheets. Adding code to an event procedure of a...
Opening and Closing the Battlecell Workbook
Several of the requirements for the Battlecell program can best be satisfied using the Open , BeforeClose , and WindowResize events of the Workbook object. The Open event is triggered when a workbook file .xls extension is first opened, thus making it an ideal location for sizing both the application and workbook windows. The WindowResize event procedure is triggered whenever the user resizes the workbook window, so it must also include code that ensures the Battlecell game board is in the...
Manipulating Charts
You can create several different types of charts in Excel, including the common column and pie charts and the not-so-common doughnut and radar charts. Table 9.2 summarizes the more commonly used chart types available in Excel and their function. Compares categorized values by charting the data as vertical columns running from 0 to the charted value. There is one column for each value and all columns in the same category have the same color. The same as a column chart, except that the columns...
Beginning Programs with VBA
Now that you know your way around the VBA IDE for Excel, it's time to introduce some basic programming concepts common to all languages. The next three chapters are devoted to these basic programming structures that, although they may not be that exciting, are essential for developing VBA projects. Specifically, in this chapter we look at
Challenges 1
1. Draw a simple image of a smiley face using MS Paint then load the image into an Image control placed on a worksheet in Excel. Using the MouseDown event procedure of the Image control, write a program that displays a message to the user every time the user clicks on the image. The message should tell the user if he or she clicked on the eyes, nose, mouth, or face of the image and which button they used. The message can be displayed with a message box, or in a Label control, or on the...
Modal Forms
The Show method of the UserForm object takes an optional Boolean parameter that specifies whether or not the form is modal. The default value of the modal parameter is true, which creates a modal form. A modal form is one that must be addressed by the user, and subsequently closed by the user or the program before any other part of the Excel application can be accessed. If the form is modeless, then the user may select between any open windows in the Excel application. Modeless forms are only...
Creating Tests
Tests are written from a separate worksheet interface. Figure 8.11 shows the Create_Edit_Tests worksheet with problems from an existing test imported into its data list. Create_Edit_ Tests worksheet used to write or edit tests. Create_Edit_ Tests worksheet used to write or edit tests. The data in the worksheet is formatted as an Excel list and is mapped to two XML files. The range A2 C2 is mapped to an XML file with the following structure lt xml version 1.0 encoding UTF-8 gt lt test_properties...
XML Element Names
I've already discussed some aspects of naming your XML element tags, such as their case-sensitivity and the convention to use all lowercase characters. There are a few more rules and conventions regarding element names. Rules that you must follow include Names may contain letters, numbers, and other characters. Names must not start with a number or punctuation character. Names must not start with XML or any other form of these letters in a different case . Names must not contain spaces....
XML Elements Must Have a Closing Tag
In HTML, you can get away with omitting the closing tag for many of its elements. For example, you can omit the closing tags lt P gt and lt LI gt for closing paragraphs and list elements, respectively. lt P gt This is my paragraph. lt LI gt This is an item in my list. In XML, all elements must have a closing tag. To distinguish a closing tag from an opening tag, you use a forward slash inside the angle bracket of the second tag, as follows lt p gt This is my paragraph. lt p gt lt li gt This is...
The Option Button Control
The Option Button control is similar to that of the Check Box control in that it offers the user a selection from a group of possibilities. The difference between the two the Option Button control gives the user one selection therefore, when the user selects an Option Button from a group of Option Buttons, a previously selected Option Button is automatically deselected. Option Button controls are grouped by the container on which they have been added. So no matter how many Option Button...
Locating the Code for Poker Dice
Requirement 2 for the Poker Dice program specifies that a button will be used to initiate the program. Other requirements specify actions for mouse clicks on images as well as another button. Since all ActiveX controls are drawn on the same worksheet, the entire program can be written in the object module for the worksheet containing the game board. The Click BackStyle Caption SpecialEffect Value BackStyle Caption SpecialEffect Value event procedures of the two Command Button controls and the...
Selecting the Dice
To begin, let's write code that allows a user to select a dice to hold when he or she clicks on its image. This means you have to change the Value property of the Check Box controls from the Click event procedure of the Image controls. The user is allowed to toggle the Check Box on and off, so you should use the Not operator to change the Boolean value of the Check Box's Value property. The user can accomplish the same thing by clicking on the Check Box directly however, you don't need to write...
Taking a Test
The interface used to take a test is shown in Figure 8.10. I removed the Option Button controls from the Chapter 4 program and added a Combo Box and a Command Button control otherwise, the interface is the same. I set the Style property of the ComboBox control to fmStyleDropDownList so the student cannot enter a new name but only choose existing names from the list. As usual, I also edited the Name property and a few appearance properties of the ActiveX controls at design time. The process of...
Challenges
1. Write a program that will add two numbers input by the user and display the result in a spreadsheet. Use an input box and the Va1 function to convert the user input to a numerical data type. 2. Place a Command Button control on a worksheet and write a program in the C1ick event procedure that increments a variable by 5 with every click of the mouse. Output the value of this variable in a message box. 3. Write a program that extracts the time from the string returned by the Now function and...
The ShapeRange Collection Object
The ShapeRange collection object represents a collection of Shape objects that may contain all, some, or just one of the Shape objects in the drawing layer of a worksheet. A ShapeRange collection object can be constructed from the current shapes using any of several criteria defined in decision structures If Then . For example, a ShapeRange collection object could be constructed out of just those shapes that are of type AutoShape, or perhaps only those Shape objects that are lines. If you want...
Conditionals and Branching
It may seem like I've covered a fair amount of VBA programming, but in reality, I've barely scratched the surface. Right now, you can't really do much with the VBA programs you've written, because you haven't learned any programming structures however, that is about to change as I begin to examine a simple yet very useful VBA code structure. The If Then Else structure is known as both a conditional and branching structure because it uses conditional statements to change the flow or direction of...
Initializing the Chart Sheet
The public sub procedure Main is triggered from the form button on the chart sheet and contains calls to the initialization procedures for the chart sheet, then scans the chart for score sequences. Screen updating is initially turned off otherwise Excel will update the screen as images are added or removed from the chart. Screen updating is turned back on so that the user can see the chart before it is scanned for score sequences. Note that the ChartTitle object is used to display help messages...
Project BiorHytHMs and tHe TiMe of Your Life
The Biorhythms and the Time of Your Life program see Figure 2.1 begins by asking for the user's name and birth date. The program then calculates the length of the user's life in years, months, days, hours, minutes, and seconds. Following the user input, the user's name, birth date, and age in the aforementioned units are displayed in the worksheet. The worksheet also contains an embedded chart that displays the current state of the user's three biorhythm cycles physical, emotional, and...
Constructing the BiorhYthms and the Time of Your Life PRogRAm
This project will utilize several of the VBA programming components discussed in this chapter. The project contains several different examples of data types including integer, floating point, string, and date types. I introduce some new functions designed to work with the date and string data types. The project also demonstrates nesting functions, the use of constants, and some simple mathematical operations. The majority of the work for this project will be handled by the Excel application via...
Chapter Summary Pud
In this chapter, you covered a considerable amount of material on some of the tools required to help you build a strong programming foundation. You started by taking an in-depth look at procedures in VBA specifically, event, sub, and function procedures. You learned how to use and build these procedures while considering the procedure's scope, available parameters, and return values function procedures . You even learned how to build new function procedures to use within formulas created in the...
the shapes collecnon and shape objects
The Shapes collection object represents all Shape objects in the drawing layer of the worksheet. The Shapes property of the Worksheet object is used to return the entire collection of Shape objects in the drawing layer. The following line of code uses the Count property of the Shapes collection object to return the total number of shapes in the drawing layer of the active worksheet You can think of the drawing Layer as a sheet of clear plastic cellophane draped over the top of the worksheet...
The RefEdit Control
A common requirement for custom dialog boxes is providing an interface in which the user can select a range of cells from a worksheet. Your program then uses the selected range for some specific task. The RefEdit control makes it easy to acquire a worksheet range from a form. Several of Excel's dialogs and wizards contain RefEdit controls, including the chart wizard shown in Figure 6.8. The RefEdit control allows the user to select a range from an existing Excel worksheet, and have the textual...
Adding ActiveX Controls to a Form
Like the Worksheet object, the UserForm object is a container object, meaning it is used to hold other objects. When a form is added to a project, the Control Toolbox should automatically appear see Figure 6.3 . If the Control Toolbox does not appear, select View Toolbox from the menu bar. There will be a few additional controls displayed in the control toolbox when viewed with a form relative to a worksheet , including the MultiPage and Frame controls discussed later . ActiveX controls are...