Excel Programming Weekend Crash Course

Peter G. Aitken9780764540622, 0764540629

* Thirty clearly defined lessons take the reader from understanding the parts of an Excel application into building applications to work with data, formulas, charts, and the enhanced XML capabilities of the new Excel “X” * Book is designed to teach the core concepts of Excel over a weekend or in just fifteen hours, with each session being thirty minutes * Applicable to Excel 2000, Excel 2002, and the latest release, Excel “X” * Helps Excel power users in fields such as accounting, finance, operations management, and market research to begin automating data manipulation in Excel quickly, so they can handle real-world projects * A how-to guide to using Excel’s programmability to create custom data-processing and analysis solutions * Covers security, debugging, and error handling * Companion Web site includes sample files, projects, and test enginewith self-assessment exam”

---

Table of contents :
Team DDU……Page 1
Cover……Page 4
Contents at a Glance……Page 14
Acknowledgments……Page 12
Preface……Page 8
Credits……Page 7
About the Author……Page 6
Contents……Page 16
FRIDAY……Page 27
PART I–Friday Evening……Page 29
Advantages of Programming……Page 30
Programming Fundamentals……Page 31
The VBA Language……Page 32
Objects……Page 33
Components and Automation……Page 34
Designing Your Custom Application……Page 35
Creating and Naming the Program……Page 36
Writing the Code……Page 37
Running the Program……Page 39
Code and Project Organization……Page 42
Importing and Exporting Modules……Page 44
Editing Tools……Page 45
The Property Window……Page 47
The VBA Editor Menus……Page 48
Using Macros in Programming……Page 49
Recording a Macro……Page 50
Cell References in Macros……Page 51
Online Help……Page 52
Understanding Properties and Methods……Page 56
The Importance of Object References……Page 57
Working with Collections……Page 58
The Object Hierarchy……Page 60
Creating and Opening Workbooks……Page 61
Saving and Closing Workbooks……Page 62
Referencing Workbooks……Page 63
E-Mailing a Workbook……Page 64
Other Workbook Methods and Properties……Page 65
Adding and Deleting Worksheets……Page 66
Referencing Worksheets……Page 68
Copying and Moving Worksheets……Page 69
VBA Syntax Fundamentals……Page 72
Comments……Page 73
Employing Constants……Page 74
Declaring and Using Variables……Page 75
Numeric Variables……Page 76
String Variables……Page 77
Object Variables……Page 78
The Variant Type……Page 79
Static Arrays……Page 80
Dynamic Arrays……Page 81
User-Defined Types……Page 84
Enumerations……Page 85
Understanding Variable Scope……Page 86
SATURDAY……Page 91
PART II–Saturday Morning……Page 93
The Assignment Operator……Page 94
Numerical Operators……Page 95
Logical Operators……Page 96
Comparison Operators……Page 97
Operator Precedence……Page 99
SESSION 6–Control Constructs……Page 102
The IfThen Statement……Page 103
The Select Case Statement……Page 105
The DoLoop Statement……Page 106
The ForNext Statement……Page 108
The For EachNext Statement……Page 109
The Goto Statement……Page 110
SESSION 7–Procedures and Modules……Page 112
Sub Procedures……Page 113
Optional Arguments……Page 114
ByVal and ByRef Arguments……Page 115
Calling Procedures……Page 116
Argument Type Checking……Page 117
Function Procedures……Page 118
Variables in Procedures……Page 119
Storing Procedures……Page 120
The Date Data Type……Page 122
Date/Time Values……Page 123
Date Calculations……Page 124
Date and Time Details……Page 125
Formatting Date/Time Values……Page 128
The MsgBox Function……Page 132
InputBox Function……Page 135
Searching for Text……Page 136
The StrConv Function……Page 137
The Val Function……Page 138
Working with ASCII Values……Page 139
The Asc, AscB, and AscW Functions……Page 140
The Left and Right Functions……Page 142
The Mid Statement……Page 143
Other String Functions……Page 144
The Range Object……Page 146
Manipulating Ranges……Page 147
Relative Ranges……Page 148
Other Range References……Page 152
Working with Comments……Page 153
Reading Data from Ranges……Page 155
Naming Ranges……Page 157
The Selection Property……Page 159
PART II–Saturday Morning Part Review……Page 162
PART III -Saturday Afternoon……Page 165
Referencing Cells with the Cells Property……Page 166
Referencing by Row and Column……Page 167
The SpecialCells Method……Page 170
Manipulating Columns and Rows……Page 172
Adding and Deleting Rows and Columns……Page 174
Cell References in Formulas……Page 176
Absolute Cell References……Page 177
Named Range References……Page 179
Referencing Cells in Other Worksheets and Workbooks……Page 180
Logical Operators……Page 181
Avoiding Circular References……Page 182
Controlling Formula Calculation……Page 183
Excel’s Built-in Functions……Page 186
Using Excel Functions in Formulas……Page 187
Financial Functions……Page 188
Date and Time Functions……Page 191
Math and Trig Functions……Page 192
Text Functions……Page 193
The WorksheetFunction Object……Page 194
Cell Formatting……Page 196
Number Formatting……Page 198
Font Formatting……Page 200
Alignment and Orientation of Data……Page 201
Cell Borders……Page 203
Cell Backgrounds……Page 206
Changing Row and Column Size……Page 209
Finding Data……Page 212
The Find Method……Page 213
The FindNext and FindPrevious Methods……Page 214
Replacing Data……Page 217
Displaying and Hiding Toolbars……Page 220
Creating a New Toolbar……Page 222
Adding and Removing Toolbar Buttons……Page 223
Running Programs from Toolbar Buttons……Page 224
Distributing Toolbars……Page 227
Hiding and Displaying Toolbars in VBA Code……Page 228
PART IV–Saturday Evening……Page 233
Embedded Charts and Chart Sheets……Page 234
Chart Sheets……Page 236
Identifying Data to Be Plotted……Page 237
Specifying Chart Type……Page 239
Displaying Chart Titles……Page 241
Chart Axis Titles……Page 242
Working with Fonts in a Chart……Page 244
The ChartWizard Method……Page 247
Naming and Referencing Charts……Page 250
Locking Charts……Page 251
The ChartObject Object……Page 252
Using Scatter Charts……Page 256
Scatter Chart Types……Page 258
Changing Axis Range……Page 259
Printing Charts……Page 260
Overview of User Forms……Page 264
Form Designer Basics……Page 265
Designing the Interface……Page 266
Setting Properties……Page 267
Appearance Properties……Page 268
Behavior and Position Properties……Page 269
Form Methods……Page 270
Displaying, Using, and Hiding Forms……Page 271
A Simple User Form Example……Page 272
A Summary of Controls……Page 276
Common Control Properties……Page 277
The ComboBox Control……Page 278
The CommandButton Control……Page 281
The OptionButton Control……Page 283
The RefEdit Control……Page 285
The TextBox Control……Page 287
The ToggleButton Control……Page 288
SUNDAY……Page 293
PART V–Sunday Morning……Page 295
Using Control Events……Page 296
The Form Grid……Page 298
Control Placement and Alignment Tools……Page 299
Focus and the Tab Order……Page 303
Planning the Project……Page 308
Part 2: Designing the Form……Page 310
Part 3: Writing the Initialization Code……Page 311
Part 4: Restricting Zip Code Entry to Digits……Page 312
Part 5: Writing the Data Validation Code……Page 313
Part 6: Completing the Project……Page 314
Part 7: Testing the Project……Page 317
Event Categories……Page 320
Event Handler Code……Page 321
Enabling and Disabling Events……Page 322
Workbook Events……Page 323
The Open Event……Page 324
Worksheet Events……Page 325
Using the Change Event for Data Validation……Page 326
Application Events……Page 328
Writing Application Event Procedures……Page 329
The WorkbookBeforeClose Event……Page 330
Other Events……Page 331
The OnTime Event……Page 332
The OnKey Event……Page 333
Workbook Protection……Page 336
Worksheet Protection……Page 337
VBA Code and Worksheet Protection……Page 339
Protecting Your VBA Code……Page 340
Macro Security……Page 341
Using Digital Certificates to Sign Macros……Page 342
What Are Bugs?……Page 346
Avoiding Bugs……Page 347
Breakpoints……Page 348
Step Commands……Page 349
Using Watches……Page 350
Distributing an Application……Page 352
Class Fundamentals……Page 354
Instantiating Classes……Page 355
Creating Property Procedures……Page 356
The Property Variable……Page 357
Connecting the Property to the Property Procedures……Page 358
Array Properties……Page 359
Read-Only Properties……Page 360
Property Validation……Page 361
A Class Demonstration……Page 362
PART VI–Sunday Afternoon……Page 371
What’s a Runtime Error?……Page 372
The Causes of Errors……Page 373
Preventing Errors……Page 374
Trapping Errors……Page 375
The Err Object……Page 376
Error-Handling Code……Page 377
Some Error-Handling Examples……Page 378
Notifying the User of an Error……Page 379
Using an Error as a Programming Tool……Page 380
Databases and Excel……Page 382
Database Fundamentals……Page 383
Sorting Data……Page 384
Filtering Data……Page 385
Data Entry Forms……Page 388
Database Functions……Page 389
Add-Ins versus Workbooks……Page 392
Creating an Add-In……Page 394
Using Excel’s Add-In Manager……Page 395
Functions in Add-Ins……Page 396
Using VBA to Manipulate Add-Ins……Page 397
An Add-In Demonstration……Page 398
Code the Function……Page 399
Create the Toolbar……Page 400
Testing the Add-In……Page 402
Online Help for Excel……Page 404
Putting Help in the Worksheet……Page 405
Putting Help in a Separate Worksheet……Page 406
HTML Help……Page 409
Web-Based Help……Page 411
Friday Evening Review Answers……Page 416
Saturday Morning Review Answers……Page 417
Saturday Afternoon Review Answers……Page 418
Saturday Evening Review Answers……Page 419
Sunday Morning Review Answers……Page 420
Sunday Afternoon Review Answers……Page 421
Troubleshooting……Page 422
Index……Page 424