VBA and Macros for Microsoft Excel

Microsoft Visual Basic for Applications (VBA) is a powerful development technology for customizing desktop applications and integrating them with existing data and systems, enabling customers to buy off-the-shelf software and customize it to meet their specific needs. This is a big cost-saving solution for most business who cannot afford to build programs from scratch. There are an estimated 65 million Excel users. Many are Excel power users, but few have figured out how to effectively use Excel VBA. This book teaches the skills necessary to use Microsoft VBA to customize the Excel spreadsheet. No prior programming knowledge is assumed. It takes a reader who has, perhaps, built a couple of macros from a standing start to a complete knowledge of the VBA language. Topics covered include: event programming, user forms, charts, pivot tables, Web queries, multi-dimensional arrays, classes, records, and collections, and more

Table of Contents:

Introduction.

Getting Results with VBA. What Is in This Book. A Brief History of Spreadsheets and Macros. The Future of VBA and Excel. Special Elements and Typographical Conventions. Next Steps.

I. FIRST STEPS UP THE VBA LEARNING CURVE.

The Power of Excel. Barriers to Entry. Knowing Your Tools-The Visual Basic Toolbar. Macro Security. Overview of Recording, Storing, and Running a Macro. Running a Macro. Understanding the Visual Basic Editor (VBE). Examining Code in the Programming Window. Running the Macro on Another Day Produces Undesired Results. Frustration. Next Steps: Learning VBA Is the Solution.

I Can't Understand This Code. Understanding the Parts of VBA "Speech". Is VBA Really This Hard? No! Examining Recorded Macro Code-Using the VB Editor and Help. Using Debugging Tools to Figure Out Recorded Code. The Ultimate Reference to All Objects, Methods, Properties. Five Easy Tips for Cleaning Up Recorded Code. Putting It All Together-Fixing the Recorded Code. Next Steps.

The Range Object. Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range. Referencing Ranges in Other Sheets. Referencing a Range Relative to Another Range. Using the Cells Property to Select a Range. Using the Offset Property to Refer to a Range. Using the Resize Property to Change the Size of a Range. Using the Columns and Rows Properties to Specify a Range. Using the Union Method to Join Multiple Ranges. Using the Intersect Method to Create a New Range from Overlapping Ranges. Using the IsEmpty Function to Check Whether a Cell Is Empty. Using the CurrentRegion Property to Quickly Select a Data Range. Using the Areas Collection to Return a Non-contiguous Range. Next Steps.

Creating User-Defined Functions. Useful Custom Excel Functions. Next Steps.

For…Next Loops. Do Loops. The VBA Loop: For Each. Next Steps.

Referring to Cells: A1 Versus R1C1 References. Switching Excel to Display R1C1 Style References. The Miracle of Excel Formulas. Explanation of R1C1 Reference Style. Conditional Formatting-R1C1 Required. Array Formulas Require Conditional Formatting. Next Steps.

Global Versus Local Names. Adding Names. Deleting Names. Types of Names. Hiding Names. Checking for the Existence of a Name. Next Steps.

Levels of Events. Using Events. Workbook Events. Worksheet Events. Chart Sheet Events. Application-Level Events. Next Steps.

User Interaction Methods. Creating a Userform. Calling and Hiding a Userform. Using Basic Form Controls. Using the Multipage Control to Combine Forms. Next Steps.

II. AUTOMATING EXCEL POWER IN VBA.

Overview. Embedded Charts Versus ChartSheets. Creating a Chart with VBA. Using Object Variables to Streamline Code. The Anatomy of a Chart. Table of Chart Types. Details of Various Chart Types. Interactive Charts. Exporting Charts as Images. Drawing with X-Y Charts. Custom Charts with VBA. Next Steps.

Advanced Filter Is Easier in VBA Than in Excel. Using Advanced Filter to Extract a Unique List of Values. Using Advanced Filter with Criteria Ranges. Using "Filter in Place" in Advanced Filter. The Real Workhorse: xlFilterCopy with All Records Instead of Unique Records Only. AutoFilters. Next Steps.

Versions. Creating a Vanilla Pivot Table in Excel Interface. Building a Pivot Table in Excel VBA. Revenue by Customer for a Product Line Manager. Handling Additional Annoyances. Product Profitability-Issues with Two or More Data Fields. Summarizing Date Fields with Grouping. Advanced Pivot Table Techniques. Sum, Average, Count, Min, Max, and More. Reporting Percentages. Next Steps.

Using VBA to Extend Excel. File Operations. Combining and Separating Workbooks. Working with Cell Comments. Utilities to Wow Your Clients. Techniques for VBA Pros. Cool Application. Next Steps.

Getting Data from the Web. Using Streaming Data. Using Application.OnTime to Periodically Analyze Data. Publishing Data to a Web Page. Next Steps.

What Is XML? Simple XML Rules. Universal File Format. XML as the New Universal File Format. The Alphabet Soup of XML. Using XML to Round-Trip a Workbook from Excel to HTML and Back. Next Steps.

Early Binding. Late Binding. Creating and Referencing Objects. Word's Objects. Next Steps.

III. TECHIE STUFF YOU WILL NEED TO PRODUCE APPLICATIONS FOR THE ADMINISTRATOR TO RUN.

Declare an Array. Fill an Array. Empty an Array. Arrays Can Make It Easier to Manipulate Data, But Is That All? Dynamic Arrays. Passing an Array. Next Steps.

Importing from Text Files. Writing Text Files. Next Steps.

ADO Versus DAO. The Tools of ADO. Adding a Record to the Database. Retrieving Records from the Database. Updating an Existing Record. Deleting Records via ADO. Summarizing Records via ADO. Other Utilities via ADO. Next Steps.

Inserting a Class Module. Trapping Application and Embedded Chart Events. Creating a Custom Object. Using a Custom Object. Using Property Let and Property Get to Control How Users Utilize Custom Objects. Collections. User-Defined Types (UDTs). Next Steps.

Using the UserForm Toolbar in the Design of Controls on UserForms. Controls and Collections. More UserForm Controls. Tabstrip. RefEdit. Modeless Userforms. Hyperlinks in Userforms. Adding Controls at Runtime. Using a Scrollbar as a Slider to Select Values. Adding Help Tips to Controls. Tab Order. Coloring the Active Control. Transparent Forms. Next Steps.

What Is the Windows API? Understanding an API Declaration. Using an API Declaration. API Examples. Finding More API Declarations. Next Steps.

What Happens When an Error Occurs. Basic Error Handling with the On Error GoTo Syntax. Generic Error Handlers. Train Your Clients. Errors While Developing Versus Errors Months Later. The Ills of Protecting Code. More Problems with Passwords. Errors Caused by Different Versions. Next Steps.

Creating a Custom Menu. Creating a Custom Toolbar. Other Ways to Run a Macro. Next Steps.

Characteristics of Standard Add-Ins. Converting an Excel Workbook to an Add-In. Having Your Client Install the Add-In. Using a Hidden Workbook as an Alternative to an Add-In. Next Steps.

About Tushar Mehta. Using Excel for More Than Number Crunching. The Solution. Implementing the Solution in Excel and VBA. Summary. Next Steps.