Excel 2007 VBA Programmer's Reference E-Book

John Green

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch
• Veröffentlichungsjahr: 2011

Beschreibung

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 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, and so on. 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, the chapters devoted to accessing external databases detail techniques for accessing data in a range of formats.

The final four chapters of the book address the following advanced issues: linking Excel to the Internet, writing code for international compatibility, programming the Visual Basic Editor, and how to use the functions in the Win32 API (Windows 32-bit Application Programming Interface).

Leseprobe

Chapter 1: Primer in Excel VBA

Using the Macro Recorder

User-Defined Functions

The Excel Object Model

The VBA Language

Summary

Chapter 2: The Application Object

Globals

The Active Properties

Display Alerts

Screen Updating

Evaluate

InputBox

StatusBar

SendKeys

OnTime

OnKey

Worksheet Functions

Caller

Summary

Chapter 3: Workbooks and Worksheets

The Workbooks Collection

The Sheets Collection

The Window Object

Summary

Chapter 4: Using Ranges

Activate and Select

Range Property

Offset Property

Resize Property

SpecialCells Method

CurrentRegion Property

End Property

Summing a Range

Columns and Rows Properties

Union and Intersect Methods

Empty Cells

Transferring Values between Arrays and Ranges

Summary

Chapter 5: Using Names

Naming Ranges

Special Names

Storing Values in Names

Storing Arrays

Hiding Names

Working with Named Ranges

Searching for a Name

Summary

Chapter 6: Data Lists

Structuring the Data

Sorting a Range

Creating a Table

Sorting a Table

AutoFilter

Advanced Filter

Data Form

Summary

Chapter 7: PivotTables

Creating a PivotTable Report

PivotFields

PivotItems

PivotCharts

External Data Sources

Summary

Chapter 8: Charts

Chart Sheets

Embedded Charts

Editing Data Series

Defining Chart Series with Arrays

Converting a Chart to Use Arrays

Determining the Ranges Used in a Chart

Chart Labels

Summary

Chapter 9: Event Procedures

Worksheet Events

Chart Events

Workbook Events

Headers and Footers

Summary

Chapter 10: Adding Controls

Form and ActiveX Controls

ActiveX Controls

Forms Controls

Dynamic ActiveX Controls

Controls on Charts

Summary

Chapter 11: Text Files and File Dialog

Opening Text Files

Writing to Text Files

Reading Text Files

Writing to Text Files Using Print

FileDialog

Summary

Chapter 12: Working with XML and the Open XML File Formats

The Basics of Using XML Data in Excel

Using VBA to Program XML Processes

Using VBA to Program Open XML Files

Summary

Chapter 13: UserForms

Displaying a UserForm

Creating a UserForm

Directly Accessing Controls in UserForms

Stopping the Close Button

Maintaining a Data List

Modeless UserForms

Variable UserForm Name

Summary

Chapter 14: RibbonX

Overview

Prerequisites

Adding the Customizations

XML Structure

RibbonX and VBA

Control Types

Control Attributes

Control Callbacks

Managing Control Images

Other RibbonX Elements, Attributes, and Callbacks

Dynamic Controls

CommandBar Extensions for the Ribbon

RibbonX Limitations

Summary

Chapter 15: Command Bars

Toolbars, Menu Bars, and Popups

Excel's Built-in Command Bars

Controls at All Levels

Creating New Menus

Passing Parameter Values

Deleting a Menu

Creating a Toolbar

Popup Menus

Showing Popup Command Bars

Summary

Chapter 16: Class Modules

Creating Your Own Objects

Property Procedures

Creating Collections

Encapsulation

Trapping Application Events

Embedded Chart Events

A Collection of UserForm Controls

Referencing Classes Across Projects

Summary

Chapter 17: Add-ins

Hiding the Code

Creating an Add-in

Closing Add-ins

Code Changes

Saving Changes

Interface Changes

