Excel 97 Programming for Windows For Dummies

Overview

By itself, Excel 97 can do some pretty amazing stuff. Add Visual Basic for Applications (VBA) and you add a whole other dimension of automation and customization. Excel 97 Programming For Windows For Dummies offers plenty of practical examples of Excel programming in action and explains in plain English how to
* Save time and improve accuracy by automating common tasks
* ...
See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (27) from $1.99   
  • New (10) from $2.38   
  • Used (17) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$2.38
Seller since 2009

Feedback rating:

(2338)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
1997-07-03 Paperback 1 New 0764501321 Ships Within 24 Hours. Tracking Number available for all USA orders. Excellent Customer Service. Upto 15 Days 100% Money Back Gurantee. Try ... Our Fast! ! ! ! Shipping With Tracking Number. Read more Show Less

Ships from: Bensalem, PA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$2.43
Seller since 2007

Feedback rating:

(1325)

Condition: New
0764501321 Fast Reliable Shipping From a trusted online seller!

Ships from: Cypress, TX

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$8.13
Seller since 2010

Feedback rating:

(901)

Condition: New
PAPERBACK New 0764501321 Friendly Return Policy. A+++ Customer Service!

Ships from: Philadelphia, PA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$8.13
Seller since 2013

Feedback rating:

(389)

Condition: New
PAPERBACK New 0764501321! ! KNOWLEDGE IS POWER! ! ENJOY OUR BEST PRICES! ! ! Ships Fast. All standard orders delivered within 5 to 12 business days.

Ships from: Southampton, PA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$8.13
Seller since 2014

Feedback rating:

(265)

Condition: New
PAPERBACK New 0764501321 XCITING PRICES JUST FOR YOU. Ships within 24 hours. Best customer service. 100% money back return policy.

Ships from: Bensalem, PA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$8.13
Seller since 2011

Feedback rating:

(710)

Condition: New
PAPERBACK New 0764501321 SERVING OUR CUSTOMERS WITH BEST PRICES. FROM A COMPANY YOU TRUST, HUGE SELECTION. RELIABLE CUSTOMER SERVICE! ! HASSLE FREE RETURN POLICY, SATISFACTION ... GURANTEED**** Read more Show Less

Ships from: Philadelphia, PA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$8.13
Seller since 2010

Feedback rating:

(674)

Condition: New
PAPERBACK New 0764501321! ! ! ! BEST PRICES WITH A SERVICE YOU CAN RELY! ! !

Ships from: Philadelphia, PA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$13.16
Seller since 2009

Feedback rating:

(9975)

Condition: New
New Book. Shipped from US within 4 to 14 business days. Established seller since 2000

Ships from: Secaucus, NJ

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
$13.63
Seller since 2008

Feedback rating:

(17421)

Condition: New
Brand New, Perfect Condition, Please allow 4-14 business days for delivery. 100% Money Back Guarantee, Over 1,000,000 customers served.

Ships from: Westminster, MD

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
$14.57
Seller since 2007

Feedback rating:

(23274)

Condition: New
BRAND NEW

Ships from: Avenel, NJ

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview

By itself, Excel 97 can do some pretty amazing stuff. Add Visual Basic for Applications (VBA) and you add a whole other dimension of automation and customization. Excel 97 Programming For Windows For Dummies offers plenty of practical examples of Excel programming in action and explains in plain English how to
* Save time and improve accuracy by automating common tasks
* Design custom dialog boxes to suit your and your organization's needs
* Build specialized functions to use in your worksheets
* Create add-ins, take advantage of the Windows API, and define custom data types With numerous books and articles on Excel to his credit, John Walkenbach is the ideal author to make Excel programming understandable, whether you're new to programming or just new to this latest release of Excel.
Read More Show Less

