VBA FOR BEGINNERS
ABOUT THIS BOOK CONVENTIONS USED IN THIS BOOK WHAT YOU SHOULD READ WHAT YOU DON’T HAVE TO READ FOOLISH ASSUMPTIONS HOW THIS BOOK IS ORGANIZED PART I: AN OVERVIEW OF VBA PART II: LEARNING THE ROPES PART III: EXPANDING YOUR VBA HORIZONS PART IV: PROGRAMMING FOR APPLICATIONS PART V: THE PART OF TENS THE ACCOMPANYING WEB SITE ICONS USED IN THIS BOOK WHERE TO GO FROM HERE BATTERIES INCLUDED — VBA COMES WITH OFFICE VBA: IT’S NOT JUST FOR PROGRAMMERS AUTOMATING DOCUMENTS CUSTOMIZING AN APPLICATION’S INTERFACE PERFORMING CALCULATIONS GETTING STUFF FROM A DATABASE ADDING NEW APPLICATION FEATURES MAKING SPECIAL TOOLS HAVING THINGS YOUR WAY OTHER PRODUCTS USE VBA, TOO A ROOM WITH A VIEW LOOKING AT THE INTEGRATED DEVELOPMENT ENVIRONMENT (IDE) LOOKING AT THE VBA TOOLBOX LOOKING AT OBJECTS STARTING THE VISUAL BASIC EDITOR WORD 2007, EXCEL 2007, AND POWERPOINT 2007 ACCESS 2007 ONENOTE 2007, PUBLISHER 2007, VISIO 2007, PROJECT 2007, AND ALL OLDER VERSIONS OF OFFICE SECURITY UNDER VISTA SETTING MACRO SECURITY FOR WORD 2007, EXCEL 2007, POWERPOINT 2007, AND ACCESS 2007 SETTING MACRO SECURITY FOR ONENOTE 2007, PUBLISHER 2007, VISIO 2007, PROJECT 2007, AND ALL OLDER VERSIONS OF OFFICE USING PROJECT EXPLORER USING THE PROPERTIES WINDOW USING THE CODE WINDOW USING THE IMMEDIATE WINDOW USING OBJECT BROWSER DECIDING WHAT TO DO STEPS TO CREATE A VBA PROGRAM STEP 1: DESIGN THE PROGRAM STEP 2: IMPLEMENT THE DESIGN STEP 3: TEST, TEST, TEST STEP 4: SWAT THE BUGS FOUR WAYS TO RUN YOUR PROGRAM USING THE MACRO DIALOG BOX USING THE QUICK-LAUNCH METHODS ACCESSING THE PROGRAM FROM OTHER VBA CODE EXECUTING THE VBA PROGRAM AUTOMATICALLY USING HELP TO YOUR ADVANTAGE — STEALING MICROSOFT’S CODE PARTS OF A PROGRAM DEFINING THE PARTS OF A PROGRAM UNDERSTANDING THE VBA PROGRAMMING BLOCKS USING THE MACRO RECORDER USING SUBS USING FUNCTIONS MODIFYING THE PROJECT SETTINGS DEFINING COMPILER OPTIONS TAKING THE LEGO APPROACH CREATING AN APPLICATION PLAN DEFINING THE PROJECT ADDING A MODULE DESIGNING PROCEDURES WRITING STATEMENTS WRITING YOUR FIRST SUB WRITING YOUR FIRST FUNCTION GETTING THE SCOOP ON SCOPE UNDERSTANDING THE PURPOSE OF SCOPE DEFINING THE EFFECTS OF SCOPE CREATING READABLE CODE TELLING OTHERS ABOUT YOUR CODE WRITING BASIC COMMENTS KNOWING WHEN TO USE COMMENTS UNDERSTANDING HOW TO CREATE A GOOD COMMENT UNDERSTANDING VARIABLES AND CONSTANTS MAKING THE DECLARATION KNOWING WHICH STORAGE TYPE TO USE DEFINING SCOPE DEFINING THE DATA TYPES USING STRINGS FOR TEXT USING NUMBERS FOR CALCULATIONS USING BOOLEAN VALUES TO MAKE DECISIONS USING SCIENTIFIC VALUES FOR MATH CALCULATIONS USING CURRENCY VALUES FOR MONEY CALCULATIONS USING DATE AND TIME VALUES WORKING WITH VARIANT DATA PRESENTING DATA IN A PLEASING FORMAT WORKING WITH OPERATORS APPLYING WHAT YOU KNOW TO DESIGN AN EXCEL REPORT EXERCISING CONTROL WITH STRUCTURES MAKING A DECISION WITH THE IFTHEN STATEMENT USING THE IFTHEN STATEMENT USING THE IFTHENELSE STATEMENT USING THE IFTHENELSEIF STATEMENT USING THE IIF FUNCTION MAKING A CHOICE BY USING THE SELECT CASE STATEMENT USING THE SELECT CASE STATEMENT USING THE CASE ELSE CLAUSE PERFORMING A TASK MORE THAN ONCE BY USING LOOPS USING THE DO WHILELOOP STATEMENT USING THE DOLOOP WHILE STATEMENT USING THE DO UNTILLOOP STATEMENT USING THE DOLOOP UNTIL STATEMENT USING THE FORNEXT STATEMENT USING THE FOR EACHNEXT STATEMENT REDIRECTING THE FLOW BY USING GOTO USING THE GOTO STATEMENT CORRECTLY AVOIDING MISUSE OF THE GOTO STATEMENT KNOWING THE ENEMY UNDERSTANDING SYNTAX ERRORS UNDERSTANDING COMPILE ERRORS UNDERSTANDING RUN-TIME ERRORS UNDERSTANDING SEMANTIC ERRORS PREVENTION IS BETTER THAN A CURE AVOIDING RUN-TIME ERRORS RECOVERING FROM AN ERROR UNDERSTANDING ERROR HANDLERS WRITING YOUR OWN ERROR-HANDLING CODE REPORTING ERRORS SAVING AND RESTORING CODE EXPORTING A MODULE FROM A PROGRAM IMPORTING A MODULE IN A PROGRAM TIME FOR A BUG HUNT EXECUTING A BREAK TAKING INDIVIDUAL STEPS VIEWING THE DATA TIPS USING THE IMMEDIATE WINDOW TO YOUR ADVANTAGE USING THE LOCALS WINDOW USING THE WATCHES WINDOW ADDING A NEW WATCH EXPRESSION USING THE ADD WATCH WINDOW UNDERSTANDING FORMS USING FORMS CREATIVELY DESIGNING A FORM FOR YOUR APPLICATION CONSIDERING THE FORM LAYOUT USING THE BASIC CONTROLS ADDING CONTROLS TO THE FORM UNDERSTANDING THE TWO PARTS OF A FORM USING THE LABEL CONTROL TO DISPLAY TEXT GETTING USER INPUT WITH TEXT BOXES EXECUTING TASKS WITH COMMAND BUTTONS SAYING YES OR NO WITH CHECK BOXES AND TOGGLE BUTTONS MAKING CHOICES WITH OPTION BUTTONS AND FRAMES CHOOSING OPTIONS WITH LIST BOXES AND COMBO BOXES ADDING CONTROLS TO THE TOOLBOX USING THE FORMS YOU CREATE MODIFYING THE FORM AND CONTROL PROPERTIES MAKING YOUR FORM PRETTY CREATING A CONNECTION BETWEEN FORMS AND MODULES VALIDATING USER INPUT HANDLING FORM EVENTS UNDERSTANDING CLASSES UNDERSTANDING OBJECT-ORIENTED PROGRAMMING CONCEPTS UNDERSTANDING PROPERTIES, METHODS, AND EVENTS DEFINING CLASSES CONSIDERING CLASS TYPES USING CLASSES TO IMPROVE YOUR APPLICATIONS DESIGNING A BASIC CLASS DEFINING PROPERTIES DEFINING METHODS DEFINING EVENTS USING ENUMERATED CONSTANTS DEFINING INITIALIZATION SETTING THE INSTANCING PROPERTY CREATING USEFUL CLASSES USING YOUR NEW OBJECT IN AN APPLICATION ADDING ERROR HANDLING TO CLASSES USING THE WITH STATEMENT ADDING A DIGITAL SIGNATURE TO YOUR CREATION OBTAINING A DIGITAL SIGNATURE CREATING A TEST DIGITAL SIGNATURE APPLYING THE DIGITAL SIGNATURE TO A PROJECT USING ARRAYS FOR STRUCTURED STORAGE UNDERSTANDING ARRAY USAGE UNDERSTANDING THE ARRAY TYPES COPYING DATA FROM ONE ARRAY TO ANOTHER USING COLLECTIONS TO CREATE DATA SETS UNDERSTANDING COLLECTION USAGE ADDING KEYED DATA TO THE COLLECTION ACCESSING PREDEFINED COLLECTION ITEMS DEFINING YOUR OWN DATA TYPES UNDERSTANDING USER-DEFINED DATA TYPES KNOWING WHEN TO CREATE YOUR OWN DATA TYPE ACCESSING AND MANIPULATING DATA USING DISK STORAGE APPLICATION CONFIGURATION INFORMATION DATA TRANSLATION DATA STORAGE WORKING WITH SETTINGS WRITING AN INI FILE READING AN INI FILE COMPARING WORDML WITH SAVED XML MANIPULATING XML DATA WRITING THE DATA TO DISK DEFINING A SCHEMA DEFINING XSD TO WORKSHEET LINKAGE EXPORTING THE DATA TO DISK IMPORTING THE DATA FROM DISK CREATING A SIMPLE WORD XML DOCUMENT CHANGING THE FACE OF XML WITH XSLT SAVING YOUR WORD DOCUMENT BY USING XSLT AUTOMATING THE WORD XML PROCESS WORKING WITH THE USER ENVIRONMENT BENEFICIAL CHANGES THAT YOU CAN MAKE PROBLEMS THAT YOU SHOULD CONSIDER MANIPULATING TOOLBARS AND MENUS DISPLAYING OR HIDING TOOLBARS AND MENUS MODIFYING THE TOOLBAR OR MENU CONTENT ADDING AND REMOVING TOOLBARS AND MENUS WORKING WITH THE NEW RIBBON INTERFACE UNDERSTANDING THE APPLICATION FILE CONTENT OBTAINING AND USING THE OFFICE 2007 CUSTOM UI EDITOR ADDING A TAB, GROUP, AND BUTTON PERFORMING TASKS WHEN THE RIBBON LOADS MODIFYING EXISTING TABS UNDERSTANDING THE WORD-RELATED OBJECTS USING THE DOCUMENTS COLLECTION USING THE TEMPLATES COLLECTION USING THE WINDOWS COLLECTION ACCESSING THE WORD DOCUMENT USING THE REGISTRY WITH VBA OVERCOMING UAC PROBLEMS IN VISTA ACCESSING ANY REGISTRY LOCATIONS IN OFFICE SELECTING OBJECTS IN A WORD DOCUMENT UNDERSTANDING OBJECT CONNECTIVITY WORKING WITH EMBEDDED OBJECTS BY USING THE INLINESHAPE COLLECTION MANIPULATING TEXT WORKING WITH ENVELOPES AND LABELS DESIGNING THE ENVELOPE AND LABEL FORM PRINTING ENVELOPES PRINTING LABELS UNDERSTANDING THE EXCEL-RELATED OBJECTS USING THE WORKBOOKS COLLECTION USING THE SHEETS COLLECTION USING THE CHARTS COLLECTION SELECTING OBJECTS WITHIN EXCEL DEVELOPING CUSTOM FUNCTIONS IN EXCEL PERFORMING DATA CONVERSION DEFINING MATH CALCULATIONS ADDING COMMENTS TO YOUR FUNCTIONS UNDERSTANDING THE ACCESS-RELATED OBJECTS UNDERSTANDING ACCESS AND SUB-PROCEDURE USE USING THE APPLICATION OBJECT EFFECTIVELY DEFINING YOUR WORK AREA WITH THE WORKSPACES COLLECTION WORKING WITH THE DBENGINE OBJECT USING THE CURRENTDB AND RELATED OBJECTS UNDERSTANDING THE DATABASE OBJECTS ACCESSING SPECIAL COMMANDS WITH THE DOCMD OBJECT UNDERSTANDING SQL: A QUICK OVERVIEW CREATING A SQL QUERY THE EASY WAY USING THE SQL QUERY ADDING FORM-RELATED APPLICATIONS CREATING AUTOMATIC APPLICATIONS UNDERSTANDING WHY YOU SHOULD WORK WITH MORE THAN ONE APPLICATION STARTING AND STOPPING EXTERNAL APPLICATIONS DECIDING HOW TO PERFORM THE TASK CREATING THE WIN32 API CALLS ENCAPSULATING THE PROCESS CALLING THE ACCESSEXTERNALPROGRAM FUNCTION PROCESSING OUTLOOK E-MAIL MESSAGES WITH WORD SENDING E-MAIL MESSAGES WITH OUTLOOK SENDING NOTES FROM WORD TO EXCEL USING MAGAZINES AND PERIODICALS TRADITIONAL PAPER MAGAZINES AND PERIODICALS FREE ELECTRONIC NEWSLETTERS USING RSS TO OBTAIN THE LATEST INFORMATION FINDING INTERESTING NEWSGROUPS AND LIST SERVERS MICROSOFT-SPECIFIC NEWSGROUPS THIRD-PARTY NEWSGROUPS LIST SERVERS THAT YOU ACCESS THROUGH E-MAIL AND WEB SITES LOCATING JUST THE RIGHT CODE GETTING TOOLS TO MAKE PROGRAMMING EASIER DOWNLOADING ACTIVEX CONTROLS AND THIRD-PARTY COMPONENTS USING THE AUTHOR AS A RESOURCE DEBUGGING YOUR CODE BEFORE MAKING CHANGES USING SEARCH-AND-REPLACE TO YOUR ADVANTAGE ASKING OTHERS ABOUT A FIX FINDING FIXES THAT MICROSOFT PROVIDES FINDING THIRD-PARTY SOLUTIONS TO PROBLEMS MAINTAINING A LOG GRABBING HELPFUL CODE FROM VBA HELP GETTING YOUR USERS TO HELP YOU CREATING AN UPDATE PLAN LEARNING WHEN THAT OLD CODE WON’T UPDATE USING THE CODE IN THIS BOOK FOR UPDATES