Home Page Icon
Home Page
Table of Contents for
Cover Page
Close
Cover Page
by Robert Rosenberg, Rob Bovey, John Green, Stephen Bullen, Paul T. Kimmel
Excel 2003 VBA Programmer's Reference
Cover Page
Title Page
Copyright
About the Authors
Credits
Acknowledgments
Paul Kimmel
Stephen Bullen
Rob Bovey
Robert Rosenberg
Contents
Introduction
Early Spreadsheet Macros
The XLM Macro Language
Excel 5
Excel 97
Excel 2000
Excel 2002
Excel 2003
What This Book Covers
Version Issues
What You Need to Use this Book
Conventions
Source Code
Errata
In Case of a Crisis…
p2p.wrox.com
1: Primer in Excel VBA
Using the Macro Recorder
User Defined Functions
The Excel Object Model
The VBA Language
Summary
2: Programming in the VBE
Writing Code
Managing a Project
Modifying Properties
Importing and Exporting Visual Basic Code
Editing
Managing Editor Options
Running and Debugging Code
Using Watches
Using the Object Browser
Summary
3: The Application Object
Globals
The Active Properties
Display Alerts
Screen Updating
Evaluate
InputBox
StatusBar
SendKeys
OnTime
OnKey
Worksheet Functions
Caller
Summary
4: Object-Oriented Theory and VBA
Comparing Classes and Interfaces
Defining Methods
Defining Fields
Defining Properties
Defining Events
Information Hiding and Access Modifiers
Encapsulation, Aggregation, and References
Summary
5: Event Procedures
Worksheet Events
Chart Events
Workbook Events
Headers and Footers
Summary
6: Class Modules
Creating Your Own Objects
Using Collections
Trapping Application Events
Embedded Chart Events
A Collection of UserForm Controls
Referencing Classes Across Projects
Summary
7: Writing Bulletproof Code
Using Debug.Print
Using Debug.Assert
A Brief Exemplar of PC Debugging
Creating Reusable Tools with the Debug Object
Raising Errors
Writing Error Handlers
Scaffolding
Writing to the EventLog
Summary
8: Debugging and Testing
Stepping Through Code
Using Breakpoints
Using Watches
Testing an Expression in the Immediate Window
Resources for Finding Definitions
Viewing the Call Stack
Asserting Application Invariants
Summary
9: UserForms
Displaying a UserForm
Creating a UserForm
Directly Accessing Controls in UserForms
Stopping the Close Button
Maintaining a Data List
Modeless UserForms
Summary
10: Adding Controls
The Toolbars
ActiveX Controls
Forms Toolbar Controls
Dynamic ActiveX Controls
Controls on Charts
Summary
11: Data Access with ADO
An Introduction to Structured Query Language (SQL)
An Overview of ADO
Using ADO with Non-Standard Data Sources
Summary
12: Creating and Using Add-ins
Hiding the Code
Converting the Workbook to an Add-in
Closing Add-ins
Code Changes
Saving Changes
Installing an Add-in
Add-in Install Event
Removing an Add-in from the Add-ins List
Summary
13: Automation Addins and COM Addins
Automation Addins
COM Addins
Summary
14: Customizing the VBE
Identifying VBE Objects in Code
Starting Up
Adding Menu Items to the VBE
Displaying Built-in Dialogs, UserForms, and Messages
Working with Code
Working with UserForms
Working with References
Summary
15: 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 DAO
Access, Excel and, Outlook
When Is a Virus not a Virus?
Summary
16: 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
Other Examples
Summary
17: International Issues
Changing Windows Regional Settings and the Office XP UI Language
Responding to Regional Settings and the Windows Language
Interacting with Excel
Interacting with Users
Excel 2003's International Options
Responding to Office XP Language Settings
Some Helpful Functions
Summary
18: Workbooks and Worksheets
Using the Workbooks Collection
The Sheets Collection
The Window Object
Summary
19: 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
20: Using Names
Naming Ranges
Special Names
Storing Values in Names
Storing Arrays
Hiding Names
Working with Named Ranges
Searching for a Name
Summary
21: Working with Lists
Creating a List
Resizing Lists
Totaling Rows
Converting Lists to a Range
Publishing Lists
Summary
22: PivotTables
Creating a PivotTable Report
PivotFields
PivotItems
PivotCharts
External Data Sources
Summary
23: Filtered Lists
Structuring the Data
Data Form
AutoFilter
Advanced Filter
Summary
24: Generating 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
25: Office Files and Folders
FileSearch
FileDialog
Summary
26: Command Bars
Toolbars, Menu Bars, and Popups
Excel's Built-In Commandbars
Controls at All Levels
Creating New Menus
Passing Parameter Values
Deleting a Menu
Creating a Toolbar
Pop-Up Menus
Showing Pop-Up Command Bars
Disabling Commandbars
Disabling Shortcut Access to Customize
Summary
27: SmartTags
SmartTag Enhancements
The FileName SmartTag
Controlling SmartTags with VBA
The Problems with SmartTags
Summary
28: Excel and the Internet
So What's all the Hype About?
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
29: XML and Excel
What Is XML?
What Is XSD?
What Is XMLSS?
Importing XML Data
BlackJack: Data Versatility
Summary
A: Excel 2003 Object Model
Common Properties with Collections and Associated Objects
Excel Objects and Their Properties, Methods and Events
ChartGroup Object and the ChartGroups Collection
ChartObject Object and the ChartObjects Collection
Comment Object and the Comments Collection
CubeField Object and the CubeFields Collection
CustomProperty Object and the CustomProperties Collection
CustomView Object and the CustomViews Collection
DataLabel Object and the DataLabels Collection
DiagramNode Object and the DiagramNodes Collection
Error Object and the Errors Collection
ErrorCheckingOptions Collection Object
FormatCondition Object and the FormatConditions Collection
B: VBE Object Model
Links Between the Excel and VBE Object Models
Common Properties and Methods
AddIn Object and AddIns Collection
AddIn Common Properties
CodeModule Object
CodeModule Common Properties
CodeModule Properties
CodeModule Methods
CodeModule Examples
CodePane Object and CodePanes Collection
CodePane Common Properties
CodePane Properties
CodePane Methods
CodePanes Collection Properties
CommandBarEvents Object
CommandBarEvents Events
CommandBarEvents Examples
Events Object
LinkedWindows Collection
LinkedWindows Collection Methods
Property Object and Properties Collection
Property Common Properties
Reference Object and References Collection
Reference Common Properties
References Collection Methods
References Collection Events
ReferencesEvents Object
ReferencesEvents Events
ReferencesEvents Examples
VBComponent Object and VBComponents Collection
VBE Object
VBProject Object and VBProjects Collection
VBProjects Collection Methods
Window Object and Windows Collection
Window Methods
Windows Collection Methods
Window Examples
C: Office 2003 Object Model
AnswerWizardFiles Collection Object
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Title Page
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset