Excel 2002 Vba Programmer's Reference

by Bullen, Stephen

ISBN13: 9781861005700

ISBN10: 1861005709

Format: Trade Paper

Pub. Date: 2001-09-26

Publisher(s): Peer Information

Other versions by this Author

List Price: ~~$39.99~~

Rent Book

Select for Price

Add to Cart

There was a problem. Please try again later.

New Book

We're Sorry
Sold Out

Used Book

We're Sorry
Sold Out

eBook

We're Sorry
Not Available

Buy from our Marketplace starting at $3.26

Summary

Written by active and popular members of the Excel community, this guide includes coverage of all the new features including smart tags, pivot tables, and web components. The book examines all the new XML capabilities that 2002 brings to the Excel world.

Introduction to Excel	p. 1
Early Spreadsheet Macros	p. 1
The XLM Macro Language	p. 2
Excel 5	p. 2
Excel 97	p. 3
Excel 2000	p. 4
Excel 2002	p. 4
Excel 2002 VBA Programmer's Reference	p. 5
Version Issues	p. 5
What You Need to Use this Book	p. 6
Conventions Used	p. 6
In Case of a Crisis...	p. 6
Feedback	p. 7
Questions?	p. 7
Primer in Excel VBA	p. 9
Using the Macro Recorder	p. 10
User Defined Functions	p. 24
The Excel Object Model	p. 28
The VBA Language	p. 36
The Application Object	p. 69
Globals	p. 69
The Active Properties	p. 70
Display Alerts	p. 71
Screen Updating	p. 71
Evaluate	p. 72
InputBox	p. 74
StatusBar	p. 75
SendKeys	p. 76
OnTime	p. 77
OnKey	p. 78
Worksheet Functions	p. 79
Caller	p. 80
Summary	p. 81
Workbooks and Worksheets	p. 83
The Workbooks Collection	p. 83
The Sheets Collection	p. 89
The Window Object	p. 94
Summary	p. 97
Using Ranges	p. 99
Activate and Select	p. 99
Range Property	p. 100
Offset Property	p. 108
Resize Property	p. 109
SpecialCells Method	p. 111
CurrentRegion Property	p. 113
End Property	p. 115
Summing a Range	p. 116
Columns and Rows Properties	p. 117
Union and Intersect Methods	p. 119
Empty Cells	p. 120
Transferring Values between Arrays and Ranges	p. 122
Summary	p. 127
Using Names	p. 129
Naming Ranges	p. 130
Special Names	p. 131
Storing Values in Names	p. 132
Storing Arrays	p. 133
Hiding Names	p. 134
Working with Named Ranges	p. 134
Searching for a Name	p. 135
Summary	p. 140
PivotTables	p. 143
Creating a PivotTable Report	p. 144
PivotFields	p. 149
Pivotitems	p. 153
PivotCharts	p. 158
External Data Sources	p. 159
Filtered Lists	p. 163
Structuring the Data	p. 163
Data Form	p. 164
AutoFilter	p. 165
Advanced Filter	p. 172
Summary	p. 174
Generating Charts	p. 177
Chart Sheets	p. 177
Embedded Charts	p. 180
Editing Data Series	p. 182
Defining Chart Series with Arrays	p. 185
Converting a Chart to use Arrays	p. 187
Determining the Ranges used in a Chart	p. 188
Chart Labels	p. 189
Summary	p. 190
Event Procedures	p. 193
Worksheet Events	p. 193
Chart Events	p. 196
Workbook Events	p. 198
Headers and Footers	p. 200
Summary	p. 201
Adding Controls	p. 203
The Toolbars	p. 203
ActiveX Controls	p. 204
Forms Toolbar Controls	p. 207
Dynamic ActiveX Controls	p. 210
Controls on Charts	p. 212
Summary	p. 214
Office Files and Folders	p. 217
FileSearch	p. 218
FileDialog	p. 228
Summary	p. 232
UserForms	p. 235
Displaying a UserForm	p. 235
Creating a UserForm	p. 237
Directly Accessing Controls in UserForms	p. 239
Stopping the Close Button	p. 243
Maintaining a Data List	p. 243
Modeless UserForms	p. 249
Variable UserForm Name	p. 251
Summary	p. 251
Command Bars	p. 253
Toolbars, Menu Bars, and Popups	p. 253
Excel's Built-In Command Bars	p. 256
Controls at All Levels	p. 258
Creating New Menus	p. 263
Passing Parameter Values	p. 265
Deleting a Menu	p. 266
Creating a Toolbar	p. 267
Pop-Up Menus	p. 270
Showing Pop-Up Command Bars	p. 272
Disabling Command Bars	p. 274
Disabling Shortcut Access to Customize	p. 275
Summary	p. 287
Class Modules	p. 289
Creating Your Own Objects	p. 290
Property Procedures	p. 291
Creating Collections	p. 292
Encapsulation	p. 295
Trapping Application Events	p. 295
Embedded Chart Events	p. 297
A Collection of UserForm Controls	p. 299
Referencing Classes Across Projects	p. 301
Summary	p. 302
Addins	p. 305
Hiding the Code	p. 305
Creating an Addin	p. 306
Closing Addins	p. 307
Code Changes	p. 308
Saving Changes	p. 308
Interface Changes	p. 309
Installing an Addin	p. 311
Addinlnstall Event	p. 312
Removing an Addin from the Addins List	p. 313
Summary	p. 314
Automation Addins and COM Addins	p. 317
Automation Addins	p. 317
COM Addins	p. 328
Summary	p. 339
SmartTags	p. 341
The FileName SmartTag	p. 342
Controlling SmartTags with VBA	p. 357
The Problems with SmartTags	p. 360
Summary	p. 361
Interacting with Other Office Applications	p. 363
Establishing the Connection	p. 364
Opening a Document in Word	p. 367
Accessing an Active Word Document	p. 368
Creating a New Word Document	p. 369
Access and DAO	p. 370
Access, Excel and, Outlook	p. 372
When is a Virus not a Virus?	p. 374
Summary	p. 376
Data Access with ADO	p. 379
An Introduction to Structured Query Language (SQL)	p. 379
An Overview of ADO	p. 384
Summary	p. 415
Excel and the Internet	p. 417
So What's all the Hype About?	p. 418
Using the Internet for Storing Workbooks	p. 418
Using the Internet as a Data Source	p. 419
Using the Internet to Publish Results	p. 423
Using the Internet as a Communication Channel	p. 426
Summary	p. 443
International Issues	p. 445
Changing Windows Regional Settings and the Office XP UI Language	p. 445
Responding to Regional Settings and the Windows Language	p. 446
Interacting with Excel	p. 452
Interacting with Users	p. 455
Excel 2002's International Options	p. 458
Features That Don't Play by the Rules	p. 461
Responding to Office XP Language Settings	p. 467
Some Helpful Functions	p. 473
Summary	p. 476
Programming the VBE	p. 479
Identifying VBE Objects in Code	p. 480
Starting Up	p. 483
Adding Menu Items to the VBE	p. 484
Displaying Built-In Dialogs, UserForms, and Messages	p. 495
Working with Code	p. 502
Working with UserForms	p. 506
Working with References	p. 511
COM Addins	p. 513
Summary	p. 513
Programming with the Windows API	p. 515
Anatomy of an API Call	p. 516
Interpreting C-Style Declarations	p. 517
Constants, Structures, Handles, and Classes	p. 520
What if Something Goes Wrong?	p. 523
Wrapping API Calls in Class Modules	p. 525
Some Example Classes	p. 530
Modifying UserForm Styles	p. 536
Resizable Userforms	p. 539
Other Examples	p. 547
Summary	p. 549
Excel 2002 Object Model	p. 551
Common Properties with Collections and Associated Objects	p. 551
Excel Objects and Their Properties, Methods and Events	p. 552
VBE Object Model	p. 849
Office XP Object Model	p. 877
Common Properties with Collections and Associated Objects	p. 877
Office Objects and Their Properties and Events	p. 878
Table of Contents provided by Syndetics. All Rights Reserved.

Excel 2002 Vba Programmer's Reference

Rent Book

New Book

Used Book

eBook

How Marketplace Works:

Summary

Table of Contents

Digital License