In the Real World

In the highly competitive world of computer technology it is a rare occurrence when everyone agrees on a standard. The XML standard (defined by the World Wide Web Consortium see http www.w3c.org) has been widely adopted and continues to grow in terms of its use and available support tools (editors, code libraries, samples, and so on). The popularity of XML continues to grow even though it is relatively simplistic when compared to other data-communication technologies used to store and transfer...

WHats in THis Book and WHat Is Required

I developed the programs in this book using Excel 2003 for Windows. Although Excel and VBA don't change much from one version to the next, I can't guarantee that the programs in this book will execute without error in earlier versions of Excel. With each new version of Excel, VBA is updated with new objects, and existing objects are expanded with new properties and methods. If I use even one new object, property, or method specific to VBA-Excel 2003 in a program, then it will generate an error...

Chapter Summary

In this chapter, you were introduced to XML by learning its purpose, definition, and basic syntax. You also learned how to open and save XML documents from the Excel application window and the advantages of adding the data to an Excel list. Finally, you learned how to use several new objects in the Excel object model designed to support XML. This included the XmlMap object and the ListObject object and some of their associated and or subordinate objects. For the chapter project, you revisited...

An XML Sample

In the Math Game program, I create an XML language that describes an elementary math test. The following XML code describes a test for the Math Game program. The code is only a portion of the document, but the omitted portion is repetitive. or the purchase of special licenses, is intriguin < xml version 1.0 encoding UTF-8> < test> < number> 1< number> < operator> +< operator> < answer> 3< answer> < problem> < problem> < number> 2< number> <...

Z

Z-axis, in chart sheet, 389 Zero, divide by error, 270 Zero-length string, 41 ZoomGameBoard() sub procedure, 183, 185 Zoom property, of Window object, 169, 181 ZOrder() method, of Shape object, 431 techniques for the game developers of tomorrow Perfect i programming skills and create eye-catching art for your game keep players coming back for more. BEGINNING 'if illustration STDRYBOARC ,, FUR GA Beginning Illustration and Storyboarding for Games Check out advanced books and the full Game...

Using the On Error Statement

In the MultiPage.xls project from Chapter 6, the Click() event of the cmdCalcStats Command Button control contained the statement The On Error statement enables error handling in a VBA program. The On Error statement must be followed with instructions to VBA for deciding a course of action when a runtime error is encountered. The course of action taken depends on the type of error that is anticipated. The On Error statement must precede the code that is anticipated to generate the runtime...

Writing the Code for the Worksheet Module

The remaining code is entered into the code module for the Worksheet object and controls the creation of a word search puzzle. This part of the program only reads data from the hidden worksheet (Lists) so it does not require any file I O. The code for the Worksheet object module is listed next. In the same manner as the Activate() event of the UserForm object, the Click() event of the Refresh button (Name property cmdRefresh) serves to fill the Combo Box and List Box controls with the unique...

XML Attributes Must Be Enclosed in Quotes

In XML, tags may have attributes assigned as name value pairs. For example, in the XML document describing a test for the Math Game, I could extend the definition of the < test> tag to include a test identification number for the purpose of describing the level of difficulty. In this example, testID is an attribute of the tag < test> and it must be assigned a value using quotes. Use child elements instead of attributes as much as possible in your XML documents. There are no rules in XML...

What Is XML

XML is a text-based markup language designed to describe a document's data, not its appearance. HTML is great for displaying data, but it is difficult and awkward to use for describing data. XML serves to separate the two processes making it easier to do both. Because HTML displays data and XML describes data, the two technologies are complimentary, not competitive.

Designing the Form

The program is divided into two parts the first part contains the form used to update the data file and the second part contains the worksheet used to create a puzzle. Figure 7.16 shows the form's design from the IDE. Whenever a program is required to display a list, you should immediately think List Box or Combo Box control. I have added a Combo Box control to the form for displaying the list of topics and a List Box control for displaying the list of words associated with a selected topic....

Designing the Worksheet

A word search puzzle is created on a worksheet the design of which is shown in Figure 7.17. A worksheet makes an ideal interface for this program since it is easy to write letters to the cells and print a portion of the worksheet (as you will see in the program code). As was done with the form, the worksheet contains a Combo Box and List Box control for displaying the topic and word lists, respectively. The data listed in the ActiveX controls on the puzzle worksheet will be read only from the...