Installing an Add-in

AddinInstall Event

Removing an Add-in from the Add-ins List

Summary

Chapter 18: Automation Add-Ins and COM Add-Ins

Automation Add-Ins

COM Add-Ins

Summary

Chapter 19: Interacting with Other Office Applications

Establishing the Connection

Opening a Document in Word

Accessing an Active Word Document

Creating a New Word Document

Access and ADO

Access, Excel, and, Outlook

Better than Mail Merge

Summary

Chapter 20: Data Access with ADO

An Introduction to Structured Query Language (SQL)

An Overview of ADO

Summary

Chapter 21: Managing External Data

The External Data User Interface

The QueryTable and ListObject

The WorkbookConnection Object and the Connections Collection

External Data Security Settings

Summary

Chapter 22: The Trust Center and Document Security

The Trust Center

Automating Document Inspection

Summary

Chapter 23: Browsing OLAP Data Sources with Excel

Analyzing OLAP Data via Pivot Tables

Understanding the MDX behind OLAP-based Pivot Tables

Browsing OLAP Data Sources without Pivot Tables

Creating Offline Cubes

Summary

Chapter 24: Excel and the Internet

What Can the Internet Do for You?

Using the Internet for Storing Workbooks

Using the Internet as a Data Source

Using the Internet to Publish Results

Using the Internet as a Communication Channel

Summary

Chapter 25: International Issues

Changing Windows Regional Settings and the Office 2007 UI Language

Responding to Regional Settings and the Windows Language

Interacting with Excel

Interacting with Users

Excel 2007's International Options

Features That Don't Play by the Rules

The Range.Value, Range.Formula, and Range.FormulaArray Properties

The Range.AutoFilter Method

The Range.AdvancedFilter Method

The Application.Evaluate, Application.ConvertFormula, and

Responding to Office 2007 Language Settings

Some Helpful Functions

Summary

Chapter 26: Programming the VBE

Identifying VBE Objects in Code

Starting Up

Adding Menu Items to the VBE

Working with Workbooks

Working with Code

Working with UserForms

Working with References

COM Add-ins

Summary

Chapter 27: Programming with the Windows API

Anatomy of an API Call

Interpreting C-Style Declarations

Constants, Structures, Handles, and Classes

What If Something Goes Wrong?

Wrapping API Calls in Class Modules

Some Example Classes

Modifying UserForm Styles

Resizable UserForms

Summary

Appendix A: Excel 2007 Object Model

Appendix B: VBE Object Model

Appendix C: Office 2007 Object Model

Advertisement

Introduction

Chapter 1

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 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 Visual Basic and the Visual Basic Editor, you might want to skip straight to Chapter 2.

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. Specifically, this chapter covers the following topics:

The Excel macro recorderUser-defined functionsThe Excel object modelVBA 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. More formal terminology is covered in this chapter, but you will continue to see 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 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.

Don’t get the impression that we are dismissing the macro recorder. The macro recorder is one of the most valuable tools available to VBA programmers. It is the fastest way to generate working VBA code, but you must be prepared to apply your own knowledge of VBA to edit the recorded macro to obtain flexible and efficient code. A recurring theme in this book is recording an Excel macro and then showing how to adapt the recorded code.

In this chapter, you learn how to use the macro recorder and you see all the ways Excel provides to run your macros. You 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. I know this is rather a silly macro because you could do this easily with an AutoFill operation, but this example will serve to show you 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, because 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? Assume that you want the active cell to be A2, at the completion of the macro, so you 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 can’t see the Developer tab above the Ribbon, you will need to click the round Microsoft Office button that you can see in the top-left corner of the Excel screen shown in Figure 1-1. Click Excel Options at the bottom of the dialog box and select Personalize. Select the checkbox for Show Developer tab in the Ribbon and click OK. Now you can select the Developer section of the Ribbon and click Record Macro to display the Record Macro dialog box, shown in .