Excel 2002 VBA Programmers Reference

Overview

What is this book about?

VBA is the programming language for the Microsoft Office suite and many other applications. VBA gives you complete control of Excel, allowing you to do anything from automating Excel tasks to developing full applications, using Excel as the development environment.

Excel 2002 is an important upgrade to the Office suite spreadsheet program. It shows Microsoft's commitment to moving Office to be a web-enabled productivity...

See more details below
Paperback
$50.60
BN.com price
(Save 36%)$79.99 List Price
Other sellers (Paperback)
  • All (20) from $1.99   
  • New (3) from $29.76   
  • Used (17) from $1.99   
Sending request ...

Overview

What is this book about?

VBA is the programming language for the Microsoft Office suite and many other applications. VBA gives you complete control of Excel, allowing you to do anything from automating Excel tasks to developing full applications, using Excel as the development environment.

Excel 2002 is an important upgrade to the Office suite spreadsheet program. It shows Microsoft's commitment to moving Office to be a web-enabled productivity tool, a rich client for working with web-based data, with new features such as SmartTags and XML support.

Based on the successful content and format of Excel 2000 VBA Programmer's Reference, this new edition has been fully updated for Excel 2002. The authors are all Excel MVPs, involved daily in supporting the Excel VBA programmer community.

What does this book cover?

The first part of the book introduces Excel and VBA, including a VBA primer. The second part offers thematic, succinct, and practical discussions of the features available to Excel VBA programmers, with real-world examples answering frequently asked questions. The third and final part is a complete reference to the Excel, VBE, and Common Office Object Models.

Here are just a few of the things you'll learn in this edition:

  • The entire Excel Object Model and the Common Office Object Model
  • Working with PivotTables
  • Accessing and manipulating data sources from Excel with ADO
  • Programming the Visual Basic Editor (VBE)
  • New features in Excel 2002, SmartTags, XML, and the Web
  • Expanded references for Excel versions 97, 2000, and 2002

Who is this book for?

This book not only caters for beginner- and intermediate-level programmers with its introductory coverage of VBA and Excel, but also provides advanced information for experienced Excel developers in later chapters and the reference.

Read More Show Less

Product Details

  • ISBN-13: 9780764543715
  • Publisher: Wiley
  • Publication date: 11/1/2001
  • Edition number: 1
  • Pages: 1032
  • Product dimensions: 7.20 (w) x 9.10 (h) x 2.13 (d)

Meet the Author

John Green works as an independent computer consultant and established his company, Execuplan Consulting, which specializes in developing computer based planning applications and in training. He's led training courses for software applications and operating systems and has been accorded the status of Most Valuable Professional by Microsoft for his contributions to the CompuServe Excel forum and MS Internet newsgroups. Contact him at jgreen@enternet.com.au.

Stephen Bullen set-up his own company called Business Modelling Solutions Ltd, which specialises in Excel and Access development and consulting. The BMS web site 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 can be contacted by email to Stephen@BMSLtd.co.uk.

Rob Bovey is a software developer and 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 Developers Kit. Microsoft has awarded him the title of Most Valuable Professional each year since 1995.

Robert Rosenberg runs his own consulting business which specializes in Microsoft Office advanced training and custom solutions. As a Microsoft Valuable Professional in Excel, he also continually offers advanced online support on Excel on behalf of Microsoft to users of their Internet newsgroups. Robert can be contacted on at rrosenberg@r-cor.com.

Read More Show Less

Table of Contents

Chapter 1: Introduction to Excel.

Chapter 2: Primer in Excel VBA.

Chapter 3: The Application Object.

Chapter 4: Workbooks and Worksheets.

Chapter 5: Using Ranges.

Chapter 6: Using Names.

Chapter 7: PivotTables.

Chapter 8: Filtered Lists.

Chapter 9: Generating Charts.

Chapter 10: Event Procedures.

Chapter 11: Adding Controls.

Chapter 12: Office Files and Folders.

Chapter 13: UserForms.

Chapter 14: Command Bars.

Chapter 15: Class Modules.

Chapter 16: Addins.