Product Details

  • ISBN-13: 9780764501326
  • Publisher: Wiley
  • Publication date: 7/28/1997
  • Series: For Dummies Series
  • Edition number: 1
  • Pages: 405
  • Product dimensions: 7.44 (w) x 9.26 (h) x 0.89 (d)

Meet the Author

John Walkenbach has written hundreds of articles and numerous books, including Microsoft?? Excel 2000 Formulas. He is the principal of JWalk Associates, a spreadsheet application development company.
Read More Show Less

Table of Contents

Introduction.

PART I: Introducing VBA.

Chapter 1: What Is VBA?

Chapter 2: Jumping Right In.

PART II: How VBA Works with Excel.

Chapter 3: Introducing the Visual Basic Editor.

Chapter 4: Introducing the Excel Object Model.

Chapter 5: VBA Subroutines and Functions.

Chapter 6: Using the Excel Macro Recorder.

PART III: Programming Concepts.

Chapter 7: Essential VBA Language Elements.

Chapter 8: Working with Range Objects.

Chapter 9: Using VBA Functions and Excel Functions.

Chapter 10: Controlling Program Flow and Making Decisions.

Chapter 11: Automatic Procedures and Events.

Chapter 12: Error-Handling Techniques.

Chapter 13: Bug Extermination Techniques.

Chapter 14: VBA Programming Examples.

PART IV: Developing Custom Dialog Boxes.

Chapter 15: Custom Dialog Box Alternatives.

Chapter 16: Custom Dialog Box Basics.

Chapter 17: Using Dialog Box Controls.

Chapter 18: Dialog Box Techniques and Tricks.

PART V: Creating Custom Toolbars and Menus.

Chapter 19: Customizing the Excel Toolbars.

Chapter 20: When Excel's Normal Menus Aren't Good Enough.

PART VI: Putting It All Together.

Chapter 21: Creating Worksheet Functions -- and Living to Tell about It.

Chapter 22: Creating Excel Add-Ins.

Chapter 23: Creating Excel Applications for Other Users.

PART VII: The Part of Tens.

Chapter 24: Top Ten VBA Tips and Tricks.

Chapter 25: Top Ten VBA Questions (And Answers).

Chapter 26: Top Ten Excel Resources.

Index.

Read More Show Less

First Chapter

Chapter 1
What Is VBA?

In This Chapter

  • A conceptual overview of VBA
  • What you can do with VBA
  • Advantages and disadvantages of using VBA
  • A mini-lesson on the history of Excel

This chapter is completely devoid of hands-on training stuff. It does, however, contain some essential background information that will assist you on your way to becoming an Excel programmer. In other words, this chapter paves the way for everything else that follows and gives you a feel for how Excel programming fits into the overall scheme of the universe.

Okay, So What Is VBA?

VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft -- you know, the company run by the richest man in the United States. Excel -- along with the other members of Microsoft Office 97 -- includes the VBA language.

Don't confuse VBA with VB (which stands for Visual Basic). VB is a programming language that lets you create standalone executable programs (you know, those EXE files). Although VBA and VB have a lot in common, they are different animals. And to confuse things even more, there's also something called VBScript (Visual Basic Script) -- which is a programming language that's used for Web pages and other applications, including Outlook 97. Again, VBScript has a lot in common with VBA, but it's definitely not the same.

In a nutshell, VBA is the tool you use to develop macros (or programs) that control Excel.

A few words about terminology

With the introduction of VBA in Excel 5, the terminology used to describe the Excel programmable features got a bit muddy. For example, VBA is a programming language, but it also serves as a macro language. So what do you call something written in VBA and executed in Excel? Is it a macro, or is it a program? Excel's online help often refers to VBA procedures as macros, so I use that terminology. But I also call this stuff a program.

By the way, macro does not stand for Messy And Confusing Repeated Operation. Rather, it comes from the Greek makros, which means large -- which also describes your paycheck after you become an expert macro programmer.

What Can You Do with VBA?

