Excel 2007 VBA Programming For Dummies

Introduction     1Is This the Right Book?     1So You Want to Be a Programmer     2Why Bother?     2What I Assume about You     3Obligatory Typographical Conventions Section     4Check Your Security Settings     4How This Book Is Organized     6Introducing VBA     6How VBA Works with Excel     6Programming Concepts     6Communicating with Your Users     6Putting It All Together     7The Part of Tens     7Marginal Icons     7Get the Sample Files     8Now What?     8Introducing VBA     9What Is VBA?     11Okay, So What Is VBA?     11What Can You Do with VBA?     12Inserting a bunch of text     13Automating a task you perform frequently     13Automating repetitive operations     13Creating a custom command     13Creating a custom button     14Developing new worksheet functions     14Creating complete, macro-driven applications     14Creating custom add-ins for Excel     14Advantages and Disadvantages of VBA     14VBA advantages     15VBA disadvantages     15VBA in a Nutshell     16An Excursion into Versions     18Jumping Right In     21First Things First     21What You'll Be Doing     22Taking the First Steps     23Recording the Macro     23Testing the Macro     25Examining the Macro     25Modifying the Macro     28Saving Workbooks that Contain Macros     29Understanding Macro Security     29More about the NameAndTime Macro     31How VBA Works with Excel     33Working In the Visual Basic Editor     35What Is the Visual Basic Editor?     35Activating the VBE     35Understanding VBE components     36Working with the Project Explorer     38Adding a new VBA module     39Removing a VBA module     39Exporting and importing objects     40Working with a Code Window     40Minimizing and maximizing windows     40Creating a module     42Getting VBA code into a module      42Entering code directly     43Using the macro recorder     45Copying VBA code     47Customizing the VBA Environment     47Using the Editor tab     48Using the Editor Format tab     50Using the General tab     52Using the Docking tab     52Introducing the Excel Object Model     53Excel Is an Object?     54Climbing the Object Hierarchy     54Wrapping Your Mind around Collections     56Referring to Objects     56Navigating through the hierarchy     57Simplifying object references     58Diving into Object Properties and Methods     59Object properties     59Object methods     62Object events     63Finding Out More     63Using VBA's Help system     63Using the Object Browser     64VBA Sub and Function Procedures     67Subs versus Functions     67Looking at Sub procedures     68Looking at Function procedures     68Naming Subs and Functions     69Executing Sub procedures      69Executing the Sub procedure directly     71Executing the procedure from the Macro dialog box     72Executing a macro by using a shortcut key     72Executing the procedure from a button or shape     74Executing the procedure from another procedure     76Executing Function procedures     76Calling the function from a Sub procedure     77Calling a function from a worksheet formula     77Using the Excel Macro Recorder     79Is It Live or Is It VBA?     79Recording Basics     80Preparing to Record     82Relative or Absolute?     82Recording in absolute mode     82Recording in relative mode     83What Gets Recorded?     85Recording Options     86Macro name     87Shortcut key     87Store Macro In     87Description     87Is This Thing Efficient?     88Programming Concepts     91Essential VBA Language Elements     93Using Comments in Your VBA Code     93Using Variables, Constants, and Data Types     95Understanding variables      95What are VBA's data types?     97Declaring and scoping variables     98Working with constants     103Working with strings     105Working with dates     106Using Assignment Statements     106Assignment statement examples     107About that equal sign     107Other operators     108Working with Arrays     109Declaring arrays     109Multidimensional arrays     110Dynamic arrays     111Using Labels     111Working with Range Objects     113A Quick Review     113Other Ways to Refer to a Range     114The Cells property     115The Offset property     116Referring to entire columns and rows     116Some Useful Range Object Properties     117The Value property     117The Text property     118The Count property     118The Column and Row properties     118The Address property     119The HasFormula property     119The Font property     120The Interior property     120The Formula property     121The NumberFormat property     121Some Useful Range Object Methods     122The Select method     122The Copy and Paste methods     123The Clear method     123The Delete method     124Using VBA and Worksheet Functions     125What Is a Function?     125Using Built-in VBA Functions     126VBA function examples     126VBA functions that do more than return a value     128Discovering VBA functions     129Using Worksheet Functions in VBA     132Worksheet function examples     133Entering worksheet functions     136More about Using Worksheet Functions     136Using Custom Functions     137Controlling Program Flow and Making Decisions     139Going with the Flow, Dude     139The GoTo Statement     140Decisions, decisions     141The If-Then structure     141The Select Case structure     146Knocking Your Code for a Loop     149For-Next loops     150Do-While loop     153Do-Until loop     154Looping through a Collection     155Automatic Procedures and Events     157Preparing for the Big Event     157Are events useful?     159Programming event-handler procedures     160Where Does the VBA Code Go?     160Writing an Event-Handler Procedure     161Introductory Examples     163The Open event for a workbook     163The BeforeClose event for a workbook     165The BeforeSave event for a workbook     165Examples of Activation Events     166Activate and deactivate events in a sheet     166Activate and deactivate events in a workbook     167Workbook activation events     168Other Worksheet-Related Events     169The BeforeDoubleClick event     169The BeforeRightClick event     169The Change event     170Events Not Associated with Objects     172The OnTime event     172Keypress events     174Error-Handling Techniques     177Types of Errors     177An Erroneous Example     178The macro's not quite perfect     179The macro is still not perfect      180Is the macro perfect yet?     180Giving up on perfection     181Handling Errors Another Way     182Revisiting the EnterSquareRoot procedure     182About the On Error statement     183Handling Errors: The Details     184Resuming after an error     184Error handling in a nutshell     186Knowing when to ignore errors     186Identifying specific errors     187An Intentional Error     188Bug Extermination Techniques     191Species of Bugs     191Identifying Bugs     192Debugging Techniques     193Examining your code     193Using the MsgBox function     194Inserting Debug.Print statements     195Using the VBA debugger     196About the Debugger     196Setting breakpoints in your code     196Using the Watch window     199Using the Locals Window     201Bug Reduction Tips     201VBA Programming Examples     203Working with Ranges     203Copying a range     204Copying a variable-sized range      205Selecting to the end of a row or column     206Selecting a row or column     207Moving a range     207Looping through a range efficiently     208Prompting for a cell value     209Determining the selection type     210Identifying a multiple selection     211Changing Excel Settings     211Changing Boolean settings     212Changing non-Boolean settings     212Working with Charts     213Modifying the chart type     214Looping through the ChartObjects collection     214Modifying chart properties     215Applying chart formatting     215VBA Speed Tips     216Turning off screen updating     216Turning off automatic calculation     217Eliminating those pesky alert messages     218Simplifying object references     219Declaring variable types     219Using the With-End With structure     220Communicating with Your Users     221Simple Dialog Boxes     223Why Create UserForms?     223The MsgBox Function     224Displaying a simple message box      225Getting a response from a message box     225Customizing message boxes     226The InputBox Function     229InputBox syntax     229An InputBox example     229The GetOpenFilename Method     231The syntax     232A GetOpenFilename example     232Selecting multiple files     234The GetSaveAsFilename Method     235Getting a Folder Name     236Displaying Excel's Built-in Dialog Boxes     236UserForm Basics     239Knowing When to Use a UserForm     239Creating UserForms: An Overview     240Working with UserForms     241Inserting a new UserForm     241Adding controls to a UserForm     242Changing properties for a UserForm control     243Viewing the UserForm Code window     244Displaying a UserForm     245Using information from a UserForm     245A UserForm Example     246Creating the UserForm     246Adding the CommandButtons     247Adding the OptionButtons     248Adding event-handler procedures     250Creating a macro to display the dialog box     251Making the macro available     252Testing the macro     253Using UserForm Controls     255Getting Started with Dialog Box Controls     255Adding controls     255Introducing control properties     257Dialog Box Controls: The Details     259CheckBox control     259ComboBox control     260CommandButton control     261Frame control     262Image control     262Label control     263ListBox control     264MultiPage control     265OptionButton control     266RefEdit control     267ScrollBar control     267SpinButton control     268TabStrip control     269TextBox control     269ToggleButton control     270Working with Dialog Box Controls     270Moving and resizing controls     270Aligning and spacing controls     271Accommodating keyboard users     272Testing a UserForm     273Dialog Box Aesthetics     274UserForm Techniques and Tricks      275Using Dialog Boxes     275A UserForm Example     275Creating the dialog box     276Writing code to display the dialog box     278Making the macro available     279Trying out your dialog box     279Adding event-handler procedures     280Validating the data     282Now the dialog box works     282More UserForm Examples     282A ListBox example     282Selecting a range     287Using multiple sets of OptionButtons     288Using a SpinButton and a TextBox     289Using a UserForm as a progress indicator     291Creating a tabbed dialog box     295Displaying a chart in a dialog box     296A Dialog Box Checklist     297Accessing Your Macros Through the User Interface     299CommandBars and Excel 2007     299Excel 2007 Ribbon Customization     301Working with CommandBars     304Commanding the CommandBars collection     304Listing all shortcut menus     304Referring to CommandBars     305Referring to controls in a CommandBar     306Properties of CommandBar controls     307VBA Shortcut Menu Examples     309Resetting all built-in right-click menus     309Adding a new item to the Cell shortcut menu     309Disabling a shortcut menu     311Creating a Custom Toolbar     312Putting It Alt Together     315Creating Worksheet Functions and Living to Tell about It     317Why Create Custom Functions?     317Understanding VBA Function Basics     318Writing Functions     319Working with Function Arguments     319Function Examples     320A function with no argument     320A function with one argument     320A function with two arguments     322A function with a range argument     323A function with an optional argument     324A function with an indefinite number of arguments     326Functions That Return an Array     327Returning an array of month names     327Returning a sorted list     328Using the Insert Function Dialog Box     330Displaying the function's description     330Argument descriptions     331Creating Excel Add-Ins      333Okay...So What's an Add-In?     333Why Create Add-Ins?     334Working with Add-Ins     335Add-in Basics     336An Add-in Example     337Setting up the workbook     337Testing the workbook     339Adding descriptive information     340Protecting the VBA code     341Creating the add-in     341Opening the add-in     341Distributing the add-in     342Modifying the add-in     342The Part of Tens     345Ten VBA Questions (And Answers)     347The Top Ten Questions about VBA     347(Almost) Ten Excel Resources     351The VBA Help System     351Microsoft Product Support     351Internet Newsgroups     352Internet Web Sites     353Excel Blogs     353Google     353Local User Groups     354My Other Book     354Index     355