Excel VBA

Section 1: Understanding the BASICs - Page 37
Section 1: Understanding the BASICs - Page 38
Lesson 1: Introducing VBA - Page 39
A Brief History of VBA - Page 40
What VBA Can Do for You - Page 41
Controlling Other Office Applications from Excel - Page 42
Liabilities of VBA - Page 43
Try It - Page 44
Lesson 2: Getting Started with Macros - Page 45
Lesson 2: Getting Started with Macros - Page 46
Lesson 2: Getting Started with Macros - Page 47
Using the Macro Recorder - Page 48
Using the Macro Recorder - Page 49
Using the Macro Recorder - Page 50
Using the Macro Recorder - Page 51
Running a Macro - Page 52
Shortcut Key - Page 53
Try It - Page 54
Lesson 3: Introducing the - Page 55
How To Get Into the VBE - Page 56
The Project Explorer Window - Page 57
The Properties Window - Page 58
Using the Object Browser - Page 59
Exiting the VBE - Page 60
Try It - Page 61
Try It - Page 62
Lesson 4: Working in the VBE - Page 63
Macros and Modules - Page 64
Understanding the Code - Page 65
Editing a Macro with Comments and Improvements to the Code - Page 66
Editing a Macro with Comments and Improvements to the Code - Page 67
Editing a Macro with Comments and Improvements to the Code - Page 68
Deleting a Macro - Page 69
Renaming a Module - Page 70
Renaming a Module - Page 71
Deleting a Module - Page 72
Try It - Page 73
Try It - Page 74
Try It - Page 75
Try It - Page 76
Section 2: Diving Deeper into VBA - Page 77
Section 2: Diving Deeper into VBA - Page 78
Lesson 5: Object-oriented - Page 79
The Object Model - Page 80
Properties - Page 81
Methods - Page 82
Try It - Page 83
Try It - Page 84
Lesson 6: Variables, Data Types, - Page 85
Assigning Values to Variables - Page 86
Data Types - Page 87
Data Types - Page 88
Declaring a Variable for Dates and Times - Page 89
Forcing Variable Declaration - Page 90
Forcing Variable Declaration - Page 91
Understanding a Variable’s Scope - Page 92
Application Level - Page 93
Choosing the Scope and Lifetime of Your Constants - Page 94
Choosing the Scope and Lifetime of Your Constants - Page 95
Choosing the Scope and Lifetime of Your Constants - Page 96
Lesson 7: Understanding Objects and Collections - Page 97
Worksheets - Page 98
Cells and Ranges - Page 99
SpecialCells - Page 100
Try It - Page 101
Try It - Page 102
Try It - Page 103
Try It - Page 104
Lesson 8: Making Decisions with VBA - Page 105
AND - Page 106
NOT - Page 107
Choosing Between This or That - Page 108
If…Then…Else - Page 109
If…Then…ElseIf - Page 110
If…Then…ElseIf - Page 111
Getting Users to Make Decisions - Page 112
Input Boxes - Page 113
Try It - Page 114
Try It - Page 115
Try It - Page 116
Try It - Page 117
Try It - Page 118
Section 3: Beyond the Macro Recorder: Writing Your Own Code - Page 119
Section 3: Beyond the Macro Recorder: Writing Your Own Code - Page 120
Lesson 9: Repeating Actions with Loops - Page 121
Types of Loops - Page 122
For…Next - Page 123
For…Each…Next - Page 124
Exiting a For… Loop - Page 125
Looping In Reverse with Step - Page 126
Do…While - Page 127
Do…While - Page 128
Do…Loop…While - Page 129
Do…Loop…Until - Page 130
Try It - Page 131
Lesson Requirements - Page 132
Lesson Requirements - Page 133
Lesson Requirements - Page 134
Lesson 10: Working with Arrays - Page 135
Lesson 10: Working with Arrays - Page 136
What Arrays Can Do for You - Page 137
Declaring Arrays - Page 138
The Option Base Statement - Page 139
Boundaries in Arrays - Page 140
Declaring Dynamic Arrays with ReDim and Preserve - Page 141
Declaring Dynamic Arrays with ReDim and Preserve - Page 142
Try It - Page 143
Try It - Page 144
Try It - Page 145
Try It - Page 146
Lesson 11: Automating Procedures - Page 147
Worksheet Events — ​an Overview - Page 148
Worksheet Events — ​an Overview - Page 149
Enabling and Disabling Events - Page 150
Examples of Common Worksheet Events - Page 151
Worksheet_SelectionChange Event - Page 152
Worksheet_BeforeRightClick Event - Page 153
Worksheet_Deactivate Event - Page 154
Worksheet_PivotTableUpdate Event - Page 155
Worksheet_PivotTableUpdate Event - Page 156
Worksheet_PivotTableUpdate Event - Page 157
Worksheet_PivotTableUpdate Event - Page 158
Lesson 12: Automating Procedures with Workbook Events - Page 159
Lesson 12: Automating Procedures with Workbook Events - Page 160
Entering Workbook Event Code - Page 161
Examples of Common Workbook Events - Page 162
Workbook_BeforeClose Event - Page 163
Workbook_Deactivate Event - Page 164
Workbook_SheetBeforeDoubleClick Event - Page 165
Workbook_SheetPivotTableUpdate Event - Page 166
Workbook_SheetActivate Event - Page 167
Try It - Page 168
Try It - Page 169
Try It - Page 170
Lesson 13: Using Embedded Controls - Page 171
The Forms Toolbar - Page 172
The Forms Toolbar - Page 173
The Forms Toolbar - Page 174
The Forms Toolbar - Page 175
The Control Toolbox - Page 176
The Control Toolbox - Page 177
The Control Toolbox - Page 178
The Control Toolbox - Page 179
Try It - Page 180
Try It - Page 181
Try It - Page 182
Try It - Page 183
Try It - Page 184
Try It - Page 185
Try It - Page 186
Lesson 14: Programming Charts - Page 187
Adding a Chart to a Chart Sheet - Page 188
Adding a Chart to a Chart Sheet - Page 189
Adding an Embedded Chart to a Worksheet - Page 190
Moving a Chart - Page 191
Moving a Chart - Page 192
Looping through All Embedded Charts - Page 193
Deleting Charts - Page 194
Renaming a Chart - Page 195
Try It - Page 196
Try It - Page 197
Try It - Page 198
Lesson 15: Programming PivotTables - Page 199
Lesson 15: Programming PivotTables - Page 200
Lesson 15: Programming PivotTables - Page 201
Lesson 15: Programming PivotTables - Page 202
Hiding the PivotTable Field List - Page 203
Formatting Numbers in the Values Area - Page 204
Formatting Numbers in the Values Area - Page 205
Why It’s Called a PivotTable - Page 206
Creating a PivotChart - Page 207
Creating a PivotChart - Page 208
Understanding PivotCaches - Page 209
Understanding PivotCaches - Page 210
Understanding PivotCaches - Page 211
Manipulating PivotFields in VBA - Page 212
Manipulating PivotItems with VBA - Page 213
Try It - Page 214
Step-by-Step - Page 215
Step-by-Step - Page 216
Step-by-Step - Page 217
Step-by-Step - Page 218
Lesson 16: User Defined Functions - Page 219
Characteristics of User Defined Functions - Page 220
UDF Examples That Solve Common Tasks - Page 221
UDF Examples That Solve Common Tasks - Page 222
UDF Examples That Solve Common Tasks - Page 223
Volatile Functions - Page 224
The Name of the Active Worksheet and Workbook - Page 225
UDFs with Conditional Formatting - Page 226
Adding a Description to the Insert Function Dialog - Page 227
Adding a Description to the Insert Function Dialog - Page 228
Try It - Page 229
Try It - Page 230
Lesson 17: Debugging Your Code - Page 231
What Causes Errors? - Page 232
What Causes Errors? - Page 233
Weapons of Mass Debugging - Page 234
Weapons of Mass Debugging - Page 235
Weapons of Mass Debugging - Page 236
Weapons of Mass Debugging - Page 237
Weapons of Mass Debugging - Page 238
Weapons of Mass Debugging - Page 239
Weapons of Mass Debugging - Page 240
Weapons of Mass Debugging - Page 241
Weapons of Mass Debugging - Page 242
Trapping Errors - Page 243
Bypassing Errors - Page 244
Bypassing Errors - Page 245
Try It - Page 246
Try It - Page 247
Try It - Page 248
Section 4: Advanced Programming Techniques - Page 249
Section 4: Advanced Programming Techniques - Page 250
Lesson 18: Creating UserForms - Page 251
Creating a UserForm - Page 252
Creating a UserForm - Page 253
Designing a UserForm - Page 254
Adding Controls to a UserForm - Page 255
Adding Controls to a UserForm - Page 256
Adding Controls to a UserForm - Page 257
Adding Controls to a UserForm - Page 258
Adding Controls to a UserForm - Page 259
Adding Controls to a UserForm - Page 260
Showing a UserForm - Page 261
Closing a UserForm - Page 262
Hiding a UserForm - Page 263
Try It - Page 264
Try It - Page 265
Try It - Page 266
Lesson 19: UserForm Controls and Their Functions - Page 267
CommandButtons - Page 268
CommandButtons - Page 269
TextBoxes - Page 270
TextBoxes - Page 271
ListBoxes - Page 272
ListBoxes - Page 273
ComboBoxes - Page 274
ComboBoxes - Page 275
CheckBoxes - Page 276
OptionButtons - Page 277
OptionButtons - Page 278
Frames - Page 279
Frames - Page 280
MultiPages - Page 281
Try It - Page 282
Try It - Page 283
Try It - Page 284
Lesson 20: Advanced UserForms - Page 285
Modal Versus Modeless - Page 286
Modal Versus Modeless - Page 287
Maximizing Your UserForm’s Size - Page 288
Unloading a UserForm Automatically - Page 289
Unloading a UserForm Automatically - Page 290
Populating ListBoxes and ComboBoxes with Unique Items - Page 291
Populating ListBoxes and ComboBoxes with Unique Items - Page 292
Populating ListBoxes and ComboBoxes with Unique Items - Page 293
Display a Real-Time Chart in a UserForm - Page 294
Try It - Page 295
Try It - Page 296
Try It - Page 297
Try It - Page 298
Lesson 21: Class Modules - Page 299
What Is a Class Module? - Page 300
Creating Your Own Objects - Page 301
An Important Benefit of Class Modules - Page 302
An Important Benefit of Class Modules - Page 303
Creating Collections - Page 304
Class Modules for Embedded Objects - Page 305
Class Modules for Embedded Objects - Page 306
Class Modules for Embedded Objects - Page 307
Try It - Page 308
Try It - Page 309
Try It - Page 310
Try It - Page 311
Try It - Page 312
Try It - Page 313
Try It - Page 314
Lesson 22: Add-Ins - Page 315
Creating an Add-In - Page 316
Creating an Add-In - Page 317
Creating an Add-In - Page 318
Creating an Add-In - Page 319
Converting a File to an Add-In - Page 320
Converting a File to an Add-In - Page 321
Installing an Add-In - Page 322
Installing an Add-In - Page 323
Creating a User Interface for Your Add-In - Page 324
Creating a User Interface for Your Add-In - Page 325
Changing the Add-In’s Code - Page 326
Removing an Add-In from the Add-Ins List - Page 327
Removing an Add-In from the Add-Ins List - Page 328
Removing an Add-In from the Add-Ins List - Page 329
Removing an Add-In from the Add-Ins List - Page 330
Lesson 23: Managing External Data - Page 331
Lesson 23: Managing External Data - Page 332
Lesson 23: Managing External Data - Page 333
Lesson 23: Managing External Data - Page 334
Creating a QueryTable for Access - Page 335
Creating a QueryTable for Access - Page 336
Using Text Files to Store External Data - Page 337
Using Text Files to Store External Data - Page 338
Using Text Files to Store External Data - Page 339
Try It - Page 340
Try It - Page 341
Try It - Page 342
Lesson 24: Data Access with - Page 343
Lesson 24: Data Access with - Page 344
The Connection Object - Page 345
The Command Object - Page 346
The SELECT Statement - Page 347
The INSERT Statement - Page 348
The DELETE Statement - Page 349
The DELETE Statement - Page 350
Lesson 25: Not Gone, Not Forgotten - Page 351
What Does a Dialog Sheet Look Like? - Page 352
What Does a Dialog Sheet Look Like? - Page 353
Option to Show Message Only Once - Page 354
Option to Show Message Only Once - Page 355
Option to Show Message Only Once - Page 356
Using XLM Get.Cell Functions - Page 357
Using the SendKeys Method - Page 358
Try It - Page 359
Try It - Page 360
Section 5: Interacting with Other - Page 361
Section 5: Interacting with Other - Page 362
Lesson 26: Overview of Office Automation from Excel - Page 363
Understanding Office Automation - Page 364
Late Binding - Page 365
Which One Is Better? - Page 366
Which One Is Better? - Page 367
Which One Is Better? - Page 368
Lesson 27: Working with Word from Excel - Page 369
Activating the Word Application - Page 370
Activating the Word Application - Page 371
Creating a New Word Document - Page 372
Copying an Excel Range to a Word Document - Page 373
Importing a Word Document to Excel - Page 374
Try It - Page 375
Try It - Page 376
Try It - Page 377
Try It - Page 378
Lesson 28: Working with Outlook from Excel - Page 379
Composing an E‑mail in Outlook from Excel - Page 380
Transferring an Excel Range to the Body of Your E‑mail - Page 381
Putting It All Together - Page 382
Putting It All Together - Page 383
E‑mailing a Single Worksheet - Page 384
E‑mailing a Single Worksheet - Page 385
E‑mailing a Single Worksheet - Page 386
E‑mailing a Single Worksheet - Page 387
E‑mailing a Single Worksheet - Page 388
Lesson 29: Working with Access from Excel - Page 389
Lesson 29: Working with Access from Excel - Page 390
Lesson 29: Working with Access from Excel - Page 391
Exporting an Access Table to an Excel Spreadsheet - Page 392
Exporting an Access Table to an Excel Spreadsheet - Page 393
Creating a New Table in Access - Page 394
Try It - Page 395
Step-by-Step - Page 396
Step-by-Step - Page 397
Step-by-Step - Page 398
Lesson 30: Working with PowerPoint - Page 399
Copying a Worksheet Range to a PowerPoint Slide - Page 400
Copying Chart Sheets to PowerPoint Slides - Page 401
Copying Chart Sheets to PowerPoint Slides - Page 402
Running a PowerPoint Presentation from Excel - Page 403
Try It - Page 404
Try It - Page 405
Try It - Page 406