Excel VBA 24-Hour Trainer

Tom Urtis is a Microsoft Office developer, programmer, instructor, and Microsoft Excel MVP. Tom is founder of Atlas Programming Management (www.atlaspm.com), a Silicon Valley-based Office business solutions company specializing in Excel, providing consulting, development, training, and support of fully customized Office programs for a diverse international clientele.

Introduction xxvii Section I: Understanding the BASI Cs Lesson 1: Introduc ing VBA 3 What Is VBA? 3 A Brief History of VBA 4 What VBA Can Do for You 5 Automating a Recurring Task 5 Automating a Repetitive Task 5 Running a Macro Automatically if Another Action Takes Place 5 Creating Your Own Worksheet Functions 5 Simplifying the Workbook s Look and Feel for Other Users 5 Controlling Other Office Applications from Excel 6 Liabilities of VBA 7 Try It 8 Lesson 2: Getting Started with Macros 9 Composing Your First Macro 9 Accessing the VBA Environment 9 Using the Macro Recorder 12 Running a Macro 16 The Macro Dialog Box 16 Shortcut Key 17 Try It 17 Lesson Requirements 18 Step-by-Step 18 Lesson 3: Introduc ing the Visual Basic Editor 19 What Is the VBE? 19 How To Get Into the VBE 20 Understanding the VBE 20 The Project Explorer Window 21 The Code Window 21 The Properties Window 22 The Immediate Window 22 Understanding Modules 22 Using the Object Browser 23 Exiting the VBE 24 Try It 25 Lesson 4: Working in the VBE 27 Toolbars in the VBE 27 Macros and Modules 28 Locating Your Macros 28 Understanding the Code 29 Editing a Macro with Comments and Improvements to the Code 30 Deleting a Macro 33 Inserting a Module 33 Renaming a Module 34 Deleting a Module 36 Locking and Protecting the VBE 36 Try It 37 Lesson Requirements 37 Step-by-Step 37 Section II : Diving Deeper into VBA Lesson 5: Ob ject-oriented Programm ing A n Ov erview 43 What Object-Oriented Programming Means 43 The Object Model 44 Properties 45 Methods 46 Collections 46 Try It 47 Lesson 6: Variabl es, Data Types, and Constants 49 What Is a Variable? 49 Assigning Values to Variables 50 Why You Need Variables 50 Data Types 51 Understanding the Different Data Types 51 Declaring a Variable for Dates and Times 53 Declaring a Variable with the Proper Data Type 53 Forcing Variable Declaration 54 Understanding a Variable s Scope 56 Local Macro Level Only 56 Module Level 56 Application Level 57 Constants 57 Choosing the Scope and Lifetime of Your Constants 58 Try It 58 Lesson Requirements 58 Step-by-Step 58 Understanding Lesson 7: Ob jects and Coll ections 61 Workbooks 61 Worksheets 62 Cells and Ranges 63 SpecialCells 64 Try It 65 Lesson Requirements 65 Step-by-Step 65 Lesson 8: Making Decisions with VBA 69 Understanding Logical Operators 69 AND 70 OR 70 NOT 71 Choosing Between This or That 72 If Then 72 If Then Else 73 If Then ElseIf 74 Select Case 74 Getting Users to Make Decisions 76 Message Boxes 76 Input Boxes 77 Try It 78 Lesson Requirements 78 Step-by-Step 78 Section III : the macro recorder: writing your Own Code Lesson 9: Repeating Ac tions with Loops 85 What Is a Loop? 85 Types of Loops 86 For Next 87 For Each Next 88 Exiting a For Loop 89 Looping In Reverse with Step 90 Do While 91 Do Until 91 Do Loop While 93 Do Loop Until 94 While Wend 94 Nesting Loops 94 Try It 95 Lesson Requirements 96 Step-by-Step 96 Lesson 10: Working with Arrays 99 What Is an Array? 99 What Arrays Can Do for You 101 Declaring Arrays 102 The Option Base Statement 103 Boundaries in Arrays 104 Declaring Arrays with Fixed Elements 104 Declaring Dynamic Arrays with ReDim and Preserve 105 Try It 107 Lesson Requirements 107 Step-by-Step 107 Lesson 11: Au tomating Procedures with Worksheet Ev ents 111 What Is an Event ? 111 Worksheet Events an Overview 112 Where Does the Worksheet Event Code Go? 112 Enabling and Disabling Events 114 Examples of Common Worksheet Events 115 Worksheet-Change Event 115 Worksheet-SelectionChange Event 116 Worksheet-BeforeDoubleClick Event 116 Worksheet-BeforeRightClick Event 117 Worksheet-FollowHyperlink Event 117 Worksheet-Activate Event 117 Worksheet-Deactivate Event 118 Worksheet-Calculate Event 118 Worksheet-PivotTableUpdate Event 119 Try It 119 Lesson Requirements 119 Step-by-Step 119 Au tomating Procedures w Lesson 12: ith Workbook Ev ents 123 Workbook Events An Overview 123 Where Does the Workbook Event Code Go? 123 Entering Workbook Event Code 125 Examples of Common Workbook Events 126 Workbook-Open Event 126 Workbook-BeforeClose Event 127 Workbook-Activate Event 127 Workbook-Deactivate Event 128 Workbook-SheetChange Event 128 Workbook-SheetSelectionChange Event 128 Workbook-SheetBeforeDoubleClick Event 129 Workbook-SheetBeforeRightClick Event 129 Workbook-SheetPivotTableUpdate Event 130 Workbook-NewSheet Event 130 Workbook-BeforePrint Event 130 Workbook-SheetActivate Event 131 Workbook-SheetDeactivate Event 131 Workbook-BeforeSave Event 131 Try It 132 Lesson Requirements 132 Step-by-Step 132 Lesson 13: Emb edded Controls 135 Working with Forms Controls and ActiveX Controls 135 The Forms Toolbar 136 The Control Toolbox 140 Try It 144 Lesson Requirements 144 Step-by-Step 144 Lesson 14: Programm ing Charts 151 Adding a Chart to a Chart Sheet 152 Adding an Embedded Chart to a Worksheet 154 Moving a Chart 155 Looping Through All Embedded Charts 157 Deleting Charts 158 Renaming a Chart 159 Try It 160 Lesson Requirements 160 Step-by-Step 160 Lesson 15: Programm ing PivotTabl es and PivotCharts 163 Creating a PivotTable Report 163 Hiding the PivotTable Field List 167 Using the Report Filter Area 167 Formatting Numbers in the Values Area 168 Why It s Called a PivotTable 170 Creating a PivotChart 171 Understanding PivotCaches 173 Manipulating PivotFields in VBA 176 Manipulating PivotItems with VBA 177 Creating a PivotTables Collection 177 Try It 178 Lesson Requirements 178 Step-by-Step 179 Lesson 16: User Defined Fu nctions 183 What Is a User Defined Function? 183 Characteristics of User Defined Functions 184 Anatomy of a UDF 184 UDF Examples That Solve Common Tasks 185 Volatile Functions 188 The Name of the Active Worksheet and Workbook 189 UDFs with Conditional Formatting 190 Calling Your Function from a Macro 190 Adding a Description to the Insert Function Dialog 191 Try It 193 Lesson Requirements 193 Step-by-Step 193 Lesson 17: Debu gging Your Code 195 What Is Debugging? 195 What Causes Errors? 196 Weapons of Mass Debugging 198 The Debugging Toolbar 198 Trapping Errors 207 Error Handler 207 Bypassing Errors 208 Try It 210 Lesson Requirements 210 Step-by-Step 210 Section IV: Advanced Programm ing Techniques Lesson 18: Creating UserForms 215 What Is a UserForm? 215 Creating a UserForm 216 Designing a UserForm 218 Showing a UserForm 225 Where Does the UserForm s Code Go? 225 Closing a UserForm 226 Unloading a UserForm 226 Hiding a UserForm 227 Try It 228 Lesson Requirements 228 Step-by-Step 228 Lesson 19: nd Their Fu nctions 231 Understanding the Frequently Used UserForm Controls 231 CommandButtons 232 Labels 232 TextBoxes 234 ListBoxes 236 ComboBoxes 238 CheckBoxes 240 OptionButtons 241 Frames 243 MultiPages 245 Try It 246 Lesson Requirements 246 Step-by-Step 246 Lesson 20: Advanced UserForms 249 The UserForm Toolbar 249 Modal versus Modeless 250 Disabling the UserForm s Close Button 250 Maximizing Your UserForm s Size 252 Selecting and Displaying Photographs on a UserForm 252 Unloading a UserForm Automatically 253 Pre-Sorting the ListBox and ComboBox Items 253 Populating ListBoxes and ComboBoxes with Unique Items 255 Display a Real-Time Chart in a UserForm 258 Try It 259 Lesson Requirements 259 Step-by-Step 259 Lesson 21: Class Modul es 263 What Is a Class? 263 What Is a Class Module? 264 Creating Your Own Objects 265 An Important Benefit of Class Modules 266 Creating Collections 268 Class Modules for Embedded Objects 269 Try It 272 Lesson Requirements 272 Step-by-Step 272 Lesson 22: Add-Ins 279 What Is an Excel Add-In? 279 Creating an Add-In 280 Converting a File to an Add-In 284 Installing an Add-In 286 Creating a User Interface for Your Add-In 288 Changing the Add-In s Code 290 Closing Add-Ins 290 Removing an Add-In from the Add-Ins List 291 Try It 291 Lesson Requirements 291 Step-by-Step 291 Lesson 23: Managing External Data 295 Creating QueryTables from Web Queries 295 Creating a QueryTable for Access 299 Using Text Files to Store External Data 301 Try It 304 Lesson Requirements 304 Step-by-Step 304 Lesson 24: Data Acc ess with Ac tiveX Data Ob jects 307 Introducing ADO 307 The Connection Object 309 The Recordset Object 309 The Command Object 310 An Introduction to Structured Query Language (SQL) 310 The SELECT Statement 311 The INSERT Statement 311 The UPDATE Statement 312 The DELETE Statement 312 Try It 313 Lesson 25: Not Gone, Not Forgotten 315 Using Dialog Sheets 315 What Does a Dialog Sheet Look Like? 316 Option to Show Message Only Once 318 Using XLM Get.Cell Functions 321 Using the SendKeys Method 322 Try It 323 Lesson Requirements 323 Step-by-Step 323 Interacting with Other Section V: Office Applications Lesson 26: Ov erview of Office Au tomation from Excel 327 Why Automate Another Application? 327 Understanding Office Automation 328 Early Binding 328 Late Binding 329 Which One Is Better? 330 Try It 330 Lesson Requirements 330 Step-by-Step 330 Lesson 27: Working with Word from Excel 333 Activating a Word Document 333 Activating the Word Application 334 Opening and Activating a Word Document 334 Creating a New Word Document 336 Copying an Excel Range to a Word Document 337 Printing a Word Document from Excel 337 Importing a Word Document to Excel 338 Try It 339 Lesson Requirements 339 Step-by-Step 339 Lesson 28: Working with Ou tlook from Excel 343 Opening Outlook 343 Composing an E-mail in Outlook from Excel 344 Creating a MailItem Object 344 Transferring an Excel Range to the Body of Your E-mail 345 Putting It All Together 346 E-mailing a Single Worksheet 348 Try It 348 Lesson Requirements 348 Step-by-Step 348 Lesson 29: ith Acc ess from Excel 353 Adding a Record to an Access Table 353 Exporting an Access Table to an Excel Spreadsheet 356 Creating a New Table in Access 358 Try It 359 Lesson Requirements 359 Step-by-Step 360 Lesson 30: Working with PowerPoint from Excel 363 Creating a New PowerPoint Presentation 363 Copying a Worksheet Range to a PowerPoint Slide 364 Copying Chart Sheets to PowerPoint Slides 365 Running a PowerPoint Presentation from Excel 367 Try It 368 Lesson Requirements 368 Step-by-Step 368 Appendix: What s on the DVD? 371 Index 375