9780470890691

Excel VBA 24-Hour Trainer

by
  • ISBN13:

    9780470890691

  • ISBN10:

    047089069X

  • Edition: DVD
  • Format: Paperback
  • Copyright: 5/3/2011
  • Publisher: Wrox
  • Purchase Benefits
  • Free Shipping On Orders Over $59!
    Your order must be $59 or more to qualify for free economy shipping. Bulk sales, PO's, Marketplace items, eBooks and apparel do not qualify for this offer.
  • Get Rewarded for Ordering Your Textbooks! Enroll Now
List Price: $39.99

Summary

This unique book-and-DVD package helps even non-programmers extend Excel with VBAVirtually every manual task in Excel can be automated with VBA, which increases your productivity and saves enormous amounts of time. This unique book-and-DVD package prepares you to get more out of Excel by using Visual Basic for Applications (VBA) to automate many routine or labor-intensive Excel tasks. Microsoft Excel MVP and author Tom Urtis walks through a series of lessons and illustrations, while the accompanying DVD provides demos and screencasts to complement each lesson. Introduces you to programming with Visual Basic for Applications (VBA), macro composition, and the programming environment Explains events programming, embedded controls, user forms, message boxes, input boxes, looping, and more Shows you how to control other Office applications from Excel, such as Word, Outlook, Access, and PowerPoint Includes enhanced coverage of each lesson on the DVD, which also offers detailed examples Provides ideas for applying VBA to everyday tasks in a way that's both practical and funUse this book-and-DVD package to get the total learning experience of VBA for Excel!

Author Biography

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.

Table of Contents