Final Word

Congratulations on finishing this book. You are now ready to tackle your own VBA projects in Excel. You will find that even with the relatively basic programming skills taught in this book, you will be able to create robust and helpful projects for the home and business. If you are interested in learning more about programming in VBA with Excel, I suggest looking into the .Net languages and how you can use them to create Office applications. You may also want to increase your use of the Windows...

Creating Your Own VBA Functions

The basic syntax for creating a function procedure in VBA is as follows Private Public Function FunctionName(paramter list) as type 'Function procedure code is listed here FunctionName Return value End Function This is similar to the syntax for any procedure with the procedure name, parameter list, and an End statement. You can, and should include a Private or Public keyword to define the scope of the function. One obvious difference is the Function keyword replaces Sub. Also, you should define...

Adding the ActiveX Controls

The program design calls for one Command Button and five Option Button controls. You should be quite familiar with the Command Button control. The Option Button control is similar to a Check Box except that a user can only select one button from a group. A group of Option Button controls is defined by their container in this case, a worksheet. It does not matter how many Option Button controls I add to a worksheet, the user will only be able to select one. Table 4.3 shows selected properties of...

VBA and Obj EctOriENtED Pro GramminG

If VBA is your first programming language, then chances are you have not heard of object-oriented programming. Don't worry if you haven't heard of it VBA does not qualify as an object-oriented language. There are some technicalities that disqualify VBA from calling itself object-oriented, but VBA still shares many of the same concepts as genuine object-oriented languages. Mainly, object-oriented languages and VBA commonly share the existence of objects and some of the tools used to manipulate...

Looping with VBA

Program looping is the repetition of a block of code a specified number of times. The number of times the block of code is repeated may be well defined or based on a conditional statement. All computer languages contain looping structures because these structures are excellent at solving problems that would otherwise require repetitive code. Imagine a program whose function it is to search for a specific name in a column of data with one hundred entries. A program with one hundred If Then...

Error Handung Debugging And Basic FiLe IO

The ability to read and write data to a computer's disk drives is fundamental to most programming languages. This chapter examines some of the different tools available in VBA and Excel that allow a programmer to write code for viewing a computer's file structure, and to read and write text files. Additional tools required for error handling and debugging your VBA programs are also discussed. Specifically, this chapter will cover

Designing the Math Game

As far as a student is concerned, the program interface doesn't change much from the one in the Chapter 4 program. The Math Game worksheet still contains the test problems, the timer, and the scored results. The number and type of ActiveX controls is the part that's different. Additional worksheets contained in the project are not meant to be viewed by a student, so hiding them would be a good idea. The other two worksheets must contain lists of test problems, students, and results. I will use...

Chapter Project Word Find

The Word Find project is an Excel VBA program that creates word search puzzles. Words for a puzzle are associated with a topic that the program uses to sort the data. The topics and words used in a puzzle are stored in a random access file. The file containing the words and topics is accessed and displayed by the program. New words and topics for puzzles can be added to the file by the user. A puzzle is created when the user selects individual words and places them within a fifteen by fifteen...

Writing the Code for the Userform Module

The program's data is stored in a random access file. The advantage to this type of file is that it can be quickly updated as long as your program correctly tracks the record number. In order to do this, a custom data type is required to ensure each record uses the same amount of memory. The custom data type named PuzzleList is built from three elements a long integer and two strings. The long integer is an identification number (IDNum), and as you will see, I use it to make finding specific...

Introduction

Visual Basic for Applications (VBA for short) is a programming environment designed to work with Microsoft's Office applications (Word, Excel, Access, and PowerPoint). Components in each application (for example, worksheets or documents) are exposed as objects to the programmer to use and manipulate to a desired end. Almost anything you can do through the normal use of the Office application can also be automated through programming. VBA is a complete programming language, but you can't use it...

Whats on the CDROM

The CD that accompanies this book includes the following The source code for the longer sample programs and the chapter projects discussed in the book, including all supporting image and sound files Audacity, an open-source audio editor The GIMP for Windows, a photo retouching and image composition program POV-Ray, a tool for creating high-quality three-dimensional graphics SawCutter, a tool for designing sounds cEdit Professional, an advanced, alternative text editor and IDE

