VBA and Macros for Microsoft Excel

Introduction.

• Getting Results with VBA.
• What Is in This Book.
• A Brief History of Spreadsheets and Macros.
• The Future of VBA and Excel.

I. FIRST STEPS UP THE VBA LEARNING CURVE.

• 1. Unleash the Power of Excel with VBA!
  Every intermediate Excel user has tried to record an Excel macro and it never works the second day. You will learn why this happens, how Microsoft failed in implementing the macro recorder, and how to bridge the gap from Macro Recorder to useful code. Covers the VBA environment, debugging, watch window.

  • 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.

• 2. This Sounds Like BASIC, So Why Doesn't It Look Familiar?
  The layman’s introduction to Object Oriented Programming. Why VBA code doesn’t look anything like the BASIC we learned in high school.

  • 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.

• 3. Referring to Ranges.
  Excel is cells, ranges, worksheets. You will learn the language of specifying cells

  • 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

• 4. User-Defined Functions.
  30 real-life example functions that you can put to productive use.

  • Creating User-Defined Functions
  • Useful Custom Excel Functions

• 5. Looping.
  Covers the usual “procedural” loops plus the powerful loops available in VBA. The macro recorder will NEVER record a loop, so this is a key stepping-stone from someone trying to record macros to being able to write their own useful programs.

  • For...Next Loops
  • Do Loops
  • The VBA Loop: For Each

• 6. R1C1 Style Formulas.
  Microsoft introduced R1C1 style of cell references, but they were beaten by the “A1” style made popular by Lotus. The macro recorder enters all formulas in R1C1 style and for a really good reason. We’ll get people up to speed on this arcane formula style and show why it can be your friend.

  • 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

• 7. Names.
  Even advanced users may not understand that identical range names can exist on all worksheets. Discuss the difference between Workbook-level names and Worksheet-level names. Explain how these differences can trip up your code.

  • Global Versus Local Names
  • Adding Names
  • Deleting Names
  • Types of Names
  • Hiding Names
  • Checking for the Existence of a Name

• 8. Event Programming
  How to run macros when the workbook opens, cells change, etc. There is incredible power in using worksheet-level event handlers and most people don’t realize or believe that it is in there.

  • Levels of Events
  • Using Events
  • Workbook Events
  • Worksheet Events
  • Chart Sheet Events
  • Application-Level Events

• 9. UserForms-An Introduction.
  The basics of how to interact with the user. Again – this will never be learned from the macro recorder.

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

II. AUTOMATING EXCEL POWER IN VBA.
Part II is for the Excel power user. You can create charts, etc. with your eyes closed and the power in this book is learning how to automate this with VBA.

• 10. Charts.
  Everything you need to know to create charts with 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

• 11. Data Mining with Advanced Filter.
  Again, this is an arcane command that is rarely used in the Excel user interface but is incredibly powerful when used in macros.

  • 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

• 12. Pivot Tables.
  Pivot tables are the most important feature in Excel. This chapter will cover how to create pivot tables using VBA.

  • 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

• 13. Excel Power.
  30 more awesome code examples showing how to do common Excel tasks using VBA. Here, you will find code samples submitted by regulars from the MrExcel board. Read as these contributors share their favorite code: Russell Hauf, Richie Sills, Nate Oliver, Tom Urtis, Tommy Miles, Colo, Suat Ozgur, XLDennis, Ivan Moala, Juan Pablo Gonzalez, Wei Jiang and Daniel Klann.

  • 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

• 14. Reading from and Writing to the Web.
  Web Queries are new to Excel, but many a day-trader tries to use them to pull real-time stock quotes into Excel. We will show how to set up a web query in VBA and how to use Application.OnTime to poll new data from the web every second or minute.

  • Getting Data from the Web
  • Using Streaming Data
  • Using Application
  • OnTime to Periodically Analyze Data
  • Publishing Data to a Web Page

• 15. XML in Excel 2003 Professional.
  XML is the great promise for Office 2003. Data can be repurposed from one application to another. The problem is, no one has XML data or can figure out how to use XML. This chapter will offer a beginner’s guide to using XML and walk through three real-life examples.

  • 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

• 16. Automating Word.
  How to produce letters in Word using Excel data.

  • Early Binding
  • Late Binding
  • Creating and Referencing Objects
  • Word's Objects

III. TECHIE STUFF YOU WILL NEED TO PRODUCE APPLICATIONS FOR THE ADMINISTRATOR TO RUN.
If your goal is to produce an application that you will give to someone else to run, then these techie topics have to be addressed.

• 17. Arrays.
  People run from arrays, but this chapter will show how you can make your code run twice as fast using Arrays.

  • 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

• 18. Text File Processing.
  Excel is limited to 65,536 rows and eventually someone has to read a file that is larger than that. This chapter shows how.

  • Importing from Text Files
  • Writing Text Files

• 19. Using Access as a Back End to Enhance Multi-User Access to Data.
  As Excel apps gain hold, you get someone who needs to have two co-workers working on the same data at the same time. This is a common technique for a VB programmer, but very foreign to VBA programmers. For multi-user apps, it is a lifesaver.

  • 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

• 20. Creating Classes, Records, and Collections.
  Powerful techniques available with classes and collections.

  • 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)

• 21. Advanced UserForm Techniques.
  The details on Userforms that would have scared you away if they were in Chapter 9.

  • 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

• 22. Windows Application Programming Interface (API).
  Introduction to API functions. How to use them. 10 Examples that you can use today and how to learn more.

  • What Is the Windows API? Understanding an API Declaration
  • Using an API Declaration
  • API Examples
  • Finding More API Declarations

• 23. Handling Errors.
  The last thing you need is to hand an application off to an admin, leave for vacation, then have your boss call you back from Maui because of an unhandled error that's brought the company to a halt. This chapter will teach you how to bulletproof the applications

  • 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

• 24. Using Custom Menus to Run Macros.
  How to run macros from custom menus.

  • Creating a Custom Menu
  • Creating a Custom Toolbar
  • Other Ways to Run a Macro

• 25. Add-Ins.
  How to package your application as an Add-In.

  • 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

• 26. Case Study: Designing an Excel Application.
  Microsoft MVP Tushar Mehta reveals the steps in developing a real application.

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