Super-Easy Guide to the Microsoft Office Excel 2003 Object Model

Article
07/11/2006

Frank C. Rice
Microsoft Corporation

August 2003

Applies to:
Microsoft® Office Excel 2003

Summary: Learn how easy it is to use the Excel object model to customize the way you work with Excel. Lessons include creating subroutines, the basics of objects, methods, and properties, and using dialog boxes. (29 printed pages)

Introduction
Why Should I Learn How to Use the Excel Object Model?
Using This Guide
What You Need to Know Before You Get Started
What You'll Know When You Finish the Lessons
Setting Up
Lesson 1: Getting Started
Lesson 2:Programming Concepts: Sub. . . End Sub and Procedures
Lesson 3: Programming Concepts: Objects, Methods, and Properties
Lesson 4: Real-World Example #1
Lesson 5: Creating Your Own Dialog Boxes
Lesson 6: Programming Concept: Collections
Lesson 7: Programming Concept: If This, Then That
Lesson 8: Real World Example #2
Where to Go from Here
Appendix A: Hands-on Challenge Answers

Introduction

Customizing Microsoft® Office Excel 2003 with the Excel object model is easy. Really easy. You don't need an advanced degree in computer science. You don't need to know C or C++ or any other programming language, for that matter. You don't need to know anything about object models.

To make the point, look at the following lines of code.

Sub ColorEverySecondRow()
    Const Gray = 15
    Range("A2").EntireRow.Select
    Do While ActiveCell.Value <> ""
       Selection.Interior.ColorIndex = Gray
       ActiveCell.Offset(2,0).EntireRow.Select
    Loop
End Sub

Can you figure out what this code does in Excel?

With these few lines of code, you can add shading to every other populated row in the active worksheet. ColorEverySecondRow begins by declaring a constant named gray that holds the value 15. The meaning of this constant will become clearer shortly. Next, row 2 is selected. When the entire row is selected, the left-most cell in Column A automatically becomes the active cell. The Do...Loop statements repeat while the active cell is not empty. In the loop, the code sets the interior color of the selected cell to 15, which is the value of the constant Gray. Next the code selects the entire row, two rows down from the active cell. This continues until the active cell is empty and the While condition is no longer true and the loop terminates.

So now you can see that in just six lines of code, you can quickly add color to your worksheets to make the sheet more readable.

Why Should I Learn How to Use the Excel Object Model?

The Excel object model allows you to customize Excel to suit your specific needs. The Excel object model is ideal when you need additional functionality in Excel and you need it right away.

For example, a department head in your company laments that working with paper expense reports is a huge bottleneck in processing trip reports from the sales representatives. You know, however, that you can quickly recreate the expense report in Excel and then map the report to an XML schema. This allows sales representatives to complete the reports on their laptop computers and submit them from the field.

Using This Guide

This guide is organized into eight lessons. Each lesson is hands-on; you practice the lessons with Excel as you read along. This guide is best used not as bed-time reading but as be-in-front-of-your-computer-and-try-it reading.

What You Need to Know Before You Get Started

All you need is a working knowledge of Microsoft® Windows® operating systems and a familiarity with Microsoft Excel. That's it.

If you already have programming experience, you can probably just glance at the "Programming Technique" sections, but the rest of the material may still be useful to you.

What You'll Know When You Finish the Lessons

After going through this guide and doing all of the examples and exercises, you can develop applications using the Excel object model. You will also be able to discover on your own how to find the right tools in the Excel object model to solve a given problem. Lastly, you will read about some key programming concepts, and gain a working knowledge of the Microsoft Visual Basic® programming language. To summarize, with the help of this guide, you will be able to apply the Excel object model to meet your organization's needs.

Setting Up

To use this guide and go through the included examples, you need to have Microsoft Office Excel 2003 (Beta 2 or later). You do not need any special development tools to use the Excel object model.

Lesson 1: Getting Started

We'll be using Microsoft Visual Basic for Applications (VBA) for all lessons in this guide. VBA is a version of Microsoft Visual Basic integrated into Microsoft Office applications, including Microsoft Excel. Solutions that you create in VBA are also called macros. A macro is a series of instructions in Visual Basic that perform something useful. When you write macros to perform tasks in Excel, you write Visual Basic instructions that use the Excel object model. In Excel you can create a macro either by writing it or by recording it.

To start writing a macro:

Start Microsoft Office Excel 2003.
On the Tools menu, point to Macro and then click Macros.
Name your macro. Tip: A macro name can't have a space in it, so type MyMacro.
Click Create.

Excel automatically starts the Visual Basic Editor, as shown in the following illustration:

Figure 1. Default view of the Visual Basic Editor

By default, the Visual Basic Editor displays three windows:

Code window The window labeled "Book1 - Module1 (Code)." This is where you write your code.
Project Explorer window The window labeled "Project - VBAProject." This window shows all the Excel objects and modules available to you. Don't worry if you don't know what Excel objects are yet. That'll come later. This window allows you to easily view and manage any number of VBA files.
Properties window The window labeled "Properties - Module1." This window displays the current set of properties for the selected item. Currently, the selected item is Module1.