Validating Selections

Custom validation procedures should test the user's and computer's ship and target selections for proper location and size. Because there are several validation procedures, I have placed them in their own standard code module. This is not really necessary as all of the remaining code could have been included with the object module for the Worksheet object however, the code in the object module was getting a bit long and more difficult to navigate, so a new module was added and named Validation...

The Watch Window

Besides the Immediate window, another useful tool for debugging VBA programs is the Watch window. The Watch window makes it possible to track the value of a variable or expression (property, function call, and so on) from anywhere in a program. Add a watch to an expression from the Debug menu or right click the expression and choose Add Watch from the shortcut menu. The resulting dialog box is shown in Figure 7.7. Choose either a specific procedure containing the expression you want to watch,...

Requirements for the Alienated Game

From the user's point of view, the Alienated Game is quite simple because all they have to do is select data markers on a chart. From your point of view, I'm betting the game is more of a challenge especially if you're not that comfortable with charts. If your comfort level is low, that provides all the more reason to spend ample time planning the program. The following list contains my requirements for the Alienated Game. 1. The game interface shall consist of a bubble chart created on a chart...

Function Procedures

Function procedures are very much like other procedures with one significant difference they return a value to the calling procedure. Now you might be concerned or confused by the fact that I used the term functions back in Chapter 2 in reference to Excel's spreadsheet functions and VBA's string and date functions. So, what's the difference between these two terminologies There is no difference. Everything I have, or will call a function is essentially the same thing. A function is a small...

M

Macro language support, disabling, 3 Macro projects, digital signing of, 4 chart types and, 391 starting, 127 Macros attaching to control, from Forms toolbar, 133 defined, 11 naming and storing, 128 recording in Math Game program, 126-132 selecting, 10 Macro security settings, changing in Excel, 3-4 Magic Squares, 34-37 Main() sub procedure, 62, 403 Map argument, 347 Mapping images, in Alienated Games program, 399-401 Masked cells, marking in Excetris program, 461 Massachusetts Institute of...

O

Object Browser, 150, 155-160, 163, 164, 171, 205, 226 FileSystem object and, 288 opening, 155 Object data type, 175, 205 Object definitions, 151 Object library, 156 Object Model chart, 157 Object module, 28, 211 Object-oriented programming, VBA and, 150-151 Objects, 149 defined, 151-153 working with, 173-177 Office library, 156 Office programs, security levels for, 3 OLEObjects collection, 436-438 OLEObjects collection object, 184, 471 OnAction property, of Shape object, 435 One-dimensional...

N

Named argument operator ( ), 122 Named arguments, 122 Name property, 15 changing, 13 of Command Button control, 18 Frame control and, 218 Option Button control and, 216 RefEdit control and, 222 of Scroll Bar control, 218 Xml Map object and, 346 Names constant, 40 variable, 27 Namespaces, 336 NeedShuffle() procedure, playing a hand of Blackjack and, 253, 254, 256, 260 Nested loops, 106 Nesting, 164 of XML tags, 335 Nesting functions, 47, 52 NewActiveRange() function, 451, 452, 453...

Requirements for Battlecell

Even if you never played Battleship as a kid, you are probably familiar with the game. Either your siblings played it, or your friends, or perhaps your own children play it now. You can also find several versions of the game on the Internet and in many department stores. If you are familiar with the game, you might think that defining a list of requirements for the game to be a pretty easy task, but this is not necessarily the case as this program will be considerably more complex than anything...

Requirements for Excetris

My idea is to create a game modeled after the original Tetris with an emphasis on programming Shape objects in Excel. The game's interface will once again be constructed from a worksheet. A specific range on a worksheet provides the game board and the game pieces are constructed out of Shape objects (AutoShapes of type msoShapeRectangle). The program tallies a score based on the number of shapes removed from the game board and assigns bonus points when multiple rows are removed as a result of...

G