Introductionp. xxvii
Understanding the BASI Cs
Introduc ing VBAp. 3
What Is VBA?p. 3
A Brief History of VBAp. 4
What VBA Can Do for Youp. 5
Automating a Recurring Taskp. 5
Automating a Repetitive Taskp. 5
Running a Macro Automatically if Another Action Takes Placep. 5
Creating Your Own Worksheet Functionsp. 5
Simplifying the Workbook's Look and Feel for Other Usersp. 5
Controlling Other Office Applications from Excelp. 6
Liabilities of VBAp. 7
Try Itp. 8
Getting Started with Macrosp. 9
Composing Your First Macrop. 9
Accessing the VBA Environmentp. 9
Using the Macro Recorderp. 12
Running a Macrop. 16
The Macro Dialog Boxp. 16
Shortcut Keyp. 17
Try Itp. 17
Lesson Requirementsp. 18
Step-by-Stepp. 18
Introduc ing the Visual Basic Editorp. 19
What Is the VBE?p. 19
How To Get Into the VBEp. 20
Understanding the VBEp. 20
The Project Explorer Windowp. 21
The Code Windowp. 21
The Properties Windowp. 22
The Immediate Windowp. 22
Understanding Modulesp. 22
Using the Object Browserp. 23
Exiting the VBEp. 24
Try Itp. 25
Working in the VBEp. 27
Toolbars in the VBEp. 27
Macros and Modulesp. 28
Locating Your Macrosp. 28
Understanding the Codep. 29
Editing a Macro with Comments and Improvements to the Codep. 30
Deleting a Macrop. 33
Inserting a Modulep. 33
Renaming a Modulep. 34
Deleting a Modulep. 36
Locking and Protecting the VBEp. 36
Try Itp. 37
Lesson Requirementsp. 37
Step-by-Stepp. 37
: Diving Deeper into VBA
Ob ject-oriented Programm ing - A n Ov erviewp. 43
What "Object-Oriented Programming" Meansp. 43
The Object Modelp. 44
Propertiesp. 45
Methodsp. 46
Collectionsp. 46
Try Itp. 47
Variabl es, Data Types, and Constantsp. 49
What Is a Variable?p. 49
Assigning Values to Variablesp. 50
Why You Need Variablesp. 50
Data Typesp. 51
Understanding the Different Data Typesp. 51
Declaring a Variable for Dates and Timesp. 53
Declaring a Variable with the Proper Data Typep. 53
Forcing Variable Declarationp. 54
Understanding a Variable's Scopep. 56
Local Macro Level Onlyp. 56
Module Levelp. 56
Application Levelp. 57
Constantsp. 57
Choosing the Scope and Lifetime of Your Constantsp. 58
Try Itp. 58
Lesson Requirementsp. 58
Step-by-Stepp. 58
Understanding
Ob jects and Coll ectionsp. 61
Workbooksp. 61
Worksheetsp. 62
Cells and Rangesp. 63
SpecialCellsp. 64
Try Itp. 65
Lesson Requirementsp. 65
Step-by-Stepp. 65
Making Decisions with VBAp. 69
Understanding Logical Operatorsp. 69
ANDp. 70
ORp. 70
NOTp. 71
Choosing Between This or Thatp. 72
If…Thenp. 72
If…Then…Elsep. 73
If…Then…ElseIfp. 74
Select Casep. 74
Getting Users to Make Decisionsp. 76
Message Boxesp. 76
Input Boxesp. 77
Try Itp. 78
Lesson Requirementsp. 78
Step-by-Stepp. 78
: the macro recorder: writing your Own Code
Repeating Ac tions with Loopsp. 85
What Is a Loop?p. 85
Types of Loopsp. 86
For…Nextp. 87
For…Each…Nextp. 88
Exiting a For… Loopp. 89
Looping In Reverse with Stepp. 90
Do…Whilep. 91
Do…Untilp. 91
Do…Loop…Whilep. 93
Do…Loop…Untilp. 94
While…Wendp. 94
Nesting Loopsp. 94
Try Itp. 95
Lesson Requirementsp. 96
Step-by-Stepp. 96
Working with Arraysp. 99
What Is an Array?p. 99
What Arrays Can Do for Youp. 101
Declaring Arraysp. 102
The Option Base Statementp. 103
Boundaries in Arraysp. 104
Declaring Arrays with Fixed Elementsp. 104
Declaring Dynamic Arrays with ReDim and Preservep. 105
Try Itp. 107
Lesson Requirementsp. 107
Step-by-Stepp. 107
Au tomating Procedures with Worksheet Ev entsp. 111
What Is an "Event"?p. 111
Worksheet Events - an Overviewp. 112
Where Does the Worksheet Event Code Go?p. 112
Enabling and Disabling Eventsp. 114
Examples of Common Worksheet Eventsp. 115
Worksheet_Change Eventp. 115
Worksheet_SelectionChange Eventp. 116
Worksheet_BeforeDoubleClick Eventp. 116
Worksheet_BeforeRightClick Eventp. 117
Worksheet_FollowHyperlink Eventp. 117
Worksheet_Activate Eventp. 117
Worksheet_Deactivate Eventp. 118
Worksheet_Calculate Eventp. 118
Worksheet_PivotTableUpdate Eventp. 119
Try Itp. 119
Lesson Requirementsp. 119
Step-by-Stepp. 119
Au tomating Procedures
with Workbook Ev entsp. 123
Workbook Events - An Overviewp. 123
Where Does the Workbook Event Code Go?p. 123
Entering Workbook Event Codep. 125
Examples of Common Workbook Eventsp. 126
Workbook_Open Eventp. 126
Workbook_BeforeClose Eventp. 127
Workbook_Activate Eventp. 127
Workbook_Deactivate Eventp. 128
Workbook_SheetChange Eventp. 128
Workbook_SheetSelectionChange Eventp. 128
Workbook_SheetBeforeDoubleClick Eventp. 129
Workbook_SheetBeforeRightClick Eventp. 129
Workbook_SheetPivotTableUpdate Eventp. 130
Workbook_NewSheet Eventp. 130
Workbook_BeforePrint Eventp. 130
Workbook_SheetActivate Eventp. 131
Workbook_SheetDeactivate Eventp. 131
Workbook_BeforeSave Eventp. 131
Try Itp. 132
Lesson Requirementsp. 132
Step-by-Stepp. 132
Emb edded Controlsp. 135
Working with Forms Controls and ActiveX Controlsp. 135
The Forms Toolbarp. 136
The Control Toolboxp. 140
Try Itp. 144
Lesson Requirementsp. 144
Step-by-Stepp. 144
Programm ing Chartsp. 151
Adding a Chart to a Chart Sheetp. 152
Adding an Embedded Chart to a Worksheetp. 154
Moving a Chartp. 155
Looping Through All Embedded Chartsp. 157
Deleting Chartsp. 158
Renaming a Chartp. 159
Try Itp. 160
Lesson Requirementsp. 160
Step-by-Stepp. 160
Programm ing PivotTabl es and PivotChartsp. 163
Creating a PivotTable Reportp. 163
Hiding the PivotTable Field Listp. 167
Using the Report Filter Areap. 167
Formatting Numbers in the Values Areap. 168
Why It's Called a PivotTablep. 170
Creating a PivotChartp. 171
Understanding PivotCachesp. 173
Manipulating PivotFields in VBAp. 176
Manipulating PivotItems with VBAp. 177
Creating a PivotTables Collectionp. 177
Try Itp. 178
Lesson Requirementsp. 178
Step-by-Stepp. 179
User Defined Fu nctionsp. 183
What Is a User Defined Function?p. 183
Characteristics of User Defined Functionsp. 184
Anatomy of a UDFp. 184
UDF Examples That Solve Common Tasksp. 185
Volatile Functionsp. 188
The Name of the Active Worksheet and Workbookp. 189
UDFs with Conditional Formattingp. 190
Calling Your Function from a Macrop. 190
Adding a Description to the Insert Function Dialogp. 191
Try Itp. 193
Lesson Requirementsp. 193
Step-by-Stepp. 193
Debu gging Your Codep. 195
What Is Debugging?p. 195
What Causes Errors?p. 196
Weapons of Mass Debuggingp. 198
The Debugging Toolbarp. 198
Trapping Errorsp. 207
Error Handlerp. 207
Bypassing Errorsp. 208
Try Itp. 210
Lesson Requirementsp. 210
Step-by-Stepp. 210
Advanced Programm ing Techniques
Creating UserFormsp. 215
What Is a UserForm?p. 215
Creating a UserFormp. 216
Designing a UserFormp. 218
Showing a UserFormp. 225
Where Does the UserForm's Code Go?p. 225
Closing a UserFormp. 226
Unloading a UserFormp. 226
Hiding a UserFormp. 227
Try Itp. 228
Lesson Requirementsp. 228
Step-by-Stepp. 228
nd Their Fu nctionsp. 231
Understanding the Frequently Used UserForm Controlsp. 231
CommandButtonsp. 232
Labelsp. 232
TextBoxesp. 234
ListBoxesp. 236
ComboBoxesp. 238
CheckBoxesp. 240
OptionButtonsp. 241
Framesp. 243
MultiPagesp. 245
Try Itp. 246
Lesson Requirementsp. 246
Step-by-Stepp. 246
Advanced UserFormsp. 249
The UserForm Toolbarp. 249
Modal versus Modelessp. 250
Disabling the UserForm's Close Buttonp. 250
Maximizing Your UserForm's Sizep. 252
Selecting and Displaying Photographs on a UserFormp. 252
Unloading a UserForm Automaticallyp. 253
Pre-Sorting the ListBox and ComboBox Itemsp. 253
Populating ListBoxes and ComboBoxes with Unique Itemsp. 255
Display a Real-Time Chart in a UserFormp. 258
Try Itp. 259
Lesson Requirementsp. 259
Step-by-Stepp. 259
Class Modul esp. 263
What Is a Class?p. 263
What Is a Class Module?p. 264
Creating Your Own Objectsp. 265
An Important Benefit of Class Modulesp. 266
Creating Collectionsp. 268
Class Modules for Embedded Objectsp. 269
Try Itp. 272
Lesson Requirementsp. 272
Step-by-Stepp. 272
Add-Insp. 279
What Is an Excel Add-In?p. 279
Creating an Add-Inp. 280
Converting a File to an Add-Inp. 284
Installing an Add-Inp. 286
Creating a User Interface for Your Add-Inp. 288
Changing the Add-In's Codep. 290
Closing Add-Insp. 290
Removing an Add-In from the Add-Ins Listp. 291
Try Itp. 291
Lesson Requirementsp. 291
Step-by-Stepp. 291
Managing External Datap. 295
Creating QueryTables from Web Queriesp. 295
Creating a QueryTable for Accessp. 299
Using Text Files to Store External Datap. 301
Try Itp. 304
Lesson Requirementsp. 304
Step-by-Stepp. 304
Data Acc ess with Ac tiveX Data Ob jectsp. 307
Introducing ADOp. 307
The Connection Objectp. 309
The Recordset Objectp. 309
The Command Objectp. 310
An Introduction to Structured Query Language (SQL)p. 310
The SELECT Statementp. 311
The INSERT Statementp. 311
The UPDATE Statementp. 312
The DELETE Statementp. 312
Try Itp. 313
Not Gone, Not Forgottenp. 315
Using Dialog Sheetsp. 315
What Does a Dialog Sheet Look Like?p. 316
Option to Show Message Only Oncep. 318
Using XLM Get.Cell Functionsp. 321
Using the SendKeys Methodp. 322
Try Itp. 323
Lesson Requirementsp. 323
Step-by-Stepp. 323
Interacting with Other
Office Applications
Ov erview of Office Au tomation from Excelp. 327
Why Automate Another Application?p. 327
Understanding Office Automationp. 328
Early Bindingp. 328
Late Bindingp. 329
Which One Is Better?p. 330
Try Itp. 330
Lesson Requirementsp. 330
Step-by-Stepp. 330
Working with Word from Excelp. 333
Activating a Word Documentp. 333
Activating the Word Applicationp. 334
Opening and Activating a Word Documentp. 334
Creating a New Word Documentp. 336
Copying an Excel Range to a Word Documentp. 337
Printing a Word Document from Excelp. 337
Importing a Word Document to Excelp. 338
Try Itp. 339
Lesson Requirementsp. 339
Step-by-Stepp. 339
Working with Ou tlook from Excelp. 343
Opening Outlookp. 343
Composing an E-mail in Outlook from Excelp. 344
Creating a MailItem Objectp. 344
Transferring an Excel Range to the Body of Your E-mailp. 345
Putting It All Togetherp. 346
E-mailing a Single Worksheetp. 348
Try Itp. 348
Lesson Requirementsp. 348
Step-by-Stepp. 348
ith Acc ess from Excelp. 353
Adding a Record to an Access Tablep. 353
Exporting an Access Table to an Excel Spreadsheetp. 356
Creating a New Table in Accessp. 358
Try Itp. 359
Lesson Requirementsp. 359
Step-by-Stepp. 360
Working with PowerPoint from Excelp. 363
Creating a New PowerPoint Presentationp. 363
Copying a Worksheet Range to a PowerPoint Slidep. 364
Copying Chart Sheets to PowerPoint Slidesp. 365
Running a PowerPoint Presentation from Excelp. 367
Try Itp. 368
Lesson Requirementsp. 368
Step-by-Stepp. 368
Appendix: What's on the DVD?p. 371
Indexp. 375
Table of Contents provided by Publisher. All Rights Reserved.

Rewards Program

Write a Review