Now let's use each of these three windows:

"Module1" is such a dry and impersonal name, don't you think? Let's change it. In the Properties window, go to the area where it says "(Name) Module." Double-click the text "Module1" and then type "MyFirstModule." Notice that the name changed in the Project window and in the title bar of the Module window.
Now in the Project Explorer window, click the PLUS SIGN (+) next to Microsoft Office Excel Objects to expand the node and see what's inside. Currently, there are four objects: Sheet1, Sheet2, Sheet3, and ThisWorkbook. It's from the ThisWorkbook object that most of the action happens.

View the Code window for your module, which you just renamed "MyFirstModule." Tip: To open it, double-click the name, "MyFirstModule" in the Project Explorer Window.

To complete the macro:

Now let's enter some code in the MyMacro routine.
In the Code window, type the following:
```
Sub MyMacro()
   Dim wrkSheet As Worksheet

   Set wrkSheet = ActiveWorkbook.Worksheets.Add
   Range("B1").Value = "My New Sheet"
End Sub
```
You don't have to know how or why this code works. You'll learn that in future lessons. Now let's see it in action!
First, close the Visual Basic Editor. To do so, on the File menu, click Close and Return to Microsoft Excel.
In Excel, on the Tools menu point to Macro and then click Macros.
The Macros dialog box opens with MyMacro already selected. Click Run.

Did you see that a new worksheet was created with My New Sheet in cell B1? Congratulations! You are now an official Excel object model programmer.

**Note **If an error dialog came up, no big deal. Click the button. Make sure that the code looks exactly as is shown above and repeat steps 2-4 again.

In addition to creating macros manually by typing code into the Visual Basic Editor, you can also create a macro by using the Macro Recorder. With the recorder, Excel creates the macro by recording the menu commands, keystrokes, and other actions needed to accomplish a task.

The process for recording a macro consists of three steps. First, start the macro recorder and give the macro a name. Then, perform the actions that you want recorded. Lastly, stop the recorder.

To start recording a macro:

To demonstrate this process, let's create a simple macro that inserts the company name and address in a worksheet. Begin by saving and closing all workbooks and then open a new workbook.

On the Tools menu, point to Macro, and then click Record New Macro. Excel displays the Record Macro dialog box.

Figure 2. View of the Record Macro dialog box
In the Macro name box, type a name for the macro such as CompanyAddress.
In the Shortcut key box, type a key combination to run the macro. For example, type A.
In the Store macro in box, choose This Workbook.
In the Description box, type a description for the. For example, type Type company address.
To begin recording, click OK. Excel displays the message, Recording, in the status bar and the Stop Recording toolbar.

Figure 3. The Stop Recording toolbar
Click A1 and then type Northwind Traders. In A2, type 1234 West Anywhere Place. In A3, type Redmond, WA 98052.
Click the Stop Recording button.
To test the new macro, clear the worksheet, and then press Ctrl+Shift+A. Excel runs the macro and populates the worksheet by performing to the steps you recorded.

About Security

Excel checks whether or not you have any macros when you start Excel. This is why you may see the following dialog box when you start Excel:

Figure 4. Macro Security dialog box

When you see this dialog box, click the Enable Macros button. This allows Excel to run the macros you create. Although not recommended for your non-training workbooks, you can also choose to avoid this dialog box by reducing the level of security on your macros. To do so, on the Tools menu, point to Macros and then click Security. Select the Medium (or Low) security option and click OK.

Important By lowering your Macro Security setting, you are not protected from unsafe macros. Use this setting only if you have virus scanning software installed or you have checked the safety of all documents you open.

Hands-on Challenge #1 (For answers, see Appendix A)

Edit your MyMacro macro in the Visual Basic Editor and change the code to display your name in cell A2.

Lesson 2: Programming Concepts: Sub. . . End Sub and Procedures

So now that you've had a taste of writing code, it's time to learn a bit more about what you are getting into. Let's take a close look at the code you just wrote:

Sub MyMacro()
   Dim wrkSheet As Worksheet
   
   Set wrkSheet = ActiveWorkbook.Worksheets.Add
   Range("B1").Value = "My New Sheet"
End Sub

The key words Sub...End Sub are used to begin and end a macro, following the pattern shown below:

Sub AnyNameHere()
   Some of that cool object model code here
End Sub

AnyNameHere is the name of the macro or procedure. A procedure is a small set of code statements that you create that does something. MyMacro is an example of a procedure. A procedure doesn't have to be a macro, however. You can create a procedure and then call that same procedure from another procedure. For example, in your sample Excel spreadsheet, create a second macro called MyOtherMacro and add the following:

Sub MyOtherMacro()
   MyMacro
End Sub

If you run MyOtherMacro, the procedure MyMacro runs and creates an additional worksheet:

Sub MyMacro()
   Dim wrkSheet As Worksheet
   
   Set wrkSheet = ActiveWorkbook.Worksheets.Add
   Range("B1").Value = "My New Sheet"
End Sub

So why would you want to do this? Creating separate procedures allows you to organize your code cleanly and it allows you to do common procedures easily. For example, say you needed to create and display three different worksheets. You could do that by changing the code in MyOtherMacro as follows:

Sub MyOtherMacro()
   MyMacro
   MyMacro
   MyMacro
End Sub

Hands-on Challenge #2

Try adding one line to the MyOtherMacro code to add another worksheet and insert the message "This is easy" into cell C1.

Lesson 3: Programming Concepts: Objects, Methods, and Properties

At some point, you may have heard some of the commotion over object-oriented programming. Object oriented programming is the key concept behind such languages as C++ and C#. What you probably didn't know, however, is that just by finishing the first two lessons, you can now call yourself an object oriented programmer!

That's right. The Excel object model uses object-oriented programming. Fortunately, to use and understand the Excel object model, you don't need to take a class in the subject. To gain a working knowledge of the Excel object model, you only need to know and understand three concepts:

Concept	Description	Example
Object	A "thing"	Worksheet
Method	Something a "thing" can do	Add a "thing"
Property	A characteristic of a "thing"	Name

You can categorize everyday things into objects, methods, and properties. For example, consider a car to be an object. A Car object has methods or various things it can do, such as Drive, Start, Turn Left, and Turn Right. A Car also has properties that describe it: the color is red and the number of headlights is two.

Figure 5. A car

Let's take a closer look at the code that you already wrote and identify the objects, methods, and properties:

   Set wrkSheet = ActiveWorkbook.Worksheets.Add
   Range("B1").Value = "My New Sheet"

There are three objects in this code. The first is wrkSheet which represents a Worksheet object. The second is the ActiveWorkbook object which represents the active workbook. The third is the Range object representing a range of cells in this worksheet. It's easy to visualize these as "things." Incidentally, the Excel object model is simply a list of objects that we can use to program Excel.

Whenever you first use an object, you begin with the Set key word. Objects take up memory in the computer; the Set key word allocates the memory required for an object.

You can give objects virtually any name you want. In the above example, I gave the Worksheet object the name, wrkSheet**,** but you can change the name to suit your mood. Excel created the object, ThisWorkbook for us. You can see this object listed in the Project window under Microsoft Office Excel Objects.

The MyMacro macro contains one *method,*Add. A method is always associated with an object. In this case, the Add method is associated with the object, wrkSheet. To use a method, you simply place a period in between the object and the method. For example, Worksheets.Add. As described above, a method is "Something a thing can do." In the example you wrote, a new worksheet is added to the ActiveWorkbook object.

The MyFirstMacro code contains one property, Value. Like a method, a property is always associated with an object. In this case, Value is associated with the object, Range. To use a property, you place a period between the object and the property. For example, Range.RowHeight.

Once again, think of a property as "a characteristic of a thing." For instance, the ColumnWidth is a characteristic of a range item. Another example: The Name is also a characteristic of a Range object.

One last thing to know: Every object is a specific type of object. Each type of object has its own set of methods and properties. In the previous example, wrkSheet is a Worksheet object. Worksheets have methods, such as Visible, and properties, such as Count, that other types of objects may not have. In addition, other types of objects may have methods or properties which the Worksheet object does not have. For example, the following instruction:

Worksheet.Value = "Hello"

Does not work because objects of type Worksheet do not have a property called Value.

Hands-on Challenge #3

Look at the following fictitious example and determine which parts are objects, methods, and properties. Hint: There is three of each:

Set PetStore = ShoppingMall.GetStore(aPetStore)
PetStore.OpeningTime = 9 AM
Set Dog = PetStore.GetPet(aDog)
Dog.Breed = "Cocker Spaniel"
Dog.Color = "Blond"
Dog.WagTail

Lesson 4: Real-World Example #1

Suppose that your boss wants to distribute a time sheet to each of your team members on a daily basis. To do this, she asked you to create a program that creates a simple Excel template that she can send to employees electronically.

Using some of the knowledge that you already gained, let's jump right in and create the macro:

To create the TimeSheet macro:

Create a module called TimeSheet. To do so, in Excel, on the Tools menu, point to Macro, and then click Macros. Type TimeSheets and then click Create. The Visual Basic Editor starts with a new TimeSheet macro ready to fill in.
In the Properties window, double-click the (Name) box and type TimeSheets.

In the Code window, type the following code:

Sub TimeSheet()
   Dim wrkSheet As Worksheet

   Set wrkSheet = ActiveWorkbook.Worksheets.Add
   Range("A1").Value = "Department"
   Range("B2").Value = "Employees Name"
   Range("D2").Value = "Day of the Week
   Range("B4").Value = "Regular Hours
   Range("B5").Value = "Sick Time
   Range("B6").Value = "Vacation Hours
   Range("B7").Value = "Overtime Hours
   Range("B9").Value = "Totals