Game board, resetting in Poker Dice program, 82-84 GameOver() sub procedure, 448, 470 playing a hand of Blackjack and, 255, 260, 263 General declarations section, of module, 30 GetAllRecords() sub procedure, 303, 305 GetCCArray() function, 457 GetChartSheets() sub procedure, 380, 381 GetDayOfWeek() function, 236 GetEmbeddedChartObjects() sub procedure, 383 GetFile() sub procedure, 303, 305 GetIDNum() function, 307 GetOperands() sub procedure, 140, 143 GetOperatorType() procedure, 139...

Project Revisiting the Math Game

In Chapter 4 I introduced you to the Math Game program which used a worksheet interface to quiz a student's elementary math skills. In this chapter, I will discuss enhancements to the Math Game program that rely on data from XML files. Enhancements to the Math Game program include the following The ability to use prewritten tests read from an XML file instead of generating problems for a test randomly. The ability to track students' test scores and automatically increase the difficulty level of...

Loops and Arrays

In Chapter 3, Procedures and Conditions, you started building your programming foundation with the branching structures If Then Else and Select Case. In this chapter, you will significantly expand on that foundation by learning looping code structures and arrays. Loops and arrays are fundamental to all programming languages they expand the capabilities of a program significantly and make them easier to write. You'll begin this chapter by looking at the different looping structures available in...

Resetting the Game Board

Before a user can play a game of Poker Dice, he or she must reset the game board by clearing the dice, check marks, and text. I handle the resetting of the game board with the procedures ToggleControls() and the Click() event of the Command Button control named cmdNewGame. These procedures are fairly straightforward. The ToggleControls() sub procedure is passed a Boolean parameter that is used to enable or disable all of the Check Box and Image controls on the game board. Set the Enabled...

Designing Blackjack

This project uses many of the tools discussed in previous chapters of this book, including various code structures and common ActiveX controls. In particular, the project includes additional tools discussed in this chapter. These tools include UserForms and their code modules, along with Frame, and Combo Box controls. The Blackjack game runs from a VBA form that contains several ActiveX controls. The form is separated into a Dealer area and a Player area using Frame controls. The dealer frame...

Using the Cells Property

The Cells property returns a Range object containing all (no indices used) or one (row and column indices are specified) of the cells in the active worksheet. When returning all of the cells in a worksheet, you should only use the Cells property with the Application and Worksheet objects, as it would be redundant, and thus confusing, to use it with the Range object. For example, returns cells A1 through A10, thus making the use of the Cells property unnecessary. The Cells property will fail...

Capturing User Selections

In order to know what image the user has selected, the program must identify the specific data series and data point whose marker displays the selected image. As discussed previously, selecting a chart component triggers the Select() event of the Chart object. To identify specific components requires testing the ElementID, Argl, and Arg2 arguments that Excel passes to the Select() event. The Alienated Game will take advantage of the Select() event procedure for identifying user-selected images.

Challenges

Create a program in VBA that adds several lines, rectangles, ovals, and triangles to a worksheet. Use a looping code structure. 2. Create a VBA program that creates a ShapeRange collection object from just the ovals in the drawing layer of a worksheet. Then alter the appearance of the ovals by adding a fill color. 3. Using a For Each loop in a VBA procedure, select just the rectangles created in the first challenge and align them to column C in the worksheet. Use the Left property of the Range...

Collecting Answers

The Change() event of a worksheet triggers when the content of cells on the worksheet are changed by the user. In the Math Game program, this event will trigger every time the user enters an answer. Excel passes the altered cell's range to the Change() event via the Target parameter. The user's answers are entered into the merged range L8 M9 defined with the name Answer therefore, the value of the Target parameter will be L 8. If the user has entered the answer in the correct cell, a series of...

Coding Battlecell

As requirement lists get longer and designs more complex, so do programs. Battlecell will have to be written in multiple code modules. The Workbook object's code module contains the event procedures necessary for handling open, close, and window resizing events. The Worksheet object's code module will also be required for its SelectionChange() event as well as the Click() event of the Command Button control. Since a worksheet serves the program interface, much of the program will be contained...

Coding the Math Game Program

Much of the code for the Math Game involves objects and methods discussed in previous chapters. At this point you are familiar with many of the structures and common objects used in Excel-VBA programs. New topics will usually come in the form of a new object and its associated methods and subordinate objects. Now, the greatest challenge for you is designing programs and developing algorithms. Since I am using three worksheets for the program interface, I will try to isolate the code that serves...

