Excel 2003 VBA Programmer's Reference


What is this book about?

Excel 2003 VBA Programmer's Reference is an updated and expanded version of the two previous editions now with a reference section downloadable from the Web for easy perusal. The book is aimed at Excel users who want to gain more control over their spreadsheets using VBA or who want to develop Excel applications for other users. The book starts with a primer chapter focused on bringing the readers up to speed with Excel and VBA. From there, the book ...

See more details below
Other sellers (Paperback)
  • All (31) from $1.99   
  • New (6) from $8.99   
  • Used (25) from $1.99   
Sending request ...


What is this book about?

Excel 2003 VBA Programmer's Reference is an updated and expanded version of the two previous editions now with a reference section downloadable from the Web for easy perusal. The book is aimed at Excel users who want to gain more control over their spreadsheets using VBA or who want to develop Excel applications for other users. The book starts with a primer chapter focused on bringing the readers up to speed with Excel and VBA. From there, the book expands to focus on major issues faced by advanced Excel users and developers.

What does this book cover?

In this book, you'll discover how to do the following:

  • Set up applications and convert them to add-ins
  • Package and distribute Excel applications
  • Set up interaction with other Office applications and databases
  • Program the VB Editor and use the Windows API
  • Use VB6 and VB.NET with Excel
  • Set up internationalization
  • Advanced debugging and error handling techniques
Read More Show Less

Product Details

  • ISBN-13: 9780764556609
  • Publisher: Wiley
  • Publication date: 2/23/2004
  • Edition number: 1
  • Pages: 1176
  • Sales rank: 1,249,829
  • Product dimensions: 7.38 (w) x 9.20 (h) x 2.20 (d)

Meet the Author

Paul Kimmel founded Software Conceptions, Inc in 1990 and has been designing and building software and writing computer books ever since. Paul Kimmel is the author of several books on VBA, VB, VB.NET, C#, Delphi, and C++. Paul is also the bi-monthly columnist for www.codeguru.com ‘s VB Today column and a frequent contributor to periodicals and online publications, including www.InformiT.com. You may contact him at pkimmel@softconcepts.com for help designing and building software.

Stephen Bullen is based in Carlow, Ireland and in London, England. He has been running his own Company, Business Modelling Solutions Ltd Since 1997, specializing in Excel development and consulting and has worked for some of the worlds largest companies. The BMS web site, www.BMSLtd.co.uk, contains a large number of examples of his work, including tools and utilities to extend Excel’s functionality and many examples of Excel development techniques.
Stephen devotes a lot of his spare time to helping other Excel users by answering questions in the CompuServ Excel forum and Microsoft’s Internet Newsgroup. In recognition of his contribution and knowledge, Microsoft has awarded him the status of ‘Most Valuable Professional’ every year since 1996.
Stephen wrote most of the Latest chapters in the Excel 2000 and Excel 2002 VBA.
Programmers References, which has been carried forward to this book and updated by Paul Kimmel where appropriate. Stephen has not directly contributed to this edition.

John Green, Sydney Australia, is an independent consultant specializing in Excel and Access. With 30 years of computing experience, a Chemical Engineering degree and an MBA, he draws from a diverse background. He has led training courses for software applications and operating systems both in Australia and overseas. Microsoft has awarded him the status of ‘Most Valuable Professional’ every year since 1995.
John was the principal author of the Excel 2000 and Excel 2002 VBA Programmers References, which have been carried forward to this book and updated by Paul Kimmel where appropriate. John has not directly contributed to this edition.

Bob Bovey is a software developer specializing in Microsoft Office, Visual Basic, and SQL Server applications. He is founder and president of the custom application development firm Application Professionals. Rob developed several Addins shipped by Microsoft for Excel. He also co-authored the Microsoft Excel 97 Developer’s Kit. Microsoft has awarded Rob the status of ‘Most Valuable Professional’ every year since 1995. Rob authored the chapter on Data Access with ADO for the Excel 2002 VBA Programmer’s Reference. He has not directly contributed to this edition.

Robert Rosenberg runs his own consulting business, which specializes in providing custom solutions and advanced training in Microsoft Office. His clients include fortune five hundred companies in the entertainment, financial, and legal fields. As a Microsoft Valuable Professional in Excel, he also continually offers advanced online support in Excel on behalf of Microsoft to users of their Internet newsgroups. Robert was responsible for updating the content for the Excel and Office Indexes for the 2002 version of this book. This included updating example code and listings for existing VBA objects as well as listing new object descriptions, their methods, properties and/or arguments along with code examples.

Brian Patterson (Contributor) currently works for Illinois Mutual Life as a Software Development Coordinator where he is generally working with C# in WinForms or the Corporate Internet site. Brian has been writing for various publications since 1994 and has co-written several .NET related books including “Migrating to Visual Basic.nET” and .NET Enterprise Development with VB.NET.” You can generally find him posting in the MSDN Newsgroups or hanging out with his lovely wife and 3 children. You can reach him via email at bdpatterson@illinoismutual.com.

Read More Show Less

Table of Contents

About the Authors.



Chapter 1: Primer in Excel VBA.

Chapter 2: Programming in the VBE.

Chapter 3: The Application Object.

Chapter 4: Object-Oriented Theory and VBA.

Chapter 5: Event Procedures.

Chapter 6: Class Modules.

Chapter 7: Writing Bulletproof Code.

Chapter 8: Debugging and Testing.

Chapter 9: UserForms.

Chapter 10: Adding Controls.

Chapter 11: Data Access with ADO.

Chapter 12: Creating and Using Add-ins.

Chapter 13: Automation Addins and COM Addins.

Chapter 14: Customizing the VBE.

Chapter 15: Interacting with Other Office Applications.

Chapter 16: Programming with the Windows API.

Chapter 17: International Issues.

Chapter 18: Workbooks and Worksheets.

Chapter 19: Using Ranges.

Chapter 20: Using Names.

Chapter 21: Working with Lists.

Chapter 22: PivotTables.

Chapter 23: Filtered Lists.

Chapter 24: Generating Charts.

Chapter 25: Office Files and Folders.

Chapter 26: Command Bars.

Chapter 27: SmartTags.

Chapter 28: Excel and the Internet.

Chapter 29: XML and Excel.

Appendix A: Excel 2003 Object Model.

Appendix B: VBE Object Model.

Appendix C: VBE Object Model.


Read More Show Less

First Chapter

Excel 2003 VBA Programmer's Reference

By Paul T. Kimmel Stephen Bullen John Green Rob Bovey Robert Rosenberg

John Wiley & Sons

ISBN: 0-7645-5660-6

Chapter One

Primer in Excel VBA

This chapter is intended for those who are not familiar with Excel and the Excel macro recorder, or who are inexperienced with programming using the Visual Basic for Applications (VBA) language. If you are already comfortable with navigating around the features provided by Excel, have used the macro recorder, and have a working knowledge of VBA and the Visual Basic Editor (VBE), you might want to skip straight to Chapter 3.

If this is not the case, this chapter has been designed to provide you with the information you need to be able to move on comfortably to the more advanced features presented in the following chapters. We will be covering the following topics:

The Excel macro recorder

User-defined functions

The Excel Object Model

VBA programming concepts

Excel VBA is a programming application that allows you to use Visual Basic code to run the many features of the Excel package, thereby allowing you to customize your Excel applications. Units of VBA code are often referred to as macros. We will be covering more formal terminology in this chapter, but we will continue to use the term macro as a general way to refer to any VBA code.

In your day-to-day use of Excel, if you carry out the same sequence of commands repetitively, you can save a lot of time and effort by automating those steps using macros. If you are setting up an application for other users, who don't know much about Excel, you can use macros to create buttons and dialog boxes to guide them through your application as well as automate the processes involved.

If you are able to perform an operation manually, you can use the macro recorder to capture that operation. This is a very quick and easy process and requires no prior knowledge of the VBA language. Many Excel users record and run macros and feel no need to learn about VBA.

However, the recorded results might not be very flexible, in that the macro can only be used to carry out one particular task on one particular range of cells. In addition, the recorded macro is likely to run much more slowly than the code written by someone with knowledge of VBA. To set up interactive macros that can adapt to change and also run quickly, and to take advantage of more advanced features of Excel such as customized dialog boxes, you need to learn about VBA.

In this chapter you will learn how to use the macro recorder and you will see all the ways Excel provides to run your macros. You will see how to use the Visual Basic Editor to examine and change your macros, thus going beyond the recorder and tapping into the power of the VBA language and the Excel Object Model.

You can also use VBA to create your own worksheet functions. Excel comes with hundreds of built-in functions, such as SUM and IF, which you can use in cell formulas. However, if you have a complex calculation that you use frequently and that is not included in the set of standard Excel functions-such as a tax calculation or a specialized scientific formula-you can write your own user-defined function.

Using the Macro Recorder

Excel's macro recorder operates very much like the recorder that stores the greeting on your telephone answering machine. To record a greeting, you first prepare yourself by rehearsing the greeting to ensure that it says what you want. Then, you switch on the recorder and deliver the greeting. When you have finished, you switch off the recorder. You now have a recording that automatically plays when you leave a call unanswered.

Recording an Excel macro is very similar. You first rehearse the steps involved and decide at what points you want to start and stop the recording process. You prepare your spreadsheet, switch on the Excel recorder, carry out your Excel operations, and switch off the recorder. You now have an automated procedure that you and others can reproduce at the press of a button.

Recording Macros

Say, you want a macro that types six month names as three letter abbreviations, "Jan" to "Jun", across the top of your worksheet, starting in cell B1. We know this is rather a silly macro as you could do this easily with an AutoFill operation, but this example will serve to show us some important general concepts:

First, think about how you are going to carry out this operation. In this case, it is easy-you will just type the data across the worksheet. Remember, a more complex macro might need more rehearsals before you are ready to record it.

Next, think about when you want to start recording. In this case, you should include the selection of cell B1 in the recording, as you want to always have "Jan" in B1. If you don't select B1 at the start, you will record typing "Jan" into the active cell, which could be anywhere when you play back the macro.

Next, think about when you want to stop recording. You might first want to include some formatting such as making the cells bold and italic, so you should include that in the recording. Where do you want the active cell to be after the macro runs? Do you want it to be in the same cell as "Jun", or would you rather have the active cell in column A or column B, ready for your next input? Let's assume that you want the active cell to be A2, at the completion of the macro, so we will select A2 before turning off the recorder.

Now you can set up your screen, ready to record.

In this case, start with an empty worksheet with cell A1 selected. If you like to work with toolbars, use View[right arrow]Toolbars to select and display the Visual Basic toolbar as shown in Figure 1-1 in the top right of the screen. Press the Record Macro button, with the red dot, to start the recorder. If you prefer, start the recorder with Tools[right arrow]Macro[right arrow]Record New Macro ... from the Worksheet menu bar.

In the Macro name: box, replace the default entry, such as Macro1, with the name you want for your macro. The name should start with a letter and contain only letters, numbers and the underscore character with a maximum length of 255 characters. The macro name must not contain special characters such as !, ?, or blank spaces. It is also best to use a short but descriptive name that you will recognize later. You can use the underscore character to separate words, but it is easy to just use capitalization to distinguish words.

Call the macro MonthNames1, because we will create another version later.

In the Shortcut key: box, you can type in a single letter. This key can be pressed later, while holding down the Ctrl key, to run the macro. We will use a lower case m. Alternatively, you can use an upper case M. In this case, when you later want to run the macro, you need to hold down the Ctrl key and the Shift key while you press M. It is not mandatory to provide a shortcut key. You can run a macro in a number of other ways, as we will see.

In the Description: box, you can accept the default comments provided by the recorder, or type in your own comments. These lines will appear at the top of your macro code. They have no significance to VBA but provide you and others with information about the macro. You can edit these comments later, so there is no need to change them now. All Excel macros are stored in workbooks.

You are given a choice regarding where the recorded macro will be stored. The Store macro in: combo box lists three possibilities. If you choose New Workbook, the recorder will open a new empty workbook for the macro. Personal Macro Workbook refers to a special hidden workbook that we will discuss next. We will choose This Workbook to store the macro in the currently active workbook.

When you have filled in the Record Macro dialog box, click the OK button. You will see the word Recording on the left side of the Status Bar at the bottom of the screen and the Stop Recording toolbar should appear on the screen. Note that the Stop Recording toolbar will not appear if it has been previously closed during a recording session. If it is missing, refer to the following instructions under the heading Absolute and Relative Recording to see how to reinstate it. However, you don't really need it for the moment because we can stop the recording from the Visual Basic toolbar or the Tools menu.

If you have the Stop Recording toolbar visible, make sure that the second button, the Relative Reference button, is not selected. It shouldn't have a border, that is, it should not be as it appears in this screenshot in Figure 1-2. By default, the macro recorder uses absolute cell references when it records.

You should now click cell B1 and type in "Jan" and fill in the rest of the cells, as shown in Figure 1-3. Then, select B1:G1 and click the Bold and Italic buttons on the Formatting toolbar. Click the A2 cell and then stop the recorder.

You can stop the recorder by pressing the Stop Recording button on the Stop Recording toolbar, by pressing the square Stop Recording button on the Visual Basic toolbar-the round Start Recording button changes to the Stop Recording button while you are recording-or you can use Tools Macro Stop Recording from the menu bar. Save the workbook as Recorder.xls.

The Personal Macro Workbook

If you choose to store your recorded macro in the Personal Macro Workbook, the macro is added to a special file called Personal.xls, which is a hidden file that is saved in your Excel Startup directory when you close Excel. This means that Personal.xls is automatically loaded when you launch Excel and, therefore, its macros are always available for any other workbook to use.

If Personal.xls does not already exist, the recorder will create it for you. You can use Window Unhide to see this workbook in the Excel window, but it is seldom necessary or desirable to do this as you can examine and modify the Personal.xls macros in the Visual Basic Editor window. An exception, where you might want to make Personal.xls visible, is if you need to store data in its worksheets. You can hide it again, after adding the data, with Window[right arrow]Hide. If you are creating a general-purpose utility macro, which you want to be able to use with any workbook, store it in Personal.xls. If the macro relates to just the application in the current workbook, store the macro with the application.

Running Macros

To run the macro, either insert a new worksheet in the Recorder.xls workbook, or open a new empty workbook, leaving Recorder.xls open in memory. You can only run macros that are in open workbooks, but they can be run from within any other open workbook.

You can run the macro by holding down the Ctrl key and pressing m, the shortcut that we assigned at the start of the recording process. You can also run the macro by clicking Tools[right arrow]Macro[right arrow]Macros ... on the Worksheet menu bar and double-clicking the macro name, or by selecting the macro name and clicking Run, as shown in Figure 1-4.

The same dialog box can be opened by pressing the Run Macro button on the Visual Basic toolbar, as shown in Figure 1-5.

Shortcut Keys

You can change the shortcut key assigned to a macro by first bringing up the Macro dialog box, by using Tools[right arrow]Macro[right arrow]Macros, or the Run Macro button on the Visual Basic toolbar. Select the macro name and press Options. This opens the following dialog box shown in Figure 1-6.

It is possible to assign the same shortcut key to more than one macro in the same workbook using this dialog box (although the dialog box that appears when you start, the macro recorder will not let you assign a shortcut that is already in use).

Shortcuts are appropriate for macros that you use very frequently, especially if you prefer to keep your hands on the keyboard. It is worth memorizing the shortcuts so you won't forget them if you use them regularly. Shortcuts are not appropriate for macros that are run infrequently or are intended to make life easier for less experienced users of your application. It is better to assign meaningful names to those macros and run them from the Macro dialog box. Alternatively, they can be run from buttons that you add to the worksheet, or place on the toolbars. You will learn how to do this shortly.

Absolute and Relative Recording

When you run MonthNames1, the macro returns to the same cells you selected while typing in the month names. It doesn't matter which cell is active when you start, if the macro contains the command to select cell B1, then that is what it selects. The macro selects B1 because you recorded in absolute record mode. The alternative, relative record mode, remembers the position of the active cell relative to its previous position. If you have cell A10 selected, and then turn on the recorder and you go on to select B10, the recorder notes that you moved one cell to the right, rather than noting that you selected cell B10.

We will record a second macro called MonthNames2. There will be three differences in this macro compared with the previous one:

We will use the Relative Reference button on the Stop Recording toolbar as our first action after turning on the recorder.

We will not select the "Jan" cell before typing. We want our recorded macro to type "Jan" into the active cell when we run the macro.

We will finish by selecting the cell under "Jan", rather than A2, just before turning off the recorder.

Start with an empty worksheet and select the B1 cell. Turn on the macro recorder and specify the macro name as MonthNames2. Enter the shortcut as uppercase M-the recorder won't let you use lowercase m again. Click the OK button and select the Relative Reference button on the Stop Recording toolbar.

If you needed to resynchronize the Stop Recording toolbar using the instructions above, upper case M will already be assigned. If you have difficulties assigning the uppercase M shortcut to MonthNames2 on the second recording, use another key such as uppercase N, and change it back to M after finishing the recording. Use Tools Macro Macros ... and, in the Macro dialog box, select the macro name and press the Options button, as explained earlier in the Shortcut Keys section.

Type "Jan" and the other month names, as you did when recording MonthNames1. Select cells B1:G1 and press the Bold and Italic buttons on the Formatting toolbar.

Finally, select cell B2, the cell under "Jan", and turn off the recorder.

Before running MonthNames2, select a starting cell, such as A10. You will find that the macro now types the month names across row 10, starting in column A and finishes by selecting the cell under the starting cell.

Before you record a macro that selects cells, you need to think about whether to use absolute or relative reference recording.


Excerpted from Excel 2003 VBA Programmer's Reference by Paul T. Kimmel Stephen Bullen John Green Rob Bovey Robert Rosenberg Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)