You're undoubtedly aware that people use Excel for thousands of different tasks. Here are just a few examples:

  • Keeping lists of things, such as customers, students' grades, or Christmas gift ideas
  • Budgeting and forecasting
  • Analyzing scientific data
  • Creating invoices and other forms
  • Developing charts and maps from data
  • Blah, blah, blah

This list could go on and on, but I think you get the idea. My point is simply that Excel is used for a wide variety of applications, and everyone reading this book has different needs and expectations regarding Excel. But one thing virtually every reader has in common is the need to automate some aspects of Excel. That, dear reader, is what VBA is all about.

For example, you might create a VBA macro to format and print your month-end sales report. After developing and debugging the macro, you can execute the macro with a single command, causing Excel to perform many time-consuming procedures automatically. Rather than struggle through a tedious sequence of commands, you can grab a cup of joe and let your computer do the work -- which is how it's supposed to be, right?

Kodak had an advertising slogan: "You press the button, we do the rest." That statement pretty much sums up the appeal of macros. You execute a macro with a single action (perhaps a button click), and the macro automatically does lots of cool things for you.

In the following sections, I briefly describe some common uses for VBA macros. One or two of these may push your button.

Insert a text string automatically

If you often need to enter your company name into worksheets, you can create a macro to do the typing for you. You can also extend this concept as far as you like. For example, you might develop a macro that automatically types a list of all salespeople who work for your company. This is a very simple -- but quite handy -- use for VBA.

Automate a task you perform frequently

Assume you're a sales manager and you need to prepare a month-end sales report to keep your boss happy. If the task is straightforward, you can develop a VBA macro to do it for you. Your boss will be impressed by the consistently high quality of your reports, and you'll be promoted to a new job for which you are highly unqualified.

Automate repetitive operations

If you need to perform the same action on, say, 12 different Excel workbooks, you can record a macro while you perform the task on the first workbook and then let the macro repeat your action on the other workbooks. The nice thing about this is that Excel never complains about being bored.

Create a custom command

Do you find that you often issue the same sequence of Excel menu commands? If so, you can save yourself a few seconds by developing a macro that combines these commands into a single custom command, which you can execute with a single keystroke.

Create a custom toolbar button

You can customize the Excel toolbars with your own buttons that execute macros you write. Quite impressive.

Create a custom menu command

You can also customize Excel's menus with your own commands that execute macros you write. Even more impressive.

Create a simplified front end for users who don't know much about Excel

In almost any office, you can find lots of people who don't really understand how to use computers (sound familiar?). Using VBA, you can make it easy for these inexperienced users to perform useful work. For example, you can set up a foolproof data entry template so that you don't have to waste your time doing mundane work.

Develop new worksheet functions

Although Excel includes numerous built-in functions, you can create custom worksheet functions that can greatly simplify your formulas. I guarantee you'll be surprised by how easy this is. (I show you how to do this in Chapter 21.) Even better, the Paste Function dialog box displays your custom functions, making them appear to be built-in Excel functions.

Create complete, turnkey, macro-driven applications

If you're willing to spend some time, you can use VBA to create large-scale applications complete with custom dialog boxes, online help, and lots of other accoutrements.

Create custom add-ins for Excel

You're probably familiar with some of the add-ins that ship with Excel (for example, the Analysis ToolPak is a popular add-in). Most of these were created with Excel macros. I developed my Power Utility Pak add-in using only VBA. With some help from this book, you can create your own add-ins.

Advantages and Disadvantages of VBA

In this section, I briefly describe the good things about VBA -- and I also explore its darker side.

VBA advantages