Designing Battlecell

As stated in previous chapters, when designing a program, you need to consider the user interface and all inputs and outputs required by the program. The Battlecell program interface shown in Figure 5.16 is fairly simple, consisting of two 10 by 10 grids in which the user and computer must place their ships. The user will input ship and target selections via mouse clicks on worksheet cells. Clicking the Command Button control will initiate the game and an area of merged cells will serve as a...

Do Loops

Do loops will execute a given block of code repetitively based on the value of a conditional expression. All Do-Loops require the keywords Do and Loop, plus one additional keyword (While or Until) depending on the desired action. The keywords are used to build four basic representations of the Do-Loop. The first two representations use the keyword Until with a conditional statement that determines if, and how many times the code inside the loop executes. With the conditional statement at the...

Input Validation

Trusting that a user will input the type of data required by your program is a leap of faith. You can, and should, provide hints to the user indicating the type of data and format your program requires however, you should also include code in your program to check what the user enters against a required format. The process of checking user input for accuracy is known as validation. Validation should be included whenever input is required from the user and the format of that input cannot be...

Maintaining the Student List and Viewing Results

After a test is completed and scored, the results are added to the Students worksheet and the XML file is automatically updated (discussed later). Results can be viewed from the Students worksheet, where the XML list that holds all test results has been created in columns I through K. Although there is no need to allow the user to export the results, they are allowed to clear the data from the XML file. Users may clear the list in the worksheet manually or by clicking the Command Button control...

Table Members of the File System Object

ChDrive Changes the current drive. CurDir Returns the current directory path. Dir Returns the name of a file, directory, or folder that matches a pattern, file attribute, or the volume label of a drive. FileAttr The mode used to open a file with the Open statement. FileCopy Copies a file from a source path to a destination path. FileDateTime Returns the date and time that a file was created or last modified. FileLen Returns the length of a file in bytes....

VBA Procedures

I briefly discussed programming modules in Chapter 2. You may remember that a module is a segment of your project that contains a related set of declarations and procedures. You may also remember that every module has its own window within the VBA IDE and, depending on whether or not it is an object module or a standard module, slightly different behavior regarding variables. Programming procedures can be constructed within each of these module windows if they are not already defined. Let's...

VBA Conection Objects

Collection objects in VBA are fairly straightforward they are exactly what the name implies a group or collection of the same object types. Referring to the bicycle example again, consider a collection of bicycles. The bicycle objects in your bicycle collection can be different sizes, colors, and types, but they are all bicycles. Collection objects allow you to work with objects as a group rather than just working with a single object. In VBA, collection objects are typically denoted with the...

ExceL Charts

Charts are valuable tools for data analysis and presentation in Excel or any other spreadsheet application. Unfortunately, the learning curve for creating charts is typically a bit longer and steeper than for other spreadsheet components. This is also true with regard to programming charts in Excel because the Chart object is a rather substantial component of the Excel object model. Before attempting to program with Excel's Chart object, a good understanding of the common chart types and their...

XML Tags Are Case Sensitive

In HTML, you are allowed to mix uppercase or lowercase characters. For example, HTML doesn't care if you open the body of a document with < BODY> and close it with < body> . In XML, opening and closing tags must be written in the same case. The tag pair < TEST> < test> is illegal in XML and must be written as < test> < test> or < TEST> < TEST> or some other combination where the case of each letter in the opening and closing tags match exactly. Although not...

Requirements for Blackjack

Because of my familiarity with the game, the requirement list for the Blackjack game was relatively easy to compile. Due to project length, I did not add many of the rules normally found in Blackjack such as doubling down, splitting, insurance for dealer blackjack, and so on. If you are unfamiliar with these features, you can find descriptions in the challenges at the end of the chapter. It would be great practice for you to add some of these features to the game. The requirements for the...

Program Outline

When playing a game, the Alienated Game should proceed as outlined in the following 1. The user initiates a new game with a click of the form button drawn on the chart sheet interface. 2. The chart sheet and ImageMap worksheet containing the image map are initialized for a new game. 3. The data is added to the chart as ten distinct series. 4. Data markers are filled with images using the image map contained in the ImageMap worksheet. 5. The image map is scanned for score sequences. If score...

