Programming Excel with VBA: a practical real-world guide

At a Glance; Contents; About the Author; About the Technical Reviewer; Acknowledgments; Introduction; Chapter 1: Understanding Visual Basic for Applications (VBA); What Is Macro Code?; The VBA Environment; Modules: The VBA Documents; Standard and Class Modules; Class Modules; The VBA Language; Procedures: The VBA Code; Using Function and Sub Procedures; Calculating Age in Years; Test Procedure Codes Using VBA Immediate Window; Using Your Function Procedure Inside Excel; Executing Code Procedures Step-by-Step; Variable Declaration; Implicit vs. Explicit Variable Declaration.

Using Option ExplicitVariable Types; Array Declares; Variable Scope and Lifetime; Using the Static Statement to Hold Any Variable Value; Using Code Module Variables; Using Private Code Module Variables; Create a Flow Chart for the Algorithm of Complex Procedures; Comment Your Code!; Using Public Code Module Variables; Public Procedures and Variables Constitute the Module Interface; Using Enumerators; Passing Arguments by Reference or by Value; Using a Naming Convention; Using Property Procedures; Property Procedures Allow Greater Control of Private Variables.

VBA Statements, Functions, and InstructionsUsing VBA Instructions; VBA Logical Decision Instructions; Making Decisions with If & nd If Instructions; Making Decisions with the Select Case & nd Select Instruction; VBA Looping Statements; The For & ext Statement; The For Each & ext Statement; The While & nd and Do & oop Statements; Using Event Procedures; Using Class Modules; Declaring and Raising Events on Object Code Modules; Using VBA UserForms; The VBA Me Keyword; Evoking a VBA Procedure from an Excel Worksheet; Two Special VBA Functions: MsgBox and InputBox; Using MsgBox(); Using InputBox.

Dealing with VBA ErrorsThe On Error Resume Next Instruction; Setting an Error Trap; Protecting Your VBA Code; Conclusion; Summary; Chapter 2: Programming the Microsoft Excel Application Object; The Microsoft Excel Object Model; The Application Object; Using Application Properties to Control the Way the Excel Interface Behaves; Using Application Methods to Show Excel File Dialogs; Using the FileDialog Method; Open One Single File; Open Many Files; Using the GetOpenFileName and GetSaveAsFileName Methods; Open One Single File; Open Many Files; Using Application InputBox Method.

Using Application OnTime MethodUsing Application Events to React to User Actions; Creating an Excel. Application Object Reference; Firing Application Events; Using Class Modules to Control Application Object Events; Using a Class Module to Control Sheet Tab Name Changes; Chapter Summary; Chapter 3: Programming the Microsoft Excel Workbook Object; The Workbook Object; Using Workbook Object Events; Workbook Open Event and the frmSplashScreen UserForm; Implementing a UserForm Timer; Using the Application. OnTime Method; Using the VBA Timer() Function; Using the UserForm Repaint Method.