Chapter 17: Automation Addins and COM Addins.

Chapter 18: SmartTags.

Chapter 19: Interacting with Other Office Applications.

Chapter 20: Data Access with ADO.

Chapter 21: Excel and the Internet.

Chapter 22: International Issues.

Chapter 23: Programming the VBE.

Chapter 24: Programming with the Windows API.

Appendix A: Excel 2002 Object Model.

Appendix B: VBE Object Model.

Appendix C: Office XP Object Model.

Read More Show Less

First Chapter

Excel 2002 VBA

Programmers Reference
By Rob Bovey Stephen Bullen John Green Robert Rosenberg

John Wiley & Sons

ISBN: 0-7645-4371-7


Chapter One

Introduction to Excel

Excel made its debut on the Macintosh in 1985 and has never lost its position as the most popular spreadsheet application in the Mac environment. In 1987, Excel was ported to the PC, running under Windows. It took many years for Excel to overtake Lotus 1-2-3, which was one of the most successful software systems in the history of computing at that time.

There were a number of spreadsheet applications that enjoyed success prior to the release of the IBM PC in 1981. Among these were VisiCalc and Multiplan. VisiCalc started it all, but fell by the wayside early on. Multiplan was Microsoft's predecessor to Excel, using the R1C1 cell addressing which is still available as an option in Excel. But it was 1-2-3 that shot to stardom very soon after its release in 1982 and came to dominate the PC spreadsheet market.

Early Spreadsheet Macros

1-2-3 was the first spreadsheet application to offer spreadsheet, charting, and database capabilities in one package. However, the main reason for its run-away success was its macro capability. Legend has it that the 1-2-3 developers set up macros as a debugging and testing mechanism for the product. It is said that they only realized the potential of macros at the last minute, and included them into the final release pretty much as an afterthought.

Whatever their origins, macros gave non-programmers a simple way to become programmers and automate their spreadsheets. They grabbed the opportunity and ran. At last they had a measure of independence from the computer department.

The original 1-2-3 macros performed a task by executing the same keystrokes that a user would use to carry out the same task. It was, therefore, very simple to create a macro as there was virtually nothing new to learn to progress from normal spreadsheet manipulation to programmed manipulation. All you had to do was remember what keys to press and write them down. The only concessions to traditional programming were eight extra commands, the /x commands. The /x commands provided some primitive decision making and branching capabilities, a way to get input from a user, and a way to construct menus.

One major problem with 1-2-3 macros was their vulnerability. The multi-sheet workbook had not yet been invented and macros had to be written directly into the cells of the spreadsheet they supported, along with input data and calculations. Macros were at the mercy of the user. For example, they could be inadvertently disrupted when a user inserted or deleted rows or columns. Macros were also at the mercy of the programmer. A badly designed macro could destroy itself quite easily while trying to edit spreadsheet data.

Despite the problems, users reveled in their newfound programming ability and millions of lines of code were written in this cryptic language, using arcane techniques to get around its many limitations. The world came to rely on code that was often badly designed, nearly always poorly documented, and at all times highly vulnerable, often supporting enterprise-critical control systems.

The XLM Macro Language

The original Excel macro language required you to write your macros in a macro sheet that was saved in a file with an .xlm extension. In this way, macros were kept separate from the worksheet, which was saved in a file with an .xls extension. These macros are now often referred to as XLM macros, or Excel 4 macros, to distinguish them from the VBA macro language introduced in Excel Version 5.

The XLM macro language consisted of function calls, arranged in columns in the macro sheet. There were many hundreds of functions necessary to provide all the features of Excel and allow programmatic control. The XLM language was far more sophisticated and powerful than the 1-2-3 macro language, even allowing for the enhancements made in 1-2-3 Releases 2 and 3. However, the code produced was not much more intelligible.

The sophistication of Excel's macro language was a two edged sword. It appealed to those with high programming aptitude, who could tap the language's power, but was a barrier to most users. There was no simple relationship between the way you would manually operate Excel and the way you programmed it. There was a very steep learning curve involved in mastering the XLM language.