Second Edition

This wri shows that it' possible to teach newcomers a programming language and good programming practices without being boring Lou owuo. This wri shows that it' possible to teach newcomers a programming language and good programming practices without being boring Lou owuo. Microsoft Excel VBA Programming for the Absolute Beginner Professional Trade Reference 2005 by Thomson Course Technology PTR. All rights reserved. No part of this book may be reproduced or transmitted in any form or by any...

Private Public and Procedure Scope

The Private and Public keywords used with procedure definitions have a similar function to that used with variable declarations. Private and Public are used to define the procedure's scope. The Public keyword makes the procedure visible to all other procedures in all modules in the project. The Private keyword ensures that the procedure is visible to other procedures within the same module, but keeps it inaccessible to all other procedures outside the module in which it is defined. The Private...

The Check Box Control

The Check Box control is a familiar and relatively easy control to use. Figure 3.10 shows the icon for the Check Box control. Check Box controls are designed to give the user multiple selections from a group. Use the Option Button control if you wish to Limit the user to only one choice. Table 3.7 lists the most important properties of the Check Box control. Table 3.7 Selected Properties of the Check Box Control Name Used for referencing the control in your program Caption Displays text that...

Playing the Game

The game is played by searching the chart for two adjacent images in a single row or column that can be swapped in order to create a score sequence. The user selects an image by first selecting a series and then selecting a specific point within that series that is, it takes two single clicks to select a single point if a series has not already been selected. When the user selects an image (or any chart component), it triggers the Select() event of the Chart object. This is where I have entered...

VBA Shapes

VBA shapes refer to those objects added to a document or worksheet from the Drawing toolbar in the application. This includes AutoShapes, freeforms, images, and text. The Drawing toolbar is common to most Microsoft Office applications, so programming its components only differs in terms of the document to which its shapes are added (for example, an Excel worksheet, Word document, or a PowerPoint slide). These topics are specifically discussed in this chapter The Shapes collection and Shape...

Custom Dialog for Quick Stats

Premium Vba Controls For Developer

The following example of a custom dialog is built from a UserForm object and several ActiveX controls. The form window allows a user to quickly select a worksheet range and calculate some basic statistics. Furthermore, the form allows the user to summarize their work by writing the statistics for each selected range to a List Box control for later review. Figures 6.12 and 6.13 show the two pages of the MultiPage control used in the form's design. MultiPage control -RefEdit control -Label...

Who ShouLd Read This Book

The goal of this book is to help you learn VBA programming with Excel. No prior programming experience is required or expected. Although you do not have to be an Excel user, you must have a good understanding of the basic tools involved in using any spreadsheet application. This includes a basic understanding of ranges and cell references, formulas, built-in functions, and charts. I ask my students at the start of every semester if they know how to use Excel. At least 90 percent of them say...

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

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

Sus Calculation Spreadsheet

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

The With End With Structure

VBA includes a programming structure designed to reduce the number of object qualifiers required in your code. Although the With End With structure discussed in this section is not required under any circumstances, its use is often recommended because it makes your programs more readable. Also you will often see the With End With structure in recorded macros. Consider the following code Range(A1 D1).Select With Selection.Font .Bold True .Name Arial .Size 18 End With .HorizontalAlignment...

Chart Sheets

Vba Multiplication Table Programming

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

Adding a Form to a Project

To add a form to a project, select Insert UserForm from the menu bar in the VBA IDE as shown in Figure 6.2. Inserting a VBA form into a project from the VBA IDE. Inserting a VBA form into a project from the VBA IDE. A new folder labeled Forms will appear in the Project Explorer window. An example of a form just added to a project is shown in Figure 6.3. m Microsoft Visual Buh PERSONA .,XLS The Forms folder in the Project Explorer m Microsoft Visual Buh PERSONA .,XLS The Forms folder in the...

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

Defining Custom Data Types in VBA

As is the case in any programming language, custom data types in VBA are derived from existing data types. A custom data type is a collection of related elements, possibly of different types, having a single name assigned to them. Consider an application that is required to store and retrieve information about customers in a database. The database contains information that includes a customer's identification number, name, age, gender, and address. Certainly you could declare five separate...

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