Challenges

Add a modeless form (set the ShowModal property of the UserForm object) to a VBA project then add two Command Button controls to a worksheet. Using their Click() event procedures, use one Command Button control to show the form, and the other Command Button control to hide the form. 2. Add a RefEdit control and a Command Button control to the form created in the previous challenge. The RefEdit control is for the user to display a selected range. Then add code to the Command Button control such...

The Range Object

The Range object represents a group of one or more contiguous cells in an Excel worksheet. The Range object is one level beneath the Worksheet object in Excel's object hierarchy, and it is extremely useful, as it allows us to manipulate the properties of an individual cell or collection of cells in a worksheet. You will probably find yourself using the Range object in every program you write using VBA for the Excel application. Consider the following code examples that use properties of the...

Writing Tests

The code module for the worksheet used to create or edit tests must contain procedures that import and export data between the mapped ranges in the worksheet and the two XML files that describe a test document's properties and its problems. The first procedure listed is the Click() event of the Command Button control labeled Save File (see Figure 8.11). This procedure exports the data entered in the lists to two separate XML files (the test and test properties files). Both file names are...

Sequential Access Files

Writing information to a sequential access file is sort of like recording music to a cassette tape. The songs vary in length and are recorded one after the other. Because it is hard to know the location of each song on the tape, it is difficult to quickly access a particular song. When information is written to a sequential file, the individual pieces of data (usually stored in variables) vary in length and are written to the file one after the other. For example, a sequential file containing...

Chapter Project The Auenated Game

The Alienated Game uses a chart sheet for the user interface (see Figure 9.12) and illustrates the use of several VBA objects subordinate to the Chart object. The program uses the less common bubble chart type because the data markers (represented by Point objects in VBA) in a regular scatter chart cannot hold images. A total of ten data series with ten values each are charted and their markers are randomly filled with one of seven images. The object of the game is to swap two images such that...

Computer Selections Placing Ships and Firing at the Player

Selecting locations for the computer's ships is a more challenging problem in that the randomly selected locations must be validated using the same rules as for the user's ships. To randomly generate a ship's location, I need two numbers that represent a single cell's row and column index. These two numbers will have to be bound such that the cell falls within the computer's grid. One additional random number (0 or 1) is required to determine the direction (0 horizontal, 1 vertical) the ship is...

Designing the Alienated Game

My goal for this project is to illustrate how to program with Excel's Chart object model, so its interface must take advantage of an Excel chart. This makes the project unusual with respect to everything you've seen thus far because the game's interface will not involve a worksheet or VBA form. Nevertheless, charts are constructed in order to display data that is typically stored in a worksheet so the game will still require many of the common Excel objects you have seen in other projects.

In the Real World

At the most basic level, you can think of a memory location in your computer as a sequence of electrical switches that can be on or off. With these two possible conditions we have the basis for the binary language a computer understands (0 for off and 1 for on). The values stored by a programming variable are then just a patterned sequence of switches that are either on or off. Some languages, such as C or C++, allow the programmer to directly access memory locations of variables. This extends...

Installing and Enabling VBA

Unfortunately, there are enough unscrupulous programmers out in the world that security is of paramount importance for your computer. Computer viruses are common and, like technology in general, are becoming increasingly more complex. Macro language viruses such as those written in VBA are relatively easy to write even for a beginning programmer. As a result, Microsoft has added several levels of security to its Office programs in order to protect against macro viruses. The first level of...

The Worksheet Object

The Worksheet object falls just under the Workbook object in Excel's object hierarchy. To investigate some of the events of the Worksheet object, the following code has been added to the SelectionChange() event procedure of Sheetl in the Center.xls workbook. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim msgOutput As String msgOutput The name of this worksheet is & Worksheets(1).Name MsgBox (msgOutput) Worksheets(2).Select End Sub The SelectionChange() event procedure was...

One Dimensional Arrays

An array is a variable used to hold a group of related values it must be declared just as a variable is declared. An array is declared with a single name and the number of elements (values) that can be stored in the array. Dim myArray(number of elements) As Type You may also declare arrays using the Public or Private keywords to define the scope as you would with a regular variable declaration. If you do not specify a data type, then, like a variable, the array will be a variant type. Arrays...

Arrays

Normally, arrays are not discussed until the end of introductory programming books however, as you are already familiar with spreadsheet applications, the concept of an array should come easily. An array is a variable that can hold multiple values. You should use arrays when a related set of values is to be stored in a variable. Doing so relieves you from having to declare a new variable with a unique name for each value in the set. Arrays are convenient as they simplify programming code...

Requirements for Word Find

The objectives for the Word Find project are to demonstrate some basic techniques for file I O and error handling in a VBA program. To accomplish the task, I use an Excel worksheet as the grid for a word search puzzle and a VBA form for updating the data required by the program. The requirements for the program follow 1. A VBA form (UserForm object) shall be used as the interface for updating the program's data (words and topics) stored in a random access file. 2. The form shall display all...

Coding the Alienated Game

Since the program interface consists of a single chart sheet and the program requires the Select() event of the Chart object, I have entered all of the program code in the module for Module level declarations include a string for holding the path to the image files, integers for holding the series and point numbers for the two images selected by the user, and a custom data type defining the type DataPoints. I will use variables declared as DataPoints to hold the last cell in the range of cells...

Programming Components within Excel

Command Caption

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. 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...

Getting Help with VBA

I can't emphasize enough how important it is that you become comfortable with the on-line help in the VBA IDE (not to mention in the Excel application). The on-line help provides fast access to solutions for any programming problems you have with your project. Books make good resources and are much better at teaching you how to program, but they can't cover everything. Often, all you need to see is a simple example of how to use a particular function or other keyword the on-line help does...

The Chart Object

A graphical representation of the Charts collection object and Chart object are shown in Figure 9.2. The figure shows the objects and collections that are subordinate to the Chart object. Many of these components also have numerous subordinate objects, so Figure 9.2 does not illustrate the breadth of the Chart object. You should not be intimidated, though, because programming the Chart object involves many of the same techniques that have been discussed throughout this book. The goal of this...

Accessing Existing Charts

When creating a chart in Excel, you have the choice of embedding the chart in an existing worksheet or creating a new worksheet to hold the chart. When a chart is created and placed in a new worksheet, it is referred to as a chart sheet. Chart sheets are special because their only function is to display a chart they cannot be used for holding any other data. Worksheets and chart sheets serve as containers for embedded charts. There are no limits (other than system memory) to the number of...

Taking a Test

A majority of the code for the Math Game program is still located in the same worksheet module as the original program from Chapter 4. Once the test begins, the algorithm is pretty much the same, but instead of generating problems randomly, they are read from the Create_Edit_Tests worksheet. Since the algorithm and much of the code is nearly the same as the program from Chapter 4, I will limit the discussion to the new sections of the program. Before a test begins, a student must sign in by...

Mapping the Images

Keeping track of the images and their locations in the chart is critical if the game is going to work properly. Keep in mind that the chart's images are actually data markers it is natural to think of deleting or changing the data values to simulate image swaps or deletions. You could probably even design the program to function by altering the charted values, but that seems too complicated. Since the bubble chart will constantly have to display 100 images in a 10 by 10 grid, it will be a lot...

Constructing THe Colorful Stats Program

Excel Vba Design

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...

V

ValidateName() function procedure, 107, 108, 109 ValidatePt() function procedure, 423 Validation defined, 107 XML, 338 Validation module, 198 Validation procedures, 270 in Battlecell program, 198-202 Value integer element, in Blackjack program, 247, 249 Value property, 26, 40, 110, 120, 315 of Combo Box control, 226 of List Box control, 226, 230 of MultiPage control, 224, 229 Option Button control and, 216 RefEdit control and, 222 of Scroll Bar control, 387 Values in spreadsheet cell, 26...

The Chart Sheet Interface

Refedit Excel

This chapter teaches you how to program Excel's Chart object so the interface for the project is built from a chart. Specifically, a chart sheet consisting of a bubble chart will serve to display the images. The requirements state that the game must involve a 10 by 10 grid of 100 images. To satisfy this requirement I will create the chart from ten data sets consisting of ten x,y-value pairs. The data is charted as ten different series in the chart. Each data set must use the same set of values...

General Purpose Public Procedures

The procedures listed below could be used in just about any VBA project. You have already seen the PlayWav() procedure in the Battlecell program from Chapter 5. I have added one more procedure called Delay(). The entire content of the code module follows Public Declare Function sndPlaySoundA Lib winmm.dll _ (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long Public Sub PlayWav(filePath As String) sndPlaySoundA filePath, CONTINUE_CODE End Sub Public Sub Delay(curTime As Single,...

Initializing Battlecell and Starting the Game

The worksheet module named Battlesheet contains most of the game's code because this worksheet serves as the user interface and contains the Command Button control that starts play. The code begins with a few module-level variables that are used in multiple procedures in this module. Private allowSelection As Boolean Private gameStarted As Boolean Private ships As Variant Private Const NUMSHIPS 5 The module level variables allowSelection and gameStarted are used by the program to distinguish...

Saving Worksheets to XML Files

Saving existing data from a worksheet to an XML file is easy. Select File, Save As from the application window and choose one of two possibilities for XML file types from the Save As dialog box as shown in Figure 8.8. If you choose to save the data as an XML spreadsheet, Excel will use its own schema to define the document. As you might expect, the XML required to define a spreadsheet is quite long, but you don't have to worry about that because Excel creates it for you. The root tag is <...

VisuAL Basic for Applications with Excel

In this first chapter, I introduce you to the programming tools available in Excel. These tools include the VBA IDE (Integrated Development Environment), controls and functions available through the main Excel application, and VBA on-line help. After your introduction to the VBA programming environment, I take you through a very short and simple program that calculates some basic statistics from a sample data set. The program displays the statistics in a worksheet formatted with a large font,...

B

Bar chart, 384, 385, 391 Battlecell program, 149-150, 246 coding, 182-202 computer selections placing ships and firing at player, 192-198 initializing Battlecell and starting game, 185-188 opening and closing Battlecell workbook, 182-185 player selections placing ships and firing at computer, 188-192 sound added to VBA program, 203-205 validating selections, 198-202 constructing, 177 designing, 180-182 requirements for, 177-180 Battlecell workbook, opening and closing, 182-185 Battlecell...

Rolling the Dice

The Command Button control's (cmdRollDice) Click() event procedure loads images of dice into the Image controls. The image for each control is selected randomly from one of six choices. The static integer variable numRolls keeps track of how many times the user has clicked on this button. The variable numRolls is incremented by one each time this procedure executes however, the user is allowed only two clicks per game. For example, when numRolls reaches a value of two, it resets to zero near...

Scoring the Answers

The ScoreAnswers() sub procedure called at the end of the game from the MathGame() procedure reads the questions asked during the game from variable arrays and displays them on the worksheet. This procedure also checks the user's answers and outputs the score as a percentage of questions answered correctly. I use a For Next loop to iterate through the arrays holding the questions and answers, because I know the number of questions that were asked during the game is stored in the module level...

Requirements for the Math Game Program

The original interface to the Math Game program required a single worksheet that presented randomly generated math problems, timed the game, and scored the results when the time allotted reached zero. The student taking the test was allowed to choose the mathematical operation. I've kept that interface pretty much intact removing the Option Button controls that allowed the student to choose the mathematical operator for the problems, and adding one Combo Box control that displays the list of...

Chart Sheets

In Chapter 5, you learned that a collection of Worksheet objects were members of the Worksheets collection object. Chart sheets (see Figure 9.3), on the other hand, are not included with this collection. This makes sense because a chart sheet is not a spreadsheet and should not be contained in a collection object called Worksheets. Instead, chart sheets are members of two different collection objects the Sheets and Charts collection objects. The Sheets collection object has broader scope,...

Starting and Initializing the Math Game Program

The Click() event procedure of the cmdBegin Command Button control serves as the main procedure in the Math Game program. This procedure initializes a few variables, clears the worksheet, and makes several calls to sub procedures that get the game started. EnableControls False numQuestions 0 Range(A2 C & UsedRange.Rows.Count).ClearContents Range(Answer).Select Application.MoveAfterReturn False 'Get the operator type and operands for the question. 'Mark the start time and start the clock....

Validation with a Spreadsheet Cell

In older versions of Excel, validation of spreadsheet content meant writing a lot of code to ensure the data was of proper type and or format. With the latest versions of Excel, this is no longer the case. Data validation is now included in the Excel application, so you don't necessarily have to write any code. Figure 4.2 to shows the data validation dialog box (select Data, Validation from the Excel application menu). Use this tool in your spreadsheets to force validation of data entered by...

A

Absolute references, denoting, 122 Activate() event, 57, 211, 212, 392 playing a hand of Blackjack and, 252 shuffling deck for Blackjack program and, 248,249 of UserForm object, 314, 315 Activate() method of Range object, 349 of Worksheet object, 158 ActiveCell property, 161 ActiveX controls, 9, 11, 18, 151, 184, 203, 266, 440 adding to forms, 212-213 adding to Math Game program, 134-136 in Blackjack game, 239 Enabled property of, 82 forms customized with, 208 within Frame control, 218 on Math...

About the Author

Uane Birnbaum began programming in graduate school, where he wrote custom software for interfacing the electronic equipment required for his experiments and analyzing the data obtained from them. Since completing his Ph.D. in physical chemistry in 1991, he has worked as a post-doctoral and research scientist in academia and industry while continuing to teach on a part-time basis. He has been teaching courses in introductory programming, database design, and data analysis in the Computer Science...

Project Excetris

Excetris is modeled after the classic Tetris computer game. The object of the game is to fill a predefined region on an Excel worksheet with five basic shapes so that gaps between the shapes are avoided. The player is continuously given one shape to add to the game board within a limited time period. When an entire row across the game board is filled with shapes, the row is removed and the shapes above moved down. Play continues until the player runs out of room for adding more shapes. You will...

T

TabOrientation property, MultiPage control and, 223 Tags, HTML, 331 Target argument, 169 Target locations, in Battlecell program, 188-192, 196 Test results, viewing in new Math Game program, 356-357, 361-363 TestRow() function procedure, 462 Tests creating in new Math Game program, 352-355 taking in new Math Game program, 351-352, 364-373 writing for new Math Game program, 357-361 theDeck array, in Blackjack program, 247, 249, 251 Timer, starting in Math Game program, 140-143 ToggleControls()...

For Each and Looping through a Range

As stated at the beginning of this chapter, objects are often built from other objects. For example a Workbook object usually contains several Worksheet objects, which in turn contains multiple Range objects. It may be necessary, on occasion, to select individual objects contained within other objects. For example, you may want to access each individual Worksheet object in a Worksheets collection object in order to set certain properties. If you are thinking loops then you are right on track,...

Chart Events

The Chart object has several events that are triggered by various user actions. Some of the events are familiar like Activate , MouseDown , and MouseUp but a few are unique to the Chart object. Table 9.3 summarizes the less familiar events associated with the Chart object. Chart object events are not automatically enabled with embedded charts. Although Chart object events can be enabled for embedded charts, the methods involved are beyond the scope of this book. Calculate When new or changed...

Adding Sound to Your VBA Program

Microsoft removed support for playing sound files in Excel several versions ago. This leaves two choices for playing sounds in Excel applications with VBA ActiveX controls and the Windows API application programming interface . As there are no ActiveX controls for playing sound that currently ship with VBA, the Windows API will be used for adding sound to your VBA programs in this book. There is a multimedia control that comes with Windows and it can be used to play sound files in your VBA...

Embedded Charts

To access embedded charts, use the ChartObjects collection and ChartObject objects. A ChartObjects collection object contains all ChartObject objects on a worksheet or chart sheet. A ChartObject object is a container for a single Chart object, but not if this Chart object represents a chart sheet. If there was such a thing as a Sheet object, then I would tell you the ChartObjects and ChartObject objects are subordinate objects of the Sheet object however, there is no Sheet object in the Excel...

The Application Object

The Application object is the top-level object in Excel's object model. It represents the entirety of the Excel application see Figure 5.6 . As the top-level object it is unique and thus, seldom needs to be addressed in code however, there are a few occasions when you must use the Application object's qualifier in code. One example is the OnTime method used in the Math Game program in Chapter 4. Other examples where the Application object must be explicitly referenced in code include the Width...

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...

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...

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...

Vba Program Challenges

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 User Form 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...

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...

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

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...

Magic Squares Spreadsheet Selectionchange Procedure

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...

Table Dialog Types Used with the File Dialog 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...

IfThen Else

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...

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...

Generating Random Questions and Operators

You have already seen how to generate random numbers in VBA. The Math Game program requires the operands for each question to be randomly generated and the operator is randomly generated if the user selects the proper Option Button control the button labeled Any, see Figure 4.7 . The GetOperatorType procedure tests the Value property of the Option Button controls to see which operator has been selected by the user. If the user selects the option Any, then the GetRandomOperator procedure is...

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...

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

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

Excel Xml Denormalize

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...

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...

The Shape Range Collection Object

Shapes Excel

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...

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 Bior HytHMs 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 Biorh Ythms and the Time of Your Life PRogRAm

The Time Your Life

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

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 Ref Edit Control

Refedit Control Vba

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...

File IO Using Workbook and Worksheet Objects

The Workbook and Worksheet objects contain methods for opening, closing, and saving workbooks in the same manner a user might perform these operations from the Excel application. You can open and save workbook files using a variety of formats with VBA code. The same file formats may also be used to save individual worksheets within an existing workbook. You use the Open method of the Workbooks collection object to open Excel-compatible files. The Open method accepts numerous arguments, but the...

Table Select Properties of the Blackjack Form

Python Programming Precedence Table

Blackjack CenterScreen fmBorderStyleNone frmDealer and frmPlayer Dealer and Player fmBorderStyleSingle imgDlrl through imgDlr5 and imgPlayerl through imgPlayer5 fmBorderStyleSingle cmbNumDecks fmStyleDropDownList l fmStyleDropDownCombo 2 cmdHit Hit False cmdDeal Begin True Table 6.6 Select Properties of the Blackjack Form continued Table 6.6 Select Properties of the Blackjack Form continued To set the size of the Image controls, I first set the AutoSize property of one Image control to true....

The Scroll Bar Control

You have undoubtedly seen and used scroll bars in numerous applications for scrolling through lengthy documents or large figures. Scroll bars sometimes automatically appear on the sides and or the bottom ofVBA controls so the user can view the entire content displayed in a control. Situations such as these require nothing extra from you, or your program the scroll bars are simply there to provide the user with a method of seeing the complete content of the control however, VBA also provides a...

XML Schemas

XML schemas are text-based documents written in the XML schema language that describe the structure of your XML document s . An XML schema is, in effect, the definition of your language. In order for other people to use your language in their application they need the definitions described in the schema. Other applications need this definition in order to understand the elements' meaning in your XML document otherwise, the language cannot be understood except by your own applications because,...

Creating a Chart Sheet

The sub procedure AddChartSheet creates a new chart sheet and a column chart of sample data selected from a worksheet by the user. The worksheet range that contains the data is selected via a custom dialog box using methods discussed in Chapter 6. The Add method of the Charts collection object is used to create a column chart on a new chart sheet. Remember, the Charts collection object represents a collection of chart sheets in a workbook refer to Table 9.1 . After the chart sheet is added, the...

Requirements for the Math Game

If you have young children or teach in elementary school, then you can use the Math Game as a testing tool of basic math skills probably first and second graders . Your kids may not enjoy the test, but you can have a lot of fun writing it and after you are comfortable with VBA, add more features to the program to suit your needs. The requirements of the Math Game as I have defined them follow 1. The user interface shall consist of a single spreadsheet formatted to accentuate the numerical...

Dynamic Arrays

The BubbleSort2 and Transpose sub procedures use arrays with fixed lengths. The number of values in fixed length arrays cannot be changed while the program is running. This is fine as long as the required length of the array is known before running the program however, the use of dynamic arrays allows programmers to create a more robust program. Wouldn't the BubbleSort2 procedure be more useful if it sorted data with any number of values rather than just ten values A similar question can be...

The Xml Map Object

When you open an XML file, either programmatically or through the application interface, Excel automatically creates an XML map. An XML map is represented in VBA by the XmlMap object. An XML map serves to map the elements and attributes of an XML file to worksheet ranges. For example, the XML map named word_find_Map in Figure 8.7 maps the range A1 A23 to the lt topic gt element in the words.xml document and the range B1 B23 to the lt word gt element. Each XmlMap object is contained in an...

Logical Operators with VBA

Logic as applied to a computer program is evaluating an expression as true or false. An expression is typically, but not always, a comparison of two variables such as var1 gt var2 or var1 var2 see Table 3.1 for a list of available comparison operators . A programmer reads these expressions as follows The value of var1 is greater than the value of var2. The value of var1 equals the value of var2. The statements are evaluated as true or false. Imagine a simple device that takes a single...

Break Mode

When your program generates a runtime error, a dialog box similar to the one shown in Figure 7.4 is displayed. Selecting the Debug option will load the VBA IDE and display the program in Break Mode. While in Break Mode, program execution is paused and can be stepped through one line at a time to closely examine factors such as order of code execution and the current values stored within variables. The line of code that generated the error will be highlighted as shown in Figure 7.5. To...

Creating an Embedded Chart

Refedit Excel

To add an embedded chart to a worksheet, use the Add method of the ChartObects collection object. The AddEmbeddedChart sub procedure creates the same column chart as the AddChartSheet sub procedure listed in the previous section however, it embeds the chart on an existing worksheet named Embedded Charts. Public Sub AddEmbeddedChart Dim dataRange As Range Set dataRange Range frmDataRange.txtDataRange.Text frmDataRange.Hide Sheets Create Chart .ChartObjects.Add Left 200, _ Top 50, Width 500,...

Scanning the Chart

What Cellrange Excel 2007

Scanning the chart sheet is required immediately after images are added to the bubble chart. A chart scan must be triggered when a new game begins and when the player swaps two images. Since a chart scan may ultimately result in the removal of images and subsequent addition of new images, this may trigger more scans. The last procedure called from sub Main is ProcessChart . The ProcessChart sub procedure essentially outlines the process of scanning a chart for score sequences, updating the...

Additional Controls

Advanced Userforms

UserForms are programmable containers for ActiveX controls. They enable you to build customized windows to serve as a user interface in any VBA application. UserForms are similar to other VBA objects in that they have properties, methods, and events that you use to control the appearance and behavior of your interface window but the main function of a UserForm is to serve as a container for other ActiveX controls. UserForms are part of the VBA object library, and therefore, are available to use...

Starting the Timer

Timer Excel

The essence of the Math Game program is contained within the sub procedure appropriately named MathGame . This procedure controls the game's clock and calls the sub procedures that score the user's answers when the clock reaches zero. The clock is controlled with a very special method of the Application object the OnTime method. You can use the OnTime method to set up repetitive calls to the same procedure based on a given time increment in this case, one second. To begin, the MathGame...

Select Case

There are innumerable ways to accomplish the same task with If Then Else and ElseIf code structures. But keep in mind that using a large number of If Then Else and ElseIf statements can make it difficult to follow the logic of your program. You should consider using the Select Case code structure in situations where you find yourself using a large number of ElseIf statements. The Select Case code structure is used when you need to test the value of a variable multiple times and, based on the...

XML Validation

Validation is the process of testing XML documents and schemas to ensure that they follow the rules of the language. After writing an XML document, you should check to ensure that it is well-formed that is, the document must adhere to the syntax rules I've already discussed. The process of testing a document's form is analogous to compiling a VBA program. When your VBA program contains syntax errors, you receive a compile error and the debugger is invoked. When your XML document is not...

The Image Control

The Image control is used to display image files most commonly bitmaps, jpegs, or gifs . The Image control can be added to a worksheet from the control toolbox like any other ActiveX control. Figure 3.10 shows the icon for the Image control. Image files can be loaded into the Image control at Design Time or run time via the Picture property. Some of the more important properties of the Image control are summarized in Table 3.5 Table 3.5 Selected Properties of the Image Control Used for...

Introduction to XML

You may already be familiar with the HyperText Markup L anguage HTML which is used by Web developers to instruct browsers on how to display Web pages. For example, when the following HTML code is saved as a text file with an .html or .htm extension, any Web browser can recognize the file and display its markup. In this case, a browser displays the message HELLO WORLD on a white background as shown in Figure 8.2. lt TITLE gt Basic HTML Document lt TITLE gt lt HEAD gt lt P gt HELLO WORLD lt P gt...

File Input and Output IO

VBA includes several objects, methods, and functions that can be used for file I O. You have probably surmised that one possibility for file I O involves the Workbook object and its methods for saving and opening files however, there are other tools available in VBA, the most relevant of which will be discussed in this chapter. When a VBA application requires file I O, it often involves a relatively small amount of data stored in program variables, and not in a worksheet or document. With...

Using VBA File IO Methods

In addition to the Open , Save , and SaveAs methods of the Workbooks, Workbook, and Worksheet objects, VBA and its associated object libraries include several I O objects such as the Dialogs, FileDialog, FileSystem, and FileSystemObject objects, and other subordinate objects. Some of these objects are conceptually more difficult to use and therefore will not be discussed in this chapter however, I will show you how to use one object from the Office library and VBA's Open statement for adding...

Using XML with ExceLVBA Projects

If you have any experience with the World Wide Web, whether it's developing Web sites or just browsing, then I am sure you have heard of XML eXtensible Markup Language . Although not a new technology, it has only been in recent years that XML has generated a lot of interest. This is partially evident by Microsoft's decision to add XML support to some of its Office programs including Excel starting with version 10.0 XP and extending that support in version 11.0 2003 . I expect the level of XML...