You can automate almost anything you do in Excel. To do so, you write instructions that Excel carries out. Automating a task by using VBA offers several advantages:

  • Excel always executes the task in exactly the same way (in most cases, consistency is a good thing).
  • Excel performs the task much faster than you could do it manually (unless, of course, you're Clark Kent).
  • Excel always performs the task without errors (which probably can't be said about you or me).
  • The task can be performed by someone who doesn't know anything about Excel.
  • You can do things in Excel that are otherwise impossible -- which can make you a very popular person around the office.

A personal anecdote

Excel programming has its own challenges and frustrations. One of my books, Excel 5 For Windows Power Programming Techniques, included a disk containing the examples I discuss in the book. I compressed these files so that they would fit on a single disk. Trying to be clever, I wrote a VBA program to expand the files and copy them to the appropriate directories. I spent a lot of time writing and debugging the code, and I tested it thoroughly on three different computers.

Imagine my surprise when I started receiving e-mail from readers who could not install the files. With a bit of sleuthing, I eventually discovered that the readers who were having the problem had all upgraded to Excel 5.0c (I developed my installation program using Excel 5.0a). It turns out that the Excel 5.0c upgrade featured a very subtle change that caused my macro to bomb. Because I'm not privy to Microsoft's plans, I couldn't anticipate this problem. Needless to say, this author suffered lots of embarrassment and had to e-mail corrections to hundreds of frustrated readers.

VBA disadvantages

It's only fair that I give equal time to listing the disadvantages (or potential disadvantages) of VBA:

  • You have to learn how to write programs in VBA (but that's why you bought this book, right?). Fortunately, it's not as difficult as you might expect.
  • Other people who need to use the VBA programs you develop must have their own copies of Excel. It would be nice if you could press a button that transforms your Excel/VBA application into a standalone program, but that isn't possible (and probably never will be).
  • Sometimes, things go wrong. In other words, you can't blindly assume that your VBA program will always work correctly under all circumstances. Welcome to the world of debugging.
  • VBA is a moving target. As you know, Microsoft is continually upgrading Excel. You may discover that VBA code you've written doesn't work properly with a future version of Excel. Take it from me, I discovered this the hard way, as detailed in the "A personal anecdote" sidebar.

VBA in a Nutshell

Following is a quick and dirty summary of what VBA is all about. Of course, I describe all this stuff in excruciating detail later in the book.

  • You perform actions in VBA by writing (or recording) code in a VBA module. You view and edit VBA modules using the Visual Basic Editor (VBE).
  • A VBA module consists of subroutine procedures. A subroutine procedure is basically computer code that performs some action on or with objects. The following example shows a simple subroutine called Test:

Sub SimpleProcedure( )

Sum = 1 + 1
MsgBox "The answer is " & Sum
End Sub
  • A VBA module can also have function procedures. A function procedure returns a single value, and you can call it from another VBA procedure or even use it as a function in a worksheet formula. Here's an example of a function named AddTwo:

Function AddTwo(arg1, arg2)

AddTwo = arg1 + arg2
End Function
  • VBA manipulates objects. Excel provides more than 100 objects that you can manipulate. Examples of objects include a workbook, a worksheet, a range on a worksheet, a chart, and a rectangle. You have many, many more objects at your disposal, and you can manipulate them using VBA code.
  • Objects are arranged in a hierarchy. Objects can act as containers for other objects. For example, Excel itself is an object called Application, and it contains other objects such as Workbook objects and CommandBar objects. The Workbook object can contain other objects, such as Worksheet objects and Chart objects. A Worksheet object can contain objects such as Range objects and PivotTable objects. The term object model refers to the arrangement of these objects (see Chapter 4 for details).
  • Objects of the same type form a collection. For example, the Worksheets collection consists of all the worksheets in a particular workbook. The Charts collection consists of all Chart objects in a workbook. Collections are themselves objects.
  • You refer to an object by specifying its position in the object hierarchy, using a dot as a separator. For example, you can refer to the workbook BOOK1.XLS as

Application.Workbooks("Book1.xls")
This refers to the workbook BOOK1.XLS in the Workbooks collection. The Workbooks collection is contained in the Application object (that is, Excel). Extending this to another level, you can refer to Sheet1 in Book1 as
Application.Workbooks("Book1.xls").Worksheets("Sheet1")
As shown in the following example, you can take this to still another level and refer to a specific cell:
Application.Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1")
  • If you omit specific references, Excel uses the active objects. If Book1 is the active workbook, you can simplify the preceding reference as follows:
Worksheets("Sheet1").Range("A1")
If you know that Sheet1 is the active sheet, you can simplify the reference even more:
Range("A1")
  • Objects have properties. You can think of a property as a setting for an object. For example, a Range object has such properties as Value and Name. A Chart object has such properties as HasTitle and Type. You can use VBA to determine object properties and to change properties.
  • You refer to a property of an object by combining the object's name with the property's name, separated by a period. For example, you can refer to the value in cell A1 on Sheet1 as follows:
Worksheets("Sheet1").Range("A1").Value
  • You can assign values to variables. To assign the value in cell A1 on Sheet1 to a variable called Interest, use the following VBA statement:
Interest = Worksheets("Sheet1").Range("A1").Value
  • Objects have methods. A method is an action Excel performs with an object. For example, one of the methods for a Range object is ClearContents. This method clears the contents of the range.
  • You specify a method by combining the object with the method, separated by a period. For example, the following statement clears the contents of cell A1:
Worksheets("Sheet1").Range("A1").ClearContents
  • VBA includes all the constructs of modern programming languages, including arrays and looping.

Believe it or not, the preceding list pretty much describes VBA in a nutshell. Now you just have to find out the details. That's the purpose of the rest of this book.

An Excursion into Versions

If you plan to develop VBA macros, you should have some understanding of Excel's history. I know you weren't expecting a history lesson, but this is important stuff.

Here's a list of all the major Excel for Windows versions that have seen the light of day, along with a few words about how they handle macros:

  • Excel 2: The original version of Excel for Windows was called version 2 (rather than 1) so that it would correspond to the Macintosh version. Excel 2 first appeared in 1987, and nobody uses it anymore, so you can pretty much forget that it ever existed.
  • Excel 3: Released in late 1990, this version features the XLM macro language. A few people live in a time warp and still use this version.
  • Excel 4: This version hit the streets in early 1992. It also uses the XLM macro language. A fair number of people still use this version. (They subscribe to the philosophy if it ain't broke, don't fix it.)
  • Excel 5: This one came out in early 1994. It was the first version to use VBA (but it also supported XLM). Many people continue to use this version because they are reluctant to move up to Windows 95.
  • Excel 95: Technically known as Excel 7 (there is no Excel 6), this version began shipping in the summer of 1995. It's a 32-bit version and requires Windows 95 or Windows NT. It has a few enhancements to VBA, and it supports the XLM language. Excel 95 uses the same file format as Excel 5.
  • Excel 97: This version (a.k.a. Excel 8) was born in January 1997. It requires Windows 95 or Windows NT. It has many enhancements, and features an entirely new interface for programming VBA macros. Excel 97 also uses a new file format (which cannot be opened by previous versions).

So what's the point of this mini history lesson? If you plan to distribute your Excel/VBA files to other users, it's vitally important that you understand which version of Excel they use. For example, if you develop a macro and save it in the Excel 97 file format, users running previous versions of Excel will not even be able to open the file.

Excel's other macro language

VBA made its debut with Excel 5, which was released in 1994. Previous versions of Excel incorporate a completely different macro language called XLM (which stands for Excel Macro). Most people who know about such things consider VBA to be a giant step forward. Microsoft, however, didn't want to abandon its loyal users by making them switch to VBA cold turkey. Therefore, Excel 5, Excel 95, and Excel 97 still support XLM macros.

Microsoft keeps XLM around mainly for compatibility purposes. People who wrote macros in earlier versions of Excel can still execute those macros when they run Excel 5 or Excel 95.

For the most part, you can just ignore the fact that another macro language lurks behind the scenes. This book covers VBA exclusively.

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)