Another barrier to Excel's acceptance on the PC was that it required Windows. The early versions of Windows were restricted by limited access to memory, and Windows required much more horsepower to operate than DOS. The Graphical User Interface was appealing, but the tradeoffs in hardware cost and operating speed were perceived as problems.

Lotus made the mistake of assuming that Windows was a flash in the pan, soon to be replaced by OS/2, and did not bother to plan a Windows version of 1-2-3. Lotus put its energy into 1-2-3/G, a very nice GUI version of 1-2-3 that only operated under OS/2. This one horse bet was to prove the undoing of 1-2-3.

By the time it became clear that Windows was here to stay, Lotus was in real trouble as it watched users flocking to Excel. The first attempt at a Windows version of 1-2-3, released in 1991, was really 1-2-3 Release 3 for DOS in a thin GUI shell. Succeeding releases have closed the gap between 1-2-3 and Excel, but have been too late to stop the almost universal adoption of Microsoft Office by the market.

Excel 5

Microsoft took a brave decision to unify the programming code behind its Office applications by introducing VBA (Visual Basic for Applications) as the common macro language in Office. Excel 5, released in 1993, was the first application to include VBA. It has been gradually introduced into the other Office applications in subsequent versions of Office. Excel, Word, Access, PowerPoint, and Outlook all use VBA as their macro language in Office XP.

Since the release of Excel 5, Excel has supported both the XLM and the VBA macro languages, and the support for XLM should continue into the foreseeable future, but will decrease in significance as users switch to VBA.

VBA is an object-oriented programming language that is identical to the Visual Basic programming language in the way it is structured and in the way it handles objects. If you learn to use VBA in Excel, you know how to use it in the other Office applications.

The Office applications differ in the objects they expose to VBA. To program an application, you need to be familiar with its object model. The object model is a hierarchy of all the objects that you find in the application. For example, part of the Excel Object Model tells us that there is an Application object that contains a Workbook object that contains a Worksheet object that contains a Range object.

VBA is somewhat easier to learn than the XLM macro language, is more powerful, is generally more efficient, and allows us to write well-structured code. We can also write badly structured code, but by following a few principles, we should be able to produce code that is readily understood by others and is reasonably easy to maintain.

In Excel 5, VBA code was written in modules, which were sheets in a workbook. Worksheets, chart sheets, and dialog sheets were other types of sheets that could be contained in an Excel 5 workbook.

A module is really just a word-processing document with some special characteristics that help you write and test code.

Excel 97

In Excel 97, Microsoft introduced some dramatic changes in the VBA interface and some changes in the Excel Object Model. From Excel 97 onwards, modules are not visible in the Excel application window and modules are no longer objects contained by the Workbook object. Modules are contained in the VBA project associated with the workbook and can only be viewed and edited in the Visual Basic Editor (VBE) window.

In addition to the standard modules, class modules were introduced, which allow you to create your own objects and access application events. Commandbars were introduced to replace menus and toolbars, and UserForms replaced dialog sheets. Like modules, UserForms can only be edited in the VBE window. As usual, the replaced objects are still supported in Excel, but are considered to be hidden objects and are not documented in the Help screens.

In previous versions of Excel, objects such as buttons embedded in worksheets could only respond to a single event, usually the Click event. Excel 97 greatly increased the number of events that VBA code can respond to and formalised the way in which this is done by providing event procedures for the workbook, worksheet and chart sheet objects. For example, workbooks now have 20 events they can respond to, such as BeforeSave, BeforePrint, and BeforeClose. Excel 97 also introduced ActiveX controls that can be embedded in worksheets and UserForms. ActiveX controls can respond to a wide range of events such as GotFocus, MouseMove, and DblClick.

The VBE provides users with much more help than was previously available. For example, as we write code, popups appear with lists of appropriate methods and properties for objects, and arguments and parameter values for functions and methods. The Object Browser is much better than previous versions, allowing us to search for entries, for example, and providing comprehensive information on intrinsic constants.

