Excel Programming

Denise Etheridge is a certified public accountant as well as the president and founder of Baycon Group, Inc. She publishes Web sites and authors computer related books. You can visit www.baycongroup.com to view her online tutorials.

HOW TO USE THIS BOOK V 1 USING MACROS AND FORM CONTROLS 2 Introducing Excel Programming 2 Introducing Macros 4 Set Macro Security 6 Create a Digital Signature 7 Record a Macro 8 Assign a Digital Signature to a Macro 10 Run a Macro 12 Create and Launch a Keyboard Shortcut 14 Assign a Macro to the Quick Access Toolbar 16 Delete a Macro 18 Add a Form Control to a Worksheet 20 Assign Values to a Form Control 22 Add a Macro to a Form Control 24 2 USING THE VISUAL BASIC EDITOR 26 Introducing the Visual Basic Editor 26 Activate the Visual Basic Editor 28 Open Visual Basic Editor Windows 30 Set Properties for a Project 32 Set Display Options for the Code Window 34 Add a New Module 36 Remove a Module 38 Hide a Macro 40 Update a Macro 42 3 INTRODUCING VISUAL BASIC FOR APPLICATIONS 44 Create Sub Procedures 44 Create Functions 46 Comment Your Code 48 Reference Cells and Ranges 50 Understanding Variables and Data Types 52 Declare Variables 54 Work with Strings 56 Work with Numbers 58 Create a Constant 60 4 INTRODUCING THE EXCEL OBJECT MODEL 62 Discover the Excel Object Model 62 Access the Excel Object Model Reference 64 Create an Object Variable 66 Change the Properties of an Object 68 Compare Object Variables 70 Using an Object Method 72 Display a Built-in Dialog Box 74 5 UNDERSTANDING ARRAYS 76 Declare an Array 76 Declare a Multidimensional Array 78 Convert a List to an Array 80 Redimension an Array 82 Create a User-Defined Data Type 84 6 CONTROLLING PROGRAM FLOW 86 Create Comparisons 86 Make Use of Logical Operators 87 Employ Do While Loops 88 Create Do Until Loops 90 Create For Next Loops 92 Execute For Each In Loops 94 Create If Then Else Statements 96 Construct Select Case Statements 98 GoTo a Named Location 100 Call a Procedure 102 7 USING EXCEL WORKSHEET FUNCTIONS 104 Work with Excel Worksheet Functions 104 Work with a MsgBox Function 106 Using the InputBox Function 108 Retrieve the Current Date and Time 110 Perform Date and Time Calculations 112 Format a Date Expression 114 Format a Numeric Expression 116 Change the Case of a String 118 Return a Portion of a String 120 8 DEBUGGING MACROS 122 Debug a Procedure with Inserted Breakpoints 122 Using the Watches Window to Debug a Procedure 124 Step through a Procedure 126 Using the Immediate Window 128 Resume Execution When an Error Is Encountered 130 Process a Runtime Error 132 9 WORKING WITH WORKBOOKS AND FILES 134 Open a Workbook 134 Open a Text File as a Workbook 136 Open a File Requested by the User 138 Save a Workbook 140 Save a Workbook in a Format Specified by the User 142 Determine if a Workbook Is Open 144 Close a Workbook 146 Create a New Workbook 148 Delete a File 150 10 WORKING WITH WORKSHEETS 152 Add a Sheet 152 Delete a Sheet 154 Move a Sheet 156 Copy a Sheet 158 Hide a Sheet 160 Change the Name of a Sheet 162 Save a Sheet to Another File 164 Protect a Worksheet 166 Protect a Chart 168 Print a Sheet 170 Sort Sheets by Name 172 11 DEFINING RANGES 174 Using the Range Property 174 Using the Cells Property 176 Combine Multiple Ranges 178 Using the Offset Property 180 Delete a Range of Cells 182 Hide a Range of Cells 184 Create a Range Name 186 Resize a Range 188 Insert a Range 190 Set the Width of Columns in a Range 192 Set the Height of Rows in a Range 194 12 WORKING WITH CELLS 196 Cut and Paste Ranges of Cells 196 Copy and Paste Ranges of Cells 198 Using Paste Special Options When Pasting 200 Add Comments to a Cell 202 Automatically Fill a Range of Cells 204 Copy a Range to Multiple Sheets 206 Add a Border 208 Find Specific Cell Values 210 Find and Replace Values in Cells 212 13 WORKING WITH LISTS 214 Convert a Column of Text into Multiple Columns 214 Perform a Sort 216 Perform a Filter 220 Perform an Advanced Filter 222 Create Subtotals 224 Create Groups 226 Define a List as a Table 228 14 CREATING DIALOG BOXES AND CUSTOMIZING THE RIBBON 230 UserForm Basics 230 Create a Custom Dialog Box 232 Call a Custom Dialog Box from a Procedure 234 Capture Input from a Custom Dialog Box 236 Validate Input from a Dialog Box 240 Create Custom UserForm Controls 242 Create a UserForm Template 244 Customize the Ribbon 246 Create a CustomUI.xml File 250 Add a CustomUI.xml File to a Workbook 252 Add Additional Options to the Ribbon 254 15 WORKING WITH CHARTS 258 Create a Chart Sheet 258 Embed a Chart in a Worksheet 260 Apply Chart Wizard Settings to a Chart 262 Add a New Data Series to a Chart 264 Format Chart Text 266 Create Charts with Multiple Chart Types 268 Add a Data Table to a Chart 270 16 WORKING WITH PIVOTTABLES 272 Create a PivotTable 272 Add Fields to a PivotTable 274 Display Subtotals and Grand Totals 276 Filter a PivotTable 278 Create Groups 279 17 AUTOMATING PROCEDURES WITH EXCEL EVENTS 280 Understanding Excel Events 280 Run a Procedure as a Workbook Opens 284 Run a Procedure before Closing a Workbook 286 Run a Procedure before Saving a Workbook 288 Run a Procedure When Excel Creates a Workbook 290 Execute a Procedure at a Specific Time 294 Execute a Procedure When You Press Keys 296 Monitor a Range of Cells for Changes 298 18 BUILDING ADD-INS 300 Create an Add-In 300 Set Add-In Properties 302 Install Add-Ins 304 Using VBA to Load Add-Ins 306 19 UNDERSTANDING XML 308 Introducing XML 308 Understanding Excel XML Files 310 Open an XML File in Excel as a Table 314 Create an XML Map 316 Import and Export XML Files Using Excel 318 Load XML Files Using VBA 320 Import XML Files Using VBA 322 APPENDIX: VBA QUICK REFERENCE 324 INDEX 342