End Sub

Click Save, type a name for your sample file such as Sample2, and then click Save. This saves the Excel file.
Run your macro to see it in action. The macro creates a time sheet template that you can send to each employee by using e-mail.
That's it. You're done! And to do this, we introduced no additional object or commands. We just expanded on the concepts we already discussed in the previous examples.

Using Events

Now you have a macro that creates a time sheet template that you can use as the basis to print time sheets for each employee. But you still have to actually run the macro for it to work. Now say, for example, that the first thing you do when you get to work in the morning is to start Excel. The last thing you do before you leave work is to quit Excel. Wouldn't it be convenient to have Excel automatically run your macro every time it starts? Then you could create the daily time sheet template every morning without doing any work at all!

You can do that by using an object event. An event occurs when something happens to an object. For example, when you click a button on a form, a Click event happens to the button object. When you start up Excel, an Auto_Open event happens.

To illustrate further, think back to the car analogy. You might want the Car object to do something when someone puts a key in the ignition. In this case, you would use a Car event called something like DriverPutKeyInIgnition. When that event gets called, you want the car engine to start and get ready to go. Of course there could be several events to which the car would respond. These events include turning the key in the ignition, shifting to a different gear, pressing on the gas pedal, and pressing the brake pedal. The car responds differently based on which event is triggered.

To run your macro each time Excel starts up:

Start up the Visual Basic Editor (if it isn't already open) in Excel. Tip: One way to do this is to press the ALT + F11 key combination.
Click on the PLUS SIGN (+) of the Microsoft Excel Object item in the Project Explorer Window. Among the items you should see an item under it labeled "ThisWorkbook."
Double-click on "ThisWorkbook". A code window appears with the title, "ThisWorkbook
Notice two drop-down lists at the top of the Code window. The first list, called the Object list, includes objects associated with ThisWorkbook. In the Object list, click Workbook.

Notice that the Visual Basic Editor creates the Sub Workbook_Open procedure for you automatically. This is the Open event container that runs automatically when you start Excel.
To call your macro, type the name of the macro in the procedure. For example:
```
Private Sub Workbook_Open()
    TimeSheet
End Sub
```

That's it! You're done! Save your macro, close the Visual Basic Editor, restart Excel, and then open your sample file to see if it worked.

Hands-on Challenge #4

Using the macro that contains the template for a weekly time sheet, (assuming you only shutdown Excel at the end of the day), have the macro called every time that you quit Excel.

Lesson 5: Creating Your Own Dialog Boxes

Now for one of the coolest parts of VBA—forms. A form is a dialog box-like object that you can create and design in VBA. You can add check boxes, text, pictures, and all other features to your form without writing a single line of code.

Why do you need forms? Sometimes your macro needs to communicate information to users or get information from users. For example, you may want to show a progress dialog box while your macro is running, or you may want users to specify specific options before your macro does its magic.

To demonstrate, let's go back to the TimeSheet macro you created in Lesson 4. The TimeSheet macro creates a time sheet template each time you open your sample file. But what if you start Excel at different times throughout the day? What if you go to the office on the weekend and start Excel? You certainly do not want to create a time sheet template five times a day. The solution is to present an option to create the template each time you start Excel.

To create a dialog box:

In Excel, on the Tools menu, point to Macro, and then click Macros. Let's create a new macro.
Type the name "TimeSheetPrompt" and click Create. This starts the Visual Basic Editor.
On the Insert menu, click UserForm.

Figure 6. A blank UserForm

Your form appears along with a toolbox of controls. Use the form to design the dialog box that the user will see and interact with.

Click the form to select it. Look at the Properties window. Change the (Name) property to "PromptDialog." Press Enter.
Change the Caption property to "Time sheets for co-workers?" Press Enter. Notice that the title of your form changes to match what you typed.
Hover over the different user controls in the toolbox and look at the ScreenTips to read descriptions of the controls.

Before we continue, let's consider for a moment what we'll need for our TimeSheetPrompt macro. We want to give the user a choice whether or not to create the time sheet. A simple way to do this is to add some explanatory text and then two buttons, "Create Time Sheet" and "Don't Create Time Sheet"
In the Toolbox, click Label (the component with the A).
In the PromptDialog window, click and drag the mouse to select the area where you want the explanatory text to appear. Tip: Don't worry, you can always move and resize it after you create it.

Note The label you just created is an object. It has methods and properties just like a worksheet or button.
Look at the Properties window for list of the properties available for the label. The first few you should see are (Name), Accelerator, and AutoSize.
Click the Caption box and type a new value for the Caption property, for example, "Do you want to create a time sheet?" and then press Enter. The Module window updates the label to show what you just typed.

Now you need to create your two buttons.
Click the form to show the Toolbox again.
Click the CommandButton button (the plain rectangle).
In the Time sheets for co-workers? window, click and drag the mouse to select the area where you want the explanatory text to appear.
To create a second button, click the CommandButton button in the Toolbox and drag another button onto the form.
Click the first button and set the following properties: Set the (Name) property to "Yes." Set the Caption property to "Create Time Sheets."
Click the next button and set the following properties: Set the (Name) property to "No." Set the Caption property to "Don't Create Time Sheets."

Figure 7. Completed dialog box

Making Your Dialog Box Work

So now you have this cool dialog box. But it doesn't do anything. To make it work, you need to add four lines of code. The tricky thing is that you need these lines in three places. Here's how you do it:

The first thing we want is for the dialog box to appear when the user starts Excel. In the Project window for ThisWorkbook, under Microsoft Office Excel Objects, double-click ThisWorkbook.

Go to the Workbook_Open event and type the line "PromptDialog.Show." The code should look like this:

Private Sub Workbook_Open()
   PromptDialog.Show
End Sub

Note PromptDialog is an object. Show is a method of that object.

Close ThisWorkbook code window. Open the PromptDialog form. To do so, double-click PromptDialog under Forms in the Project Explorer window.

Now we want the macro to perform specific tasks according to which button the user presses. Double-click the Create Time Sheet button.

The Code window opens for the PromptDialog macro. VBA creates the event that is called when the user presses the Create Time Sheet button. When the user presses the button, we want the macro to do two things. We want it to create the time sheet and to hide the dialog box. We assume you already created the TimeSheet macro from Lesson 4. The code should be as follows:

Private Sub Yes_Click()
    TimeSheet
    PromptDialog.Hide
End Sub

Note PromptDialog is the name you specified for the dialog box object in the Properties window. Hide is a method of that object.

Close the Code window. Now double-click the Don't Create Time Sheets button. In that procedure, you only want the macro to hide the dialog box. For example:

Private Sub No_Click()
    PromptDialog.Hide
End Sub

That's it! You're done! Save your changes, restart Excel and then open your sample file to re-run your macro and see the dialog box in action.

Lesson 6: Programming Concept: Collections

So far, you have learned about objects, properties, methods, and events. There is one last category of "things" you need to understand to use the Excel object model: collections.

A collection is a special type of object, an object that is a group of other objects. So, for example, if Car is an object, Cars is a collection, a collection of cars.

A collection can also be a property of another object. Continuing with the car example, Doors can be a property of a Car object and a collection of Door objects. Therefore, we can understand the relationship like this:

The Cars collection is a collection of Car objects. Each Car object has a property called Doors. The Doors collection is a collection of Door objects.

Collections in Excel

The Excel object model includes many types of collections. There is an example of one on the very first example of this guide:

   Set wrkSheet = ActiveWorkbook.Worksheets.Add

Worksheets is a collection of Worksheet objects. Worksheets is also a property of the ActiveWorkbook object. It's easy to see the usefulness of collections. A workbook can contain any number of worksheets. The Worksheets collection gives us an easy way to manage these sheets.

Other collection objects in Excel include:

Sheets A collection of all of the sheets in the parent workbook

Workbooks A collection of all open workbooks.

Charts A collection of chart sheets in a workbook.

Methods and Properties of Collections

All collections have methods and properties that allow you to access the individual objects in the collections. Three of the most important methods and properties are discussed here.

Count property This property indicates how many individual objects are in a collection. For example:

Dim numberOfWorksheets
numberOfWorksheets = ActiveWorkbook.Worksheets.Count

In this example, numberOfWorksheets is a variable. A variable is something we define to record information and use it later. In this example, the numberOfWorksheets variable is equal to the number of Worksheet objects in the ActiveWorkbook object.

Before we use a variable, we must define it by using the keyword Dim. We can name our variables anything we like. For example:

Dim myDogBitMe
myDogBitMe = ActiveWorkbook.Worksheets.Count

Note A collection can be empty, in which case the Count property is zero.

Item method This method accesses a specific object in a collection. For example:

Set myWorksheet = ActiveWorkbook.Worksheets.Item(2)

The number in the parenthesis indicates which worksheet you want your code to access. For this example, the second Worksheet object in the Worksheets collection is assigned to the variable myWorksheet.

You can also use a variable in the parenthesis. For example:

Dim numberOfWorksheets
numberOfWorksheets = ActiveWorkbook.Worksheets.Count
Set theLastWorksheet = ActiveWorkbook.Worksheets.Item(numberOfWorksheets)

Here, you first set the variable numberOfWorksheets to match the number of Worksheet objects in the Worksheets collection. Then you access the last worksheet. So if there are five worksheets, you can specify the last item by using the number 5. The previous line is equivalent to saying:

ActiveWorkbook.Worksheets.Item(5)

Add method This method allows you to add additional objects to a collection. For example:

Set anotherWorksheet = ActiveWorkbook.Worksheets.Add("Sheet6")

How you use the Add method varies depending on the collection to which you're adding an item. In most cases, similar to the previous example, you must specify a name for the new object such as Sheet6.

One more note about variables: Because a variable records information, its value only changes when you change it. As an analogy, think of a variable as an audio tape:

Let's say we create an audio tape called MyCurrentAge. On this audio tape, we record your current age. Now let's say we listen to the audio tape five years from now. What do you hear when you play back the audio tape? You hear, not your current age, but your age five years ago. This is an important concept and you can use it to answer the Hands-on Challenge #5 correctly.

Hands-on Challenge #5

Look at the following fictitious Visual Basic code and answer the questions that follow. Assume that at the start, the Papers collection is empty; its Count property is equal to zero. Tip Step through the code line by line and keep track of the numberOfPapers variable and the objects in the Papers collection.

Dim numberOfPapers
Set paper1 = MyDesk.Papers.Add("The Foofle Report")
Set paper2 = MyDesk.Papers.Add("The Mooble Report")
numberOfPapers= MyDesk.Papers.Count
Set paper3 = MyDesk.Papers.Add("The Garble Report")
Set paper4 = MyDesk.Papers.Item(currentNumber)
Set paper5 = MyDesk.Papers.Item(1)
Set paper6 = MyDesk.Papers.Item(currentNumber + 1)

What is the value of the variable, numberOfPapers?
What is the value of paper3?
What is the value of paper4?
What is the value of paper5?
What is the value of paper6?

Lesson 7: Programming Concept: If This, Then That

Sometimes we need to specify which code to run according to the state of things. For example, in the TimeSheet macro, it makes sense to create the time sheet daily, but only for the days of the week from Monday to Friday. Or we may want to create a macro that runs only for certain people or even for a specific number of people.

We can create procedures that respond to different conditions by using the If...Then control statement. The If...Then control statement is one of many Visual Basic tools that direct the flow of your code. The format of the If...Then control statement is as follows:

If <expression> Then
<code here>
End If

In the above code, <expression> represents something that can be True or False. <code here> represents the code that runs if <expression> is determined to be True.

The following table shows some examples of <expression>:

Expression	Description
`Papers.Count = 3`	Set to True if there are three Paper objects in the Papers collection.
`numEmployees > 0`	Set to True if the `numEmployees` variable is greater than 0.
`numEmployees <> 5`	Set to True if the `numEmployees` variable does not equal 5.

The following two examples help demonstrate how you can use the If...Then control statement:

Dim numberOfTimeSheets
numberOfTimeSheets = ActiveWorkbook.Worksheets.Count
If numberOfTimeSheets > 0 Then
Set myWorksheet = ActiveWorkbook.Worksheets.Item(numberOfTimeSheets)
MsgBox "The name of the last employee is " &_
 myWorksheet.Name & "."
End If
If numberOfTimeSheets = 0 Then
MsgBox "There are no time sheets in this workbook."
End If

In this example, a dialog box is displayed that indicates to the user either the name on the last employee's time sheet or that there are no time sheets in this workbook.

Note MsgBox is a Visual Basic statement used to display simple messages. You can cause a single string or any number of strings joined by a "&" sign to display using this statement. In the previous example, if the name of the last person on the time sheet is "Nancy Davolio" the user sees the following:

Figure 8. The informative message box

If. . .Then Example 2: If this option is checked, then. . .

Consider the following dialog box:

Figure 9. Dialog box with the option to save the timesheet

When the user clicks the OK button, our macro runs different code according to whether the user checked the check box labeled "No, just save it in my Drafts folder." If it's not checked, we'll print the time sheet. If it is checked, we'll save it so the user can finish it and print it later.

To understand how this works, first you need to know that a check box is an object you can draw right onto a form—similar to the button or label that you created in Lesson #5. Because it is an object, it has properties. One of those properties is called "Value." The Value property can either be True (checked) or False (not checked.)

The code should run when the user clicks the OK button. So we add the following code to the Click event of the OK button. In this example, the name of the form is PromptDialog, the name of the button is OK, and the name of the check box is justSaveDraft:

Private Sub OK_Click()
If PromptDialog.justSaveDraft.Value = True Then
   MyItem.Save
End If
If PromptDialog.justSaveDraft.Value = False Then
   MyItem.Print
End If
PromptDialog.Hide
End Sub

If the user selects the check box, Excel now saves the time sheet to the user's default file location. If the user does not select the check box, Excel prints a paper copy of the message.

Hands-on Challenge #6

Make an addition to the TimeSheet macro you created in lesson 5. In the dialog box, add a check box that allows the user to edit the time sheet before printing it. The check box can include a caption such as "Edit the time sheet before printing." If this is checked, the macro does not print the time sheet but creates the time sheet and displays it to the user.

Lesson 8: Real World Example #2

Suppose you're an assistant in the Human Resources Department. One part of your job is printing time sheets for different groups in the company. There are currently two groups for which you frequently have to print time sheets. Sometimes you'll want to print time sheets for one or for the other group.

How do you make this task super easy? By using the Excel object model to create a dialog box where you can specify the group that you want to print time sheets for.

Step 1: Create the Dialog Box

First, we need to create the dialog box that you can use to specify for which group to print time sheets and the number of time sheets for each department:

In the Visual Basic Editor, click UserForm on the Insert menu.
Add one label, two check boxes, and one button so the form looks similar to the following:

Figure 10. Dialog box with two options

Remember the Caption property sets the text the user sees in the checkboxes.
For the check boxes, the text boxes, the button, and the form itself, specify a description for the Name property such as the following:

Control Name

The form PrintTimesheets

OK Button cmdOK

Sales Dept Copy Counter Sales

Marketing Dept Copy Counter Mrktng
Bonus Item: For each check box and text box, specify the ControlTipText property to include a description of the control. For example, Type the number of copies for the text boxes.

Control	Name
The form	PrintTimesheets
OK Button	cmdOK
Sales Dept Copy Counter	Sales
Marketing Dept Copy Counter	Mrktng

Step 2: Make It Work

To make the dialog box work, you first must write code for the event that occurs when the user clicks the OK button that you added to the dialog box.

On the form in the Visual Basic Editor, double-click the OK button. Add the following code to the cmdOK_Click procedure:

Private Sub cmdOK_Click()
    Set wrkSheet = ActiveWorkbook.Worksheets.Add

    If Sales Then
       Range("B1").Value = "Sales"
       AddFields
    End If
    If Mrktng Then
       Range("B1").Value = "Marketing"
       AddFields
    End If
    
    PrintTimesheets.Hide
    
End Sub

This event procedure begins by adding a new worksheet to the existing workbook. Next, test to see if the SalesCnt text box on the dialog box contains a value. If it does, the macro displays the department name in cell B1. Then call the AddFields subroutine that we will discuss shortly.

Next, add the following line above the Private Sub cmdOK_Click() statement:

Public wrkSheet As Worksheet

This statement allocates memory storage for the worksheet that we use in the cmdOK_Click event procedure. If we don't designate memory for the worksheet, we won't be able to use it properly in your code.

Step 3: Finishing the Code

Finally, add the following subroutine after the End Sub statement of the cmdOK_Click event procedure. This procedure writes out the fields that make up the timesheet:

Sub AddFields()
   Range("A1").Value = "Department:"
   Range("B2").Value = "Employees Name:"
   Range("D2").Value = "Day of the Week:"
   Range("B4").Value = "Regular Hours"
   Range("B5").Value = "Sick Time"
   Range("B6").Value = "Vacation Hours"
   Range("B7").Value = "Overtime Hours"
   Range("B9").Value = "Totals"
End Sub

Where to Go from Here

Now that you are introduced to the Excel object model you can create custom Excel solutions. Of course, this guide only scratched the surface of the powerful things you can do using the Excel object model. Use the online Help to explore the many collections, objects, methods, and events at your disposal.

To use the online Help for Visual Basic for Applications, in the Microsoft Visual Basic Editor, on the View menu, click Object Browser.

Figure 11. Object Browser

To view tools specific to Excel, in the Project/Library drop-down list, click Excel. This shows a list of objects, collections, methods, and properties that apply to Excel. For example, in the Search Text box, type Worksheets and click Search (the set of binoculars).

The search results are displayed in the Search Results window. As you click an object, the window updates the list of objects and methods that you can apply to that object. In the bottom window pane, context-sensitive help provides details about the specified member.

Click the Worksheets object in the top window. Look at the members that are displayed in the Members of 'Worksheets' window. The symbol next to each item indicates the type of item such as whether it is a method or a property.

All the information you need is here. The context-sensitive help in the bottom window pane includes links so you can quickly navigate between related subjects. For example, you can use these links to identify the elements or methods of a specified object. Many of the context-sensitive help files in this window pane also include examples that you can learn from and use in your own code.

Congratulations on becoming an Excel object model programmer! Now you can start using the Excel object model to save your company time and money!

Appendix A: Hands-on Challenge Answers

Hands-on Challenge #1

Change the body of the message to your name.

Sub MyMacro()
   Dim wrkSheet As Worksheet
   
   Set wrkSheet = ActiveWorkbook.Worksheets.Add
   Range("A2").Value = "<your name here>"
End Sub

Hands-on Challenge #2

Edit the MyMacro using the Visual Basic Editor and change the code to look like the following:

Sub MyMacro()
   Dim wrkSheet As Worksheet
   
   Set wrkSheet = ActiveWorkbook.Worksheets.Add
   Range("B1").Value = "My New Sheet"
   Set wrkSheet = ActiveWorkbook.Worksheets.Add
   Range("C1").Value = "This is easy"
End Sub

Hands-on Challenge #3

Look at the following fictitious object model code and determine which parts are objects, methods, and properties. There is three of each:

Set PetStore = ShoppingMall.GetStore(aPetStore)
PetStore.OpeningTime = 9 AM
Set Dog = PetStore.GetPet(aDog)
Dog.Breed = "Cocker Spaniel"
Dog.Color = "Blond"
Dog.WagTail

Objects	Methods	Properties
ShoppingMall	GetStore	OpeningTime
PetStore	GetPet
Dog	WagTail	Color Breed

Hands-on Challenge #4

To create the macro that contains the template for a weekly time sheet, add the code such as the following:

Sub TimeSheet()

Dim wrkSheet As Worksheet

Set wrkSheet = ActiveWorkbook.Worksheets.Add

Range("A1").Value = "Department"

Range("B2").Value = "Employees Name"

Range("C3").Value = "Monday"

Range("D3").Value = "Tuesday"

Range("E3").Value = "Wednesday"

Range("F3").Value = "Thursday"

Range("G3").Value = "Friday"

Range("B4").Value = "Regular Hours"

Range("B5").Value = "Sick Time"

Range("B6").Value = "Vacation Hours"

Range("B7").Value = "Overtime Hours"

Range("B9").Value = "Totals"

End Sub

Next, because you want to run this each time that you quit Excel, call the TimeSheet macro during the Worksheet object's Deactivate event. Hint: To edit this event, use the ThisWorkbook Module window:

Private Sub Workbook_Deactivate()

TimeSheet

End Sub

Hands-on Challenge #5

Look at the following fictitious Visual Basic code and answer the questions that follow. Assume that at the start, the Papers collection is empty; its Count property is equal to zero. Hint: Step through the code line by line and keep track of the numberOfPapers variable and the objects in the Papers collection.

Dim numberOfPapers
Set paper1 = MyDesk.Papers.Add("The Foofle Report")
Set paper2 = MyDesk.Papers.Add("The Mooble Report")
currentNumber = MyDesk.Papers.Count
Set paper3 = MyDesk.Papers.Add("The Garble Report")
Set paper4 = MyDesk.Papers.Item(currentNumber)
Set paper5 = MyDesk.Papers.Item(1)
Set paper6 = MyDesk.Papers.Item(currentNumber + 1)

What is the value of the variable, currentNumber?

Answer: 2

Explanation: When we set the value of currentNumber, there are only two papers, paper1 and paper2, in the MyDesk.Papers collection.
What is the value of paper3?

Answer: The Garble Report
What is the name of paper4?

Answer: The Mooble Report

Explanation: Because currentNumber equals 2, this sets paper4 to the second paper in the collection, The Mooble Report.
What is the name of paper5?

Answer: The Foofle Report
What is the name of paper6?

The Garble Report

Explanation: Because (currentNumber + 1) equals 3, this sets paper6 to the third paper in the collection, The Garble Report.

Hands-on Challenge #6

First, you must add a checkbox with the name "EditSheet" to the PromptDialog form.

Figure 12. Completed dialog box

Next, modify the Yes_Click macro that doesn't create and print the time sheet. We need to test whether the EditSheet check box is selected (checked) or not. If it is selected, create the time sheet. If the check box is cleared, create and print the time sheet as follows:

Private Sub Yes_Click()
   If EditSheet = True Then
      TimeSheet
   End If
   If EditSheet = False Then
      TimeSheet
      ActiveWorkbook.Worksheets.Item(ActiveWorkbook.Worksheets.Count).PrintOut
      PromptDialog.Hide
End Sub

We added a statement to print the last worksheet which is the time sheet we just created. To ensure that we have the last sheet, specify the last sheet in the Worksheets collection.

Now, when the user clicks Create the time sheet with the check box selected, the time sheet is created but not printed.

Super-Easy Guide to the Microsoft Office Excel 2003 Object Model

Contents

Introduction

Why Should I Learn How to Use the Excel Object Model?

Using This Guide

What You Need to Know Before You Get Started

What You'll Know When You Finish the Lessons

Setting Up

Lesson 1: Getting Started

To start writing a macro:

To complete the macro:

To start recording a macro:

About Security

Hands-on Challenge #1 (For answers, see Appendix A)

Lesson 2: Programming Concepts: Sub. . . End Sub and Procedures

Hands-on Challenge #2

Lesson 3: Programming Concepts: Objects, Methods, and Properties

Hands-on Challenge #3

Lesson 4: Real-World Example #1

To create the TimeSheet macro:

Using Events

To run your macro each time Excel starts up:

Hands-on Challenge #4

Lesson 5: Creating Your Own Dialog Boxes

To create a dialog box:

Making Your Dialog Box Work

Lesson 6: Programming Concept: Collections

Collections in Excel

Methods and Properties of Collections

Hands-on Challenge #5

Lesson 7: Programming Concept: If This, Then That

Hands-on Challenge #6

Lesson 8: Real World Example #2

Step 1: Create the Dialog Box

Step 2: Make It Work

Step 3: Finishing the Code

Where to Go from Here

Appendix A: Hands-on Challenge Answers

Hands-on Challenge #1

Hands-on Challenge #2

Hands-on Challenge #3

Hands-on Challenge #4

Hands-on Challenge #5

Hands-on Challenge #6

Additional resources