Excel Programming Weekend Crash Course

* Thirty clearly defined lessons take the reader from understanding the parts of an Excel application into building applications to work with data, formulas, charts, and the enhanced XML capabilities of the new Excel "X" * Book is designed to teach the core concepts of Excel over a weekend or in just fifteen hours, with each session being thirty minutes * Applicable to Excel 2000, Excel 2002, and the latest release, Excel "X" * Helps Excel power users in fields such as accounting, finance, operations management, and market research to begin automating data manipulation in Excel quickly, so they can handle real-world projects * A how-to guide to using Excel's programmability to create custom data-processing and analysis solutions * Covers security, debugging, and error handling * Companion Web site includes sample files, projects, and test enginewith self-assessment exam" Team DDU......Page 1 Cover......Page 4 Contents at a Glance......Page 14 Acknowledgments......Page 12 Preface......Page 8 Credits......Page 7 About the Author......Page 6 Contents......Page 16 FRIDAY......Page 27 PART I–Friday Evening......Page 29 Advantages of Programming......Page 30 Programming Fundamentals......Page 31 The VBA Language......Page 32 Objects......Page 33 Components and Automation......Page 34 Designing Your Custom Application......Page 35 Creating and Naming the Program......Page 36 Writing the Code......Page 37 Running the Program......Page 39 Code and Project Organization......Page 42 Importing and Exporting Modules......Page 44 Editing Tools......Page 45 The Property Window......Page 47 The VBA Editor Menus......Page 48 Using Macros in Programming......Page 49 Recording a Macro......Page 50 Cell References in Macros......Page 51 Online Help......Page 52 Understanding Properties and Methods......Page 56 The Importance of Object References......Page 57 Working with Collections......Page 58 The Object Hierarchy......Page 60 Creating and Opening Workbooks......Page 61 Saving and Closing Workbooks......Page 62 Referencing Workbooks......Page 63 E-Mailing a Workbook......Page 64 Other Workbook Methods and Properties......Page 65 Adding and Deleting Worksheets......Page 66 Referencing Worksheets......Page 68 Copying and Moving Worksheets......Page 69 VBA Syntax Fundamentals......Page 72 Comments......Page 73 Employing Constants......Page 74 Declaring and Using Variables......Page 75 Numeric Variables......Page 76 String Variables......Page 77 Object Variables......Page 78 The Variant Type......Page 79 Static Arrays......Page 80 Dynamic Arrays......Page 81 User-Defined Types......Page 84 Enumerations......Page 85 Understanding Variable Scope......Page 86 SATURDAY......Page 91 PART II–Saturday Morning......Page 93 The Assignment Operator......Page 94 Numerical Operators......Page 95 Logical Operators......Page 96 Comparison Operators......Page 97 Operator Precedence......Page 99 SESSION 6–Control Constructs......Page 102 The IfThen Statement......Page 103 The Select Case Statement......Page 105 The DoLoop Statement......Page 106 The ForNext Statement......Page 108 The For EachNext Statement......Page 109 The Goto Statement......Page 110 SESSION 7–Procedures and Modules......Page 112 Sub Procedures......Page 113 Optional Arguments......Page 114 ByVal and ByRef Arguments......Page 115 Calling Procedures......Page 116 Argument Type Checking......Page 117 Function Procedures......Page 118 Variables in Procedures......Page 119 Storing Procedures......Page 120 The Date Data Type......Page 122 Date/Time Values......Page 123 Date Calculations......Page 124 Date and Time Details......Page 125 Formatting Date/Time Values......Page 128 The MsgBox Function......Page 132 InputBox Function......Page 135 Searching for Text......Page 136 The StrConv Function......Page 137 The Val Function......Page 138 Working with ASCII Values......Page 139 The Asc, AscB, and AscW Functions......Page 140 The Left and Right Functions......Page 142 The Mid Statement......Page 143 Other String Functions......Page 144 The Range Object......Page 146 Manipulating Ranges......Page 147 Relative Ranges......Page 148 Other Range References......Page 152 Working with Comments......Page 153 Reading Data from Ranges......Page 155 Naming Ranges......Page 157 The Selection Property......Page 159 PART II–Saturday Morning Part Review......Page 162 PART III -Saturday Afternoon......Page 165 Referencing Cells with the Cells Property......Page 166 Referencing by Row and Column......Page 167 The SpecialCells Method......Page 170 Manipulating Columns and Rows......Page 172 Adding and Deleting Rows and Columns......Page 174 Cell References in Formulas......Page 176 Absolute Cell References......Page 177 Named Range References......Page 179 Referencing Cells in Other Worksheets and Workbooks......Page 180 Logical Operators......Page 181 Avoiding Circular References......Page 182 Controlling Formula Calculation......Page 183 Excel's Built-in Functions......Page 186 Using Excel Functions in Formulas......Page 187 Financial Functions......Page 188 Date and Time Functions......Page 191 Math and Trig Functions......Page 192 Text Functions......Page 193 The WorksheetFunction Object......Page 194 Cell Formatting......Page 196 Number Formatting......Page 198 Font Formatting......Page 200 Alignment and Orientation of Data......Page 201 Cell Borders......Page 203 Cell Backgrounds......Page 206 Changing Row and Column Size......Page 209 Finding Data......Page 212 The Find Method......Page 213 The FindNext and FindPrevious Methods......Page 214 Replacing Data......Page 217 Displaying and Hiding Toolbars......Page 220 Creating a New Toolbar......Page 222 Adding and Removing Toolbar Buttons......Page 223 Running Programs from Toolbar Buttons......Page 224 Distributing Toolbars......Page 227 Hiding and Displaying Toolbars in VBA Code......Page 228 PART IV–Saturday Evening......Page 233 Embedded Charts and Chart Sheets......Page 234 Chart Sheets......Page 236 Identifying Data to Be Plotted......Page 237 Specifying Chart Type......Page 239 Displaying Chart Titles......Page 241 Chart Axis Titles......Page 242 Working with Fonts in a Chart......Page 244 The ChartWizard Method......Page 247 Naming and Referencing Charts......Page 250 Locking Charts......Page 251 The ChartObject Object......Page 252 Using Scatter Charts......Page 256 Scatter Chart Types......Page 258 Changing Axis Range......Page 259 Printing Charts......Page 260 Overview of User Forms......Page 264 Form Designer Basics......Page 265 Designing the Interface......Page 266 Setting Properties......Page 267 Appearance Properties......Page 268 Behavior and Position Properties......Page 269 Form Methods......Page 270 Displaying, Using, and Hiding Forms......Page 271 A Simple User Form Example......Page 272 A Summary of Controls......Page 276 Common Control Properties......Page 277 The ComboBox Control......Page 278 The CommandButton Control......Page 281 The OptionButton Control......Page 283 The RefEdit Control......Page 285 The TextBox Control......Page 287 The ToggleButton Control......Page 288 SUNDAY......Page 293 PART V–Sunday Morning......Page 295 Using Control Events......Page 296 The Form Grid......Page 298 Control Placement and Alignment Tools......Page 299 Focus and the Tab Order......Page 303 Planning the Project......Page 308 Part 2: Designing the Form......Page 310 Part 3: Writing the Initialization Code......Page 311 Part 4: Restricting Zip Code Entry to Digits......Page 312 Part 5: Writing the Data Validation Code......Page 313 Part 6: Completing the Project......Page 314 Part 7: Testing the Project......Page 317 Event Categories......Page 320 Event Handler Code......Page 321 Enabling and Disabling Events......Page 322 Workbook Events......Page 323 The Open Event......Page 324 Worksheet Events......Page 325 Using the Change Event for Data Validation......Page 326 Application Events......Page 328 Writing Application Event Procedures......Page 329 The WorkbookBeforeClose Event......Page 330 Other Events......Page 331 The OnTime Event......Page 332 The OnKey Event......Page 333 Workbook Protection......Page 336 Worksheet Protection......Page 337 VBA Code and Worksheet Protection......Page 339 Protecting Your VBA Code......Page 340 Macro Security......Page 341 Using Digital Certificates to Sign Macros......Page 342 What Are Bugs?......Page 346 Avoiding Bugs......Page 347 Breakpoints......Page 348 Step Commands......Page 349 Using Watches......Page 350 Distributing an Application......Page 352 Class Fundamentals......Page 354 Instantiating Classes......Page 355 Creating Property Procedures......Page 356 The Property Variable......Page 357 Connecting the Property to the Property Procedures......Page 358 Array Properties......Page 359 Read-Only Properties......Page 360 Property Validation......Page 361 A Class Demonstration......Page 362 PART VI–Sunday Afternoon......Page 371 What's a Runtime Error?......Page 372 The Causes of Errors......Page 373 Preventing Errors......Page 374 Trapping Errors......Page 375 The Err Object......Page 376 Error-Handling Code......Page 377 Some Error-Handling Examples......Page 378 Notifying the User of an Error......Page 379 Using an Error as a Programming Tool......Page 380 Databases and Excel......Page 382 Database Fundamentals......Page 383 Sorting Data......Page 384 Filtering Data......Page 385 Data Entry Forms......Page 388 Database Functions......Page 389 Add-Ins versus Workbooks......Page 392 Creating an Add-In......Page 394 Using Excel's Add-In Manager......Page 395 Functions in Add-Ins......Page 396 Using VBA to Manipulate Add-Ins......Page 397 An Add-In Demonstration......Page 398 Code the Function......Page 399 Create the Toolbar......Page 400 Testing the Add-In......Page 402 Online Help for Excel......Page 404 Putting Help in the Worksheet......Page 405 Putting Help in a Separate Worksheet......Page 406 HTML Help......Page 409 Web-Based Help......Page 411 Friday Evening Review Answers......Page 416 Saturday Morning Review Answers......Page 417 Saturday Afternoon Review Answers......Page 418 Saturday Evening Review Answers......Page 419 Sunday Morning Review Answers......Page 420 Sunday Afternoon Review Answers......Page 421 Troubleshooting......Page 422 Index......Page 424

Get up to speed on Excel programming – in a weekend!

The big day is Monday. That’s when you get to show off what you know about Excel programming. The problem is, you’re not really up to speed. Maybe it’s been a while since you programmed Excel to create custom data processing solutions. Perhaps you’ve just discovered Excel’s adaptability. Or maybe you just like a challenge. In any event, we’ve got a solution for you – Excel Programming Weekend Crash Course. Open the book Friday evening and on Sunday afternoon, after completing 30 fast, focused sessions, you’ll be able to dive right in and start programming and automating Excel. It’s as simple as that.

The Curriculum

Friday

Evening: 4 Sessions, 2 Hours

• Microsoft® Excel and Programming–Why and How
• The VBA Code Editor
• The Excel Object Model
• Syntax and Data in VBA

Saturday

Morning: 6 Sessions, 3 Hours

• Operators
• Control Constructs
• Procedures and Modules
• Working with Dates and Times
• Working with Text
• Using Ranges and Selections

Afternoon: 6 Sessions, 3 Hours

• Working with Columns, Rows, and Cells
• Programming with Custom Formulas
• Programming with Excel's Built-In Functions
• Formatting a Worksheet
• Find and Replace Operations
• Creating Custom Toolbars

Saturday, cont.

Evening: 4 Sessions, 2 Hours

• Introduction to Charts
• Advanced Charting Techniques
• Creating Custom Dialog Boxes with User Forms
• Controls for User Forms

Sunday

Morning: 6 Sessions, 3 Hours

• Advanced User Form Techniques
• A User Form Example
• Working with Events
• Security Considerations
• Debugging and Distributing an Application
• Defining and Using Custom Classes

Afternoon: 4 Sessions, 2 Hours

• Handling Runtime Errors
• Database Tasks
• Creating Add-Ins
• Adding Online Help to Your Application

Thirty clearly defined lessons take the reader from understanding the parts of an Excel application into building applications to work with data, formulas, charts, and the enhanced XML capabilities of the new Excel "X" Book is designed to teach the core concepts of Excel over a weekend or in just fifteen hours, with each session being thirty minutes Applicable to Excel 2000, Excel 2002, and the latest release, Excel 2003 Helps Excel power users in fields such as accounting, finance, operations management, and market research to begin automating data manipulation in Excel quickly, so they can handle real-world projects A how-to guide to using Excel's programmability to create custom data-processing and analysis solutions Covers security, debugging, and error handling Companion Web site includes sample files, projects, and test enginewith self-assessment exam A how-to guide to using Excel's programmability to create custom data-processing and analysis solutions. It helps Excel power users in fields, such as accounting, finance, operations management, and market research to begin automating data manipulation in Excel quickly, so they can handle real-world projects.