book

Excel 2003 VBA Programmer's Reference

Name: Excel 2003 VBA Programmer's Reference
ISBN: 9780764556609

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

July 2004

Beginner

1176 pages

27h 45m

English

Wrox

Read now

Unlock full access

Cover Page
Title Page
Copyright
About the Authors
Credits
Acknowledgments
Paul KimmelStephen BullenRob BoveyRobert Rosenberg
Contents
Introduction
Early Spreadsheet MacrosThe XLM Macro LanguageExcel 5Excel 97Excel 2000Excel 2002Excel 2003What This Book CoversVersion IssuesWhat You Need to Use this BookConventionsSource CodeErrataIn Case of a Crisis…p2p.wrox.com
1: Primer in Excel VBA
Using the Macro RecorderUser Defined FunctionsThe Excel Object ModelThe VBA LanguageSummary
2: Programming in the VBE
Writing CodeManaging a ProjectModifying PropertiesImporting and Exporting Visual Basic CodeEditingManaging Editor OptionsRunning and Debugging CodeUsing WatchesUsing the Object BrowserSummary

3: The Application Object
GlobalsThe Active PropertiesDisplay AlertsScreen UpdatingEvaluateInputBoxStatusBarSendKeysOnTimeOnKeyWorksheet FunctionsCallerSummary
4: Object-Oriented Theory and VBA
Comparing Classes and InterfacesDefining MethodsDefining FieldsDefining PropertiesDefining EventsInformation Hiding and Access ModifiersEncapsulation, Aggregation, and ReferencesSummary
5: Event Procedures
Worksheet EventsChart EventsWorkbook EventsHeaders and FootersSummary
6: Class Modules
Creating Your Own ObjectsUsing CollectionsTrapping Application EventsEmbedded Chart EventsA Collection of UserForm ControlsReferencing Classes Across ProjectsSummary
7: Writing Bulletproof Code
Using Debug.PrintUsing Debug.AssertA Brief Exemplar of PC DebuggingCreating Reusable Tools with the Debug ObjectRaising ErrorsWriting Error HandlersScaffoldingWriting to the EventLogSummary
8: Debugging and Testing
Stepping Through CodeUsing BreakpointsUsing WatchesTesting an Expression in the Immediate WindowResources for Finding DefinitionsViewing the Call StackAsserting Application InvariantsSummary
9: UserForms
Displaying a UserFormCreating a UserFormDirectly Accessing Controls in UserFormsStopping the Close ButtonMaintaining a Data ListModeless UserFormsSummary
10: Adding Controls
The ToolbarsActiveX ControlsForms Toolbar ControlsDynamic ActiveX ControlsControls on ChartsSummary
11: Data Access with ADO
An Introduction to Structured Query Language (SQL)An Overview of ADOUsing ADO with Non-Standard Data SourcesSummary
12: Creating and Using Add-ins
Hiding the CodeConverting the Workbook to an Add-inClosing Add-insCode ChangesSaving ChangesInstalling an Add-inAdd-in Install EventRemoving an Add-in from the Add-ins ListSummary
13: Automation Addins and COM Addins
Automation AddinsCOM AddinsSummary
14: Customizing the VBE
Identifying VBE Objects in CodeStarting UpAdding Menu Items to the VBEDisplaying Built-in Dialogs, UserForms, and MessagesWorking with CodeWorking with UserFormsWorking with ReferencesSummary
15: Interacting with Other Office Applications
Establishing the ConnectionOpening a Document in WordAccessing an Active Word DocumentCreating a New Word DocumentAccess and DAOAccess, Excel and, OutlookWhen Is a Virus not a Virus?Summary
16: Programming with the Windows API
Anatomy of an API CallInterpreting C-Style DeclarationsConstants, Structures, Handles, and ClassesWhat if Something Goes Wrong?Wrapping API Calls in Class ModulesSome Example ClassesModifying UserForm StylesResizable UserformsOther ExamplesSummary
17: International Issues
Changing Windows Regional Settings and the Office XP UI LanguageResponding to Regional Settings and the Windows LanguageInteracting with ExcelInteracting with UsersExcel 2003's International OptionsResponding to Office XP Language SettingsSome Helpful FunctionsSummary
18: Workbooks and Worksheets
Using the Workbooks CollectionThe Sheets CollectionThe Window ObjectSummary
19: Using Ranges
Activate and SelectRange PropertyOffset PropertyResize PropertySpecialCells MethodCurrentRegion PropertyEnd PropertySumming a RangeColumns and Rows PropertiesUnion and Intersect MethodsEmpty CellsTransferring Values between Arrays and RangesSummary
20: Using Names
Naming RangesSpecial NamesStoring Values in NamesStoring ArraysHiding NamesWorking with Named RangesSearching for a NameSummary
21: Working with Lists
Creating a ListResizing ListsTotaling RowsConverting Lists to a RangePublishing ListsSummary
22: PivotTables
Creating a PivotTable ReportPivotFieldsPivotItemsPivotChartsExternal Data SourcesSummary
23: Filtered Lists
Structuring the DataData FormAutoFilterAdvanced FilterSummary
24: Generating Charts
Chart SheetsEmbedded ChartsEditing Data SeriesDefining Chart Series with ArraysConverting a Chart to use ArraysDetermining the Ranges used in a ChartChart LabelsSummary
25: Office Files and Folders
FileSearchFileDialogSummary
26: Command Bars
Toolbars, Menu Bars, and PopupsExcel's Built-In CommandbarsControls at All LevelsCreating New MenusPassing Parameter ValuesDeleting a MenuCreating a ToolbarPop-Up MenusShowing Pop-Up Command BarsDisabling CommandbarsDisabling Shortcut Access to CustomizeSummary
27: SmartTags
SmartTag EnhancementsThe FileName SmartTagControlling SmartTags with VBAThe Problems with SmartTagsSummary
28: Excel and the Internet
So What's all the Hype About?Using the Internet for Storing WorkbooksUsing the Internet as a Data SourceUsing the Internet to Publish ResultsUsing the Internet as a Communication ChannelSummary
29: XML and Excel
What Is XML?What Is XSD?What Is XMLSS?Importing XML DataBlackJack: Data VersatilitySummary
A: Excel 2003 Object Model
Common Properties with Collections and Associated ObjectsExcel Objects and Their Properties, Methods and EventsChartGroup Object and the ChartGroups CollectionChartObject Object and the ChartObjects CollectionComment Object and the Comments CollectionCubeField Object and the CubeFields CollectionCustomProperty Object and the CustomProperties CollectionCustomView Object and the CustomViews CollectionDataLabel Object and the DataLabels CollectionDiagramNode Object and the DiagramNodes CollectionError Object and the Errors CollectionErrorCheckingOptions Collection ObjectFormatCondition Object and the FormatConditions Collection
B: VBE Object Model
Links Between the Excel and VBE Object ModelsCommon Properties and MethodsAddIn Object and AddIns CollectionAddIn Common PropertiesCodeModule ObjectCodeModule Common PropertiesCodeModule PropertiesCodeModule MethodsCodeModule ExamplesCodePane Object and CodePanes CollectionCodePane Common PropertiesCodePane PropertiesCodePane MethodsCodePanes Collection PropertiesCommandBarEvents ObjectCommandBarEvents EventsCommandBarEvents ExamplesEvents ObjectLinkedWindows CollectionLinkedWindows Collection MethodsProperty Object and Properties CollectionProperty Common PropertiesReference Object and References CollectionReference Common PropertiesReferences Collection MethodsReferences Collection EventsReferencesEvents ObjectReferencesEvents EventsReferencesEvents ExamplesVBComponent Object and VBComponents CollectionVBE ObjectVBProject Object and VBProjects CollectionVBProjects Collection MethodsWindow Object and Windows CollectionWindow MethodsWindows Collection MethodsWindow Examples
C: Office 2003 Object Model
AnswerWizardFiles Collection Object
Index

Overview

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Excel 2007 VBA Programming with XML and ASP

Publisher Resources

ISBN: 9780764556609Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills