New Horizons Computer Learning Centers of Northwest Arkansas
VBA Solutions for Microsoft Office – Level 1
INTRODUCTION Courseware Conventions INTRO-2 The Practice Disks INTRO-3 Working with the Office Samples INTRO-3 About the Author INTRO-5 GETTING STARTED WITH VBA What Is VBA? 1-2 Why Would You Want to Use VBA? 1-2 What Products Host VBA? 1-4 The Flavors of Office 1-4 Who Has Licensed VBA? 1-5 What Does a VBA Application Look Like? 1-6 Custom Dialog Boxes 1-6 Custom Toolbars and Menus1-7 Custom Buttons 1-7 Behind-the-Scenes Operations 1-8 The Code Underneath It All 1-9 Working with VBA in a Host Product 1-10 Where Does VBA Code Live? 1-10 Where are Projects Stored in Office Products? 1-11 Where Are Projects Stored in Other VBA Hosts? 1-12 How Do You Make VBA Code Happen?1-13 The Immediate Window 1-13 User Interface Tools1-14 Events 1-17 Starting with VBA by Recording Macros1-19 Recorded Macros in Excel, Word, and PowerPoint 1-19 Using the Macro Recorder 1-19 Running a Recorded Macro 1-21 Looking at the Code You Wrote 1-22 Modifying the Code to Make It Do More (or Less) 1-23 Why Not Just Record Macros? 1-25 A Word About Access Macros 1-25 LAB 1: GETTING STARTED WITH VBA1-31 Lab 1 Overview1-32 Setting Security Levels 1-33 Explore an Excel VBA Application 1-35 Call a Function from the Excel User Interface 1-39 Try Out a Word VBA Application 1-41 Record a Macro in Word1-46 Create a Toolbar and Button in Word1-51 Call a Function from an Access Query 1-55 WRITING VBA PROCEDURES 2-1 Overview of the Visual Basic Editor2-2 Opening the Visual Basic Editor 2-2 The Project Explorer 2-3 The Properties Window2-4 The Immediate Window 2-4 The Code Window 2-4 Working with the Project Explorer2-5
What Kinds of Components Does a Project Contain? 2-5 Adding Items to a Project2-7 Removing Items from a Project2-7 Saving a Project2-7 Working with Multiple Projects 2-7 Naming Your Project2-8 Creating Simple Procedures2-10 Understanding Functions 2-10 Writing Your First VBA Function 2-12 Key Concepts2-15 Running Your Function from Word 2-17 Creating a Simple Sub Procedure 2-18 Running a Sub Procedure2-19 Understanding the Code Window 2-20 Anatomy of a Module 2-20 Changing a Module’s Name2-21 Customizing the Visual Basic Editor 2-22 Showing and Hiding Windows 2-22 Docking and Undocking Windows 2-22 Changing the Editor Format2-23 Displaying Toolbars 2-24 LAB 2: WRITING VBA PROCEDURES2-29 Lab 2 Overview2-30 Create and Rename a VBA Project 2-31 Add a New Module to a Project 2-33 Create a Simple Function 2-36 Call a VBA Procedure from an Excel Worksheet2-39 Change the Visual Basic Editor Format2-40 WORKING WITH VARIABLES AND CONSTANTS What Is a Variable? 3-2 Give Your Variables Meaningful Names3-3 Data Types 3-4 What Is a Data Type? 3-4 Choosing a Data Type 3-5 Declaring Variables3-6 Using a Naming Convention 3-8 Initializing Variables 3-9 Why Not Just Use the Variant Data Type? 3-10 When Can I Use the Variant Data Type?3-10 Implicit vs Explicit Declarations3-12 Working with Arrays3-15 What Is An Array?3-15 Declaring Arrays 3-16 Working with Elements of Arrays 3-17 Changing an Array’s Size 3-18 Detecting Array Bounds 3-19 An Array Example 3-20 Using Constants 3-21 The Carriage Return-Line Feed Constant3-21 Using the MsgBox Function 3-22 Creating Your Own Constants 3-25 LAB 3: WORKING WITH VARIABLES AND CONSTANTS 3-29 Lab 3 Overview3-30 Use Data Types and Variables 3-31 Use Option Explicit 3-33 Set Option Explicit as the Default 3-36
Apply a Naming Convention 3-39 Use Built-In Constants 3-42 Define and Use a Constant3-44 VISUAL BASIC EDITOR TECHNIQUES Writing Code Efficiently 4-2 Moving Code Around 4-2 Printing Code 4-3 Viewing Multiple Procedures 4-3 Formatting Code 4-4 Commenting Out Code 4-5 Setting a Bookmark 4-5 Taking Advantage of IntelliSense 4-7 List Properties/Methods (CTRL+J)4-7 List Constants (CTRL+SHIFT+J) 4-8 Parameter Info (CTRL+SHIFT+I)4-8 Word Completion 4-9 Optional and Named Arguments 4-11 Optional Arguments 4-11 Introducing Named Arguments 4-11 LAB 4: VISUAL BASIC EDITOR TECHNIQUES 4-17 Lab 4 Overview4-18 Move Code to a New Procedure4-19 Comment Out Code with the Edit Toolbar 4-21 Use IntelliSense to Get Help4-23 Use Named Arguments 4-25 MASTERING VBA CONTROL STRUCTURES Branching Structures 5-2 Why Branch? 5-2 IfThen[Else]End If 5-2 IfThenElse[If]End If 5-4 Select Case 5-5 GoTo 5-9 Looping Structures 5-11 What is a Loop? 5-11 DoLoops 5-11 Do…Loop with Conditions 5-13 ForNext 5-16 LAB 5: MASTERING VBA CONTROL STRUCTURES 5-23 Lab 5 Overview5-24 Use If…Then/Else…End If 5-25 Use Select Case5-27 Use a Do…Loop 5-29 Use a For…Next Loop 5-31 INVESTIGATING BUILT-IN FUNCTIONS VBA’s Built-In Functions 6-2 Using the Object Browser to Find VBA Functions 6-3 Testing Functions in the Immediate Window 6-6 Convert Your Name to Proper Case 6-6 Calculate Your Age in Weeks 6-10 Interacting with the Computer 6-14 Working with the Windows Registry 6-14 VBA Registry Functions 6-15 Calling the Functions 6-16 VBA Functions You’ll Probably Need 6-19
Strings 6-19 Dates and Times 6-19 Conversion 6-20 Other Useful Functions 6-21 LAB 6: INVESTIGATING BUILT-IN FUNCTIONS 6-25 Lab 6 Overview 6-26 Request Data Using the InputBox Function 6-27 Work with Strings 6-30 Work with Dates 6-32 Work with the Format Function 6-34 CREATING USER FORMS Working with User Forms 7-2 What Are User Forms? 7-2 Creating a New User Form 7-2 Design Mode vs Run Mode7-3 Adding Controls to a User Form 7-5 What Are Controls?7-5 Introducing a Few Controls 7-5 Formatting Controls 7-8 Selecting a Control7-8 Moving a Control7-8 Resizing a Control7-8 Working with Multiple Controls7-9 Deleting Controls 7-10 Formatting Controls 7-10 Setting Properties at Design Time 7-11 Working with the Properties Window 7-12 A Few Common Properties7-13 Introducing Events 7-17 Event Procedures 7-17 Creating an Event Procedure7-18 The Click Event7-19 The Change Event 7-20 Setting Properties at Run Time7-22 Setting Property Values 7-22 Retrieving Property Values 7-23 Setting Control Properties7-23 Additional ActiveX Controls 7-26 Where Do I Get These Other Controls?7-26 Adding a Control to the Toolbox 7-26 Organizing the Toolbox7-28 Modifying an ActiveX Control’s Properties7-28 Using the Calendar Control7-30 Working with Methods 7-33 The Show Method7-33 The SetFocus Method7-34 Working with List Boxes and Combo Boxes 7-36 Using the AddItem Method or Combo Box 7-36 Using a List Box’s or Combo Box’s List Property7-37 What Did the User Choose? 7-38 LAB 7: CREATING USER FORMS 7-43 Lab 7 Overview7-44 Create Text Boxes and Labels 7-45 Use the Click Event 7-50 Change Control Properties from Code 7-53 Open a Form from a Toolbar Button7-54
Use the SetFocus Method 7-60 UNDERSTANDING OBJECT MODELS Defining the Terms 8-2 What Is an Object? 8-2 Object Models 8-2 The Excel Object Model8-3 The Microsoft Forms Object Model8-4 Typical Objects in Other Products 8-4 Using the Object Browser 8-6 Opening the Object Browser8-6 The Parts of the Object Browser8-7 Working with Object Models in VBA 8-9 Referring to Objects in Collections8-9 Referring to Collections Within Objects 8-11 Setting Properties on Objects 8-12 Object Variables 8-14 Introducing Object Variables8-14 Using Object Variables 8-15 What About Uninitialized Object Variables? 8-16 Special VBA Control Structures for Objects 8-17 Using WithEnd With 8-17 Introducing For EachNext 8-18 Differentiating Types of Controls8-19 Working with the Office Object Models8-22 The CommandBars Object Model8-22 LAB 8: UNDERSTANDING OBJECT MODELS8-31 Lab 8 Overview8-32 Use Object Variables 8-33 Use With…End With8-37 Use the Object Browser8-39 HANDLING ERRORS Recognizing a Run-Time Error 9-2 Different Kinds of Errors9-2 Doesn’t VBA Handle Run-Time Errors?9-2 The Error-Handling Standard9-4 The Three-Step Program 9-4 Creating a Standard Error Handler 9-4 Setting the Error Trap 9-6 Which Error Occurred? 9-6 Using the Error Number 9-7 Exiting the Error Handler 9-9 Resume9-9 Resume Next 9-11 Resume Label9-11 Inline Error Handling 9-12 On Error Resume Next 9-12 Disregarding Errors9-12 Handling Errors as They Occur9-12 Combining Types of Error Handling9-14 LAB 9: HANDLING ERRORS9-19 Lab 9 Overview9-20 Use Simple Error Handling 9-22 Use the Err Object 9-26 Handle the Error 9-28
DEBUGGING YOUR VBA CODE Debugging Your Code 10-2 What Is Break Mode? 10-2 What You Can Do In Break Mode10-4 The Debug Toolbar 10-5 Setting a Breakpoint 10-6 Hard-Coding Breakpoints10-6 Using DebugAssert in Office 2000/XP 10-7 Stepping Through Code10-10 Single Step Mode10-10 Procedure Step Mode 10-10 Stepping Out of a Procedure 10-11 Running to the Cursor10-12 Continuing Execution 10-12 Resetting the Project10-12 Checking Values at Run Time 10-13 Using Auto Data Tips 10-13 Using the Immediate Window 10-14 Using the Locals Window10-15 Watch Expressions 10-17 Using Watch Expressions 10-17 Conditional Watch Expressions 10-20 The Call Stack 10-22 Using the VBA Call Stack Dialog Box 10-22 Working with Error Handling 10-23 The Debugging Process 10-29 Write Bug-Free Code 10-29 Debugging Hints 10-29 LAB 10: DEBUGGING YOUR VBA CODE10-35 Lab 10 Overview10-36 Experiment with Break Mode10-37 Watch Expressions 10-39 UNDERSTANDING EVENTS Events and Event Procedures 11-2 What Did You Just Do?11-3 Where To from Here? 11-3 Which Objects Have Events? 11-4 User Form Events11-5 The UserForm QueryClose Event11-5 Event Procedure Arguments11-6 Control Events 11-8 Before/AfterUpdate Events 11-8 Click Event 11-9 Product-Specific Events11-11 Word’s Open Event11-11 The Worksheet Change Event11-13 Outlook’s ItemSend Event 11-14 Is There More? 11-15 Getting At Other Events 11-16 LAB 11: UNDERSTANDING EVENTS 11-23 Lab 11 Overview 11-24 Change a Form’s Caption on Load 11-25 Capitalize Data Entry in a Text Box 11-27 Spell-Check a Document on Close11-28 Work with a Cancelable Event 11-30
PUTTING IT ALL TOGETHER Assembling the Pieces 12-2 Building a Solution in Excel 12-3 Creating the User Form 12-3 Using the Product-Specific Event 12-4 Running the Solution12-5 Using the Solution in Word 12-7 Copying the User Form12-7 Changing the User Form12-7 Using the Product-Specific Event 12-8 Creating a Template 12-8 Running the Solution12-8 Where Are You Going from Here?12-10 APPENDIX A: CALLING VBA PROCEDURES FROM THE USER INTERFACE Calling a VBA Procedure from the User Interface A-2 Adding Shortcut Keys A-2 Adding Form Controls to a Document A-6 Creating Custom Toolbars and Buttons A-8 Using User Defined Functions in an Excel Worksheet Cell A-11 Calling a Function in an Access Form or Query A-11 APPENDIX B: COMPARISON OF VBA, VISUAL BASIC, AND MICROSOFT ACCESS FORMS Confused Yet? B-2 Loading and Unloading Forms B-3 Events B-4 Properties B-5 APPENDIX C: THE REDDICK VBA NAMING CONVENTIONS, VERSION 6 Changes to the Conventions C-2 An Introduction to Hungarian C-3 Tags C-4 Creating Data Types C-6 Constructing Procedures C-9 Prefixes C-10 Suffixes C-12 File Names C-12 Host Application and Component Extensions to the Conventions C-13 APPENDIX D: CREATING REUSABLE PROCEDURES Design before Code D-2 Why Bother? D-2 Writing the Same Code Once D-3 An Example: Parsing a String into Words D-3 Move Duplicate Code to a New Procedure D-6 Don’t Hard-Code D-9 Design with the Next Project in Mind D-12 APPENDIX E: TECHNIQUES WITH LIST BOXES AND COMBO BOXES Using Multi-Column List Boxes and Combo Boxes E-2 Multiple Selection in List Boxes E-6 Figuring Out What Was Selected E-6 Moving Items Between List Boxes E-8 Controlling Data Entry in Combo Boxes E-9