Microsoft has provided an Extensibility library that makes it possible to write VBA code that manipulates the VBE environment and VBA projects. This makes it possible to write code that can directly access code modules and UserForms. It is possible to set up applications that indent module code or export code from modules to text files, for example.

Excel 97 has been ported to the Macintosh in the form of Excel 98. Unfortunately, many of the VBE help features that make life easy for programmers have not been included. The VBE Extensibility features have not made it to the Mac either.

Excel 2000

Excel 2000 did not introduce dramatic changes from a VBA programming perspective. There were a large number of improvements in the Office 2000 and Excel 2000 user interfaces and improvements in some Excel features such as PivotTables. A new PivotChart feature was added. Web users benefited the most from Excel 2000, especially through the ability to save workbooks as web pages. There were also improvements for users with a need to share information, through new online collaboration features.

One long awaited improvement for VBA users was the introduction of modeless UserForms. Previously, Excel only supported modal dialog boxes, which take the focus when they are on screen so that no other activity can take place until they are closed. Modeless dialog boxes allow the user to continue with other work while the dialog box floats above the worksheet. Modeless dialog boxes can be used to show a "splash" screen when an application written in Excel is loaded and to display a progress indicator while a lengthy macro runs.

Excel 2002

Excel 2002 has also introduced only incremental changes. Once more, the major improvements have been in the user interface rather than in programming features. Microsoft continues to concentrate on improving web-related features to make it easier to access and distribute data using the Internet. New features that could be useful for VBA programmers include a new Protection object, SmartTags, RTD (Real Time Data), and improved support for XML.

The new Protection object lets us selectively control the features that are accessible to users when we protect a worksheet. We can decide whether users can sort, alter cell formatting, or insert and delete rows and columns, for example. There is also a new AllowEditRange object that we can use to specify which users can edit specific ranges and whether they must use a password to do so. We can apply different combinations of permissions to different ranges.

SmartTags allow Excel to recognize data typed into cells as having special significance. For example, Excel 2002 can recognize stock market abbreviations, such as MSFT for Microsoft Corporation. When Excel sees an item like this, it displays a SmartTag symbol that has a popup menu. We can use the menu to obtain related information, such as the latest stock price or a summary report on the company. Microsoft provides a kit that allows developers to create new SmartTag software, so we could see a whole new class of tools appearing that use SmartTags to make data available throughout an organization or across the Internet.

RTD allows developers to create sources of information that users can draw from. Once you establish a link to a worksheet, changes in the source data are automatically passed on. An obvious use for this is to obtain stock prices that change in real time during the course of trading. Other possible applications include the ability to log data from scientific instruments or industrial process controllers. As with SmartTags, we will probably see a host of applications developed to make it easy for Excel users to gain access to dynamic information.

Improved XML support means it is getting easier to create applications that exchange data through the Internet and intranets. As we all become more dependent on these burgeoning technologies, this will become of increasing importance.

Excel 2002 VBA Programmer's Reference

This book is aimed squarely at Excel users who want to harness the power of the VBA language in their Excel applications. At all times, the VBA language is presented in the context of Excel, not just as a general application programming language.

The pages that follow have been divided into three sections:

Primer Working with Specific Objects Object Model References

The Primer has been written for those who are new to VBA programming and the Excel Object Model. It introduces the VBA language and the features of the language that are common to all VBA applications. It explains the relationship between collections, objects, properties, methods, and events and shows how to relate these concepts to Excel through its object model. It also shows how to use the Visual Basic Editor and its multitude of tools, including how to obtain help.

The middle section of the book takes the key objects in Excel and shows, through many practical examples, how to go about working with those objects. The techniques presented have been developed through the exchange of ideas of many talented Excel VBA programmers over many years and show the best way to gain access to workbooks, worksheets, charts, ranges, etc. The emphasis is on efficiency, that is how to write code that is readable and easy to maintain and that runs at maximum speed. In addition Rob Bovey has written a chapter on Excel and ADO that details techniques for accessing data, independent of its format.

Continues...


Excerpted from Excel 2002 VBA by Rob Bovey Stephen Bullen John Green 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

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

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

Reminder:

  • - 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)