Macros Made Easy

Chapter 1 — Introduction

If you are a typical Microsoft Excel user you probably do a number of tasks in Microsoft Excel that are the same day to day or week to week. You may have tried to automate these tasks using Microsoft Excel’s macro recorder and ended up frustrated with the results. And attempts to understand and change the macro instructions that you record may have been equally frustrating.

I too have faced the above challenges and realized that the learnings that I had made would be of use to many others. This book is written for the Microsoft Excel user who wants to make their use of Microsoft Excel easier, who want to automate tasks that are repetitive, and who see macros as a way of improving their skills and knowledge. Many examples have been included in this book to help you learn Visual Basic.

By learning to write macros you will be able to automate repetitious work that you have to do. And by having the computer do the work for you, you will eliminate mistakes and get the work done far faster.

• What You Need
• How To Use This Book
• Conventions In This Book
• Windows Vs MacIntosh
• Where To Get Help
• Where To Write Your Macros
• The Module Menu
• Configuring Your Modules
• The Visual Basic Toolbar
• Editing In A Module
• Using Windows And Split Screens While Editing
• Protecting Your Macros
• Mathematical And Other Operators
• Concatenating Strings
• The Continuation Character
• Comment Lines
• Moving From One Macro To Another
• An Illustrative Macro
• Sharing Macros
• File Format No Longer Supported Problem
• Microsoft Excel Versions

Chapter 2 — Visual Basic Terminology

Like any programming language, Visual Basic has a set of terminology that it uses. Some of the terms are well known and require little explanation. Others are terms that you may not have encountered before. The following is a list of the key Visual Basic terms that are covered in this chapter:

• Object
• Property
• Method
• Argument
• Collections
• Procedure
• Subroutine
• Function
• Macro
• Statement
• Expression

This chapter will give an explanation of these terms along with illustrations so that when you encounter these terms in later chapters you won’t be confused. Also, you will find that other chapters give in-depth coverage on many of these terms.

Chapter 3 — A Sample Macro

The easiest way to begin learning Visual Basic is to begin by writing a macro. This chapter will teach you quite a bit about Visual Basic by having you create a macro that will add, subtract, multiply or divide by any value you specify. And, to help you learn, I’m going to have you make mistakes! You’ll learn faster from mistakes, because that way you will gain experience that will allow you to handle your own mistakes in the future. And, hopefully, avoid the same mistakes!

• Module Setup Requirements
• The First Macro Statements
• Testing Your Macro
• Assigning Your Macro To A Button
• Testing On Formula Cells
• Fixing The Macro For Formula Cells
• Handling Complex Cell Equations
• Using Help To Find Commands
• Using The Mid Function
• Testing The Modified Macro
• Using If And The Left Function
• Handling Text And Blank Cells
• Using The Object Browser
• Using IsNumeric In The Macro
• Dividing All Cells In A Selection By 100
• Making Our Macro Super Powerful
• Handling Errors

Chapter 4 — Using The Macro Recorder

In chapter three I showed you how to write macro code by starting from scratch from within a Visual Basic module. One requirement of that approach is that you know the Visual Basic commands that you need to use. From this book you will learn many of the commands that you will need to know. However, it is difficult to remember all the Visual Basic commands. The macro recorder that is built into Microsoft Excel can serve as a quick way to find a Visual Basic command. It can also be used to create a macro that either meets your needs or serves as the foundation of a macro. Any action that you do on a worksheet or chart can be recorded by the macro recorder.

Future chapters in this book will show you how to enhance macros you have recorded so that they can do far more than repeat a recorded set of instructions.

• How To Use The Macro Recorder
• Absolute And Relative Recording
• Absolute Recording Pitfall
• Macro Recorder Overkill
• Cleaning Up Macro Recorder Code
• Using The Recorder To Insert Code In A Macro

Chapter 5 — Introduction To Variables

In previous chapters, several macros were constructed that used what are called variables. Variables are a way to store information for later use. For example, you can store text, numbers, Boolean values (True or False), references to sheets, cells and workbooks, or sets of text or numbers. The names you use for variables are used to represent this information.

• Variable Names
• Assigning Values To Variables
• Declaring Variables In Macros
• Variable Types
• Using Type Definition Characters
• Insuring You Declare Variables
• Declaration Errors, Pitfalls And Examples
• Boolean Variables
• Date Variables
• Declaring Variables Throughout A Module
• Retaining Variable Values
• Public (Global) Variables
• Constant Variables
• Visual Basic Constants
• Creating Object Variables
• Creating And Using User Defined Types

Chapter 6 — Introduction To Arrays

It is possible to have a variable hold many different values at the same time. Such a variable is called an array. The values it holds are called elements of the array. One of the primary advantages of an array is that you can repeat a series of statements on the array values with only a few macro statements.

If you look around you, you will see many examples of arrays. For example, a book is an array and its elements are the pages in the book. Your house is an array and its elements are the rooms. And your check book is an array, and the elements are the checks.

• Array Names
• Declaring Arrays
• Specifying Elements In An Array
• Assigning Values To An Element
• Array Size
• Declaring Array Size Dynamically
• Determining Array Sizes
• Array Types
• Using For..Next Statements With Arrays
• Sorting Arrays
• Using User Types With Arrays

Chapter 7 — Control Commands

In order to add power to your macros and give you full control over what actions your macros take, you need to be able to use the following Visual Basic statements:

• Do..Loop
• While..Wend
• For..Next
• For Each..Next
• GoTo
• With..End With
• IF..Then
• Select Case

Chapter 8 — More On Macros

The previous chapters in this book have introduced you to macros and showed you how to create a macro by using the Sub and End Sub statements. This chapter will expand on using and creating macros, and share with you techniques for calling one macro from another, the various options you can use when creating a macro, and how to pass values to and from called macros.

• Macro Names
• Calling A Macro From Another Macro
• Jumping To A Called Macro
• Passing Information To A Called Macro
• Passing Arrays And User Type Variables
• Using An Unknown Number Of Arguments
• Determining Array Sizes
• Making Arguments Optional
• Passing Information Back
• Calling Macros That Are In Other Modules
• Use Many Modules Instead Of References
• Private Vs Public Macros
• Using Exit Sub And End To Stop Macros
• Preventing A User From Running A Macro
• Macro Options
• The Right Way To Write A Macro
• Printing Your Modules

Chapter 9 — Creating Functions

User defined functions are like macros in that they consist of statements and are also called procedures. However, a function is different from a macro in that it returns a value. A user defined function can be used just like Microsoft Excel functions, that is, included in your spreadsheets and macros to supply answers that you need. You should consider writing and using a user defined function any time you need a complex formula that is used in multiple cells and is subject to change. A user defined function is very easy to maintain and can be documented with comment statements.

• About Functions
• Functions Versus Macros
• A Sample User Defined Function
• Function Syntax
• Function Names
• Using Variables And Arrays In Functions
• Specifying Types For Function Arguments
• Making Arguments Optional
• Using User Types As Arguments
• Handling An Unknown Number Of Arguments
• Retaining Values After A Function Runs
• Specifying A Type For A Function
• Making Functions Private
• Making Functions Volatile
• Determining The Calling Cell
• Determining An Argument’s Cell
• Calling Functions From Another Workbook
• Functions That Act As Macros
• Using The Function Assistant
• Recalculating Functions
• A Monitoring Function

Chapter 10 — Debugging Your Macros

One of the problems with writing macros is that it is very difficult to get them right the first time. Sometimes the macros will work perfectly. But it’s far more likely that they won’t. Sometimes the problem is very obvious. Other times you become convinced that the computer is wrong and you are right — and the macro still doesn’t work because the computer has the last say!

Problems with macros generally fall into three main categories: syntax errors where a statement is entered incorrectly or keywords or arguments are left out; run-time errors where the syntax is correct but the macro tried to get Visual Basic to do something it can’t do; and logic errors where the macro does what it is told to do, not what you intended for it to do.

This chapter deals with ways to resolve the above situations. It discusses a variety of techniques that will prove useful to you when you can’t get a macro to work properly.

• Syntax Errors
• Run-Time Errors And Logic Errors
• Finding Run-Time And Logic Errors
• Validating Data To Avoid Errors
• Debugging Your Run-Time And Logic Errors
• Debug Buttons On The Toolbar
• How To Pause A Macro
• Using The Stop Command
• Inserting A Breakpoint
• Using Watch Expressions To Pause A Macro
• About The Debug Window
• Illustrating Using Watches
• Stepping Through Your Macro
• Editing In The Debug Panel
• The Immediate Panel
• Seeing The Cell Pointer When You Are Debugging
• Using The Right Mouse Button
• Debugging Functions
• Printing Your Macro

Chapter 11 — Handling Errors

Sooner or later one of your macros will run and an error will occur. For example, the user may enter the name of a file that doesn’t exist or the macro may encounter a situation that it can’t handle. When this happens your macro will crash with an error unless you have anticipated the situation in your macro. This chapter illustrates how to add such error handling code (called error traps) to your macros to handle errors that otherwise would cause your macro to crash.

• Anticipating And Handling Errors
• Break On All Errors Cure
• Using On Error Goto
• Removing Error Handling
• Using Resume And Resume Next
• Using On Error Resume Next
• Using Err And Error(Number)
• Handling Cells With Error Values
• Memory/Out Of Stack Space Problems

Chapter 12 — Objects, Methods, And Properties

Previous chapters have given you an introduction to Visual Basic terminology and gave you a general understanding of objects, methods and properties. This chapter gives additional coverage of these topics along with many examples of their use.

• About Objects And Properties
• Setting Variables So That They Refer To Objects
• About Methods
• Selecting An Object
• About Collections
• Keywords
• Specifying Vs. Selecting

Chapter 13 — Working With Files

When you write macros for Microsoft Excel, one of the key tasks that you will need to do is to work with workbooks and files. For example you may need to open workbooks, switch from one workbook to another, create workbooks, delete workbooks, or write text out to an ASCII file. This chapter addresses these tasks, and provides you with a number of useful examples.

• Activating Workbooks
• Opening Workbooks
• Determining If A Workbook Is Open
• Setting The Current Drive And Directory
• Directory Information / File Exists Test
• Opening All The Files In A Directory
• Checking For Directory Existence
• Saving Workbooks
• A Save All Files Macro
• Closing Workbooks
• Creating Workbooks
• Deleting Files
• Hiding Files
• Displaying Microsoft Excel’s File Dialogs
• Creating Ascii Files From Worksheets
• Opening Ascii Files
• Working Directly With Ascii Files

Chapter 14 — Working With Sheets

When you write macros for Microsoft Excel, one of the key tasks that you will need to do is to work with worksheets. For example you may need to switch from one sheet to another, create or delete sheets or rename sheets. This chapter addresses these needs, and provides you with a number of useful examples.

• Specifying A Sheet
• Determining What Kind Of Sheet A Sheet Is
• Specifying Multiple Sheets
• Using Variables To Refer To Sheets
• Sheet Methods And Properties
• Changing Sheet Names
• Determining If A Sheet Exists
• Hiding Sheets
• Selecting Sheets
• Activating Vs Selecting Sheets
• Deleting Sheets
• Moving Sheets
• Adding Sheets
• Copying Sheets
• Printing And Previewing Worksheets
• Printout Problems

Chapter 15 — Specifying Cells And Ranges

One of the most frequent tasks you will do with macros is to work with cells. Sometimes you may wish to specify a specific cell, for example cell A1, Z88, or D124. Sometimes you may want to specify a block of cells, such as B9:R11 or several blocks, such as C3:E5, R12:S22, instead of just a single cell. Other times you need to specify entire rows or columns.

Once you have specified the cells that you wish to work with, you then either perform an action on these cells or set a property of these cells. This chapter will first cover many techniques on how to specify cell references. Many examples will be given.

• Specifying Cells
• ActiveCell And Selection
• Using Cells(Row, Col) To Specify A Cell
• Using The Offset Method
• Using The Range Method
• A1 Style References
• Using Range Names
• Relative Range References
• Specifying Ranges On Other Sheets
• Using Range To Specify A Single Cell
• Using Range(Cell Ref, Cell Ref) To Specify An Area
• Using Range(Range Ref) To Specify An Area
• Using Range To Specify Multiple Areas
• Using Range With Other Methods
• Specifying Rows
• Determining Row Numbers
• Specifying Columns
• Determining Column Numbers
• Specifying Areas With The Union Method
• Specifying The Intersection Of Two Ranges
• Re-defining A Selection
• Specifying The Current Region
• Specifying One Area Of Many
• Using The SpecialCells Method
• The Equivalent Of End-Arrow
• Several More Examples
• A Select Only Number Cells Function

Chapter 16 — Working With Cells And Ranges

Once you have specified the cell or the range that you wish to work with, you can then use methods or properties of the cells you have specified. For example, you may want to change values, bold cells, or transfer data from one sheet to another. This chapter will cover many of the most frequently used methods and properties that one can use on cells, rows, and columns.

• Insuring The Active Cell Is The Top Left Cell
• Selecting Cells
• The Activate Method
• Counting Cells, Rows, Columns And Areas
• Cell Addresses
• Resizing A Selection
• Creating And Changing Range Names
• Inserting Cells, Rows, And Columns
• Deleting Cells, Rows, And Columns
• Copying Cells, Rows, And Columns
• Cut Method
• Pasting Cells, Rows, And Columns
• Paste Special
• Modifying Cell Formats
• Clearing Cells
• Setting Or Getting A Cell’s Value
• Writing Formulas In Cells
• More On R1C1 Style References
• IsError Function
• Determining The Contents Of A Cell

Chapter 17 — Useful Commands

Earlier chapters in this book discussed Visual Basic commands that helped you control the actions of your macros. This chapter covers in detail important Visual Basic and Microsoft Excel commands that add power to your macros, and provides a set of quick reference tables listing many of the Visual Basic and Microsoft Excel commands along with a brief description of the commands.

To get detailed help on any of the functions and methods mentioned in this chapter, just type its name, place the cursor in the word you typed and press F1. A help screen will then appear that will give you a detailed explanation, the arguments required and quite often an example of its use.

• Accessing The Object Browser
• Using The Object Browser
• Math Commands
• Date Functions
• String Functions
• Sheet And Range Methods And Properties
• File Methods And Properties
• Other Functions/Methods/Properties
• Using Microsoft Excel Functions In Macros
• Controlling Screen Updating
• Turning Off Alert Messages
• Turning Calculation Off And On
• Displaying Status Bar Messages
• Finding Text
• Displaying Message Boxes
• The Microsoft Excel Input Box
• The Visual Basic Input Box
• Finding A String Within A String
• Formatting Text That Appears In Boxes
• Copying Text From Within A String
• Application.Goto

Chapter 18 — Using The Built-In Dialogs

This chapter will provide instructions on how to use Excel’s built-in dialogs in your macros. The next chapter will provide instructions on creating your own dialogs using user forms. Using the built-in dialogs has the advantage that you do not need to either construct a dialog or the code behind the dialog.

• Using Excel’s Built-In Dialog Boxes
• Displaying Microsoft Excel’s File Dialogs

Chapter 19 — Userforms

The previous chapter covered using Excel’s built-in dialogs. This chapter covers creating your own custom dialogs. This gives you a great way to get information from a user. Custom dialogs are created by using user forms from the Visual Basic editor.

• Creating Userforms
• Deleting Userforms, Userform Properties
• The Userform Toolbar
• Userform Code Modules
• Userforms Buttons And Showing Userforms
• Key Points About Userforms And Buttons
• Using Option Buttons In Userforms
• Using Listboxes On Userforms
• Adding Labels (Text) On A User Form
• Using Text Boxes On Userforms
• Userform Combo Edit, Drop Down List Boxes
• Userform Toggle Buttons And Check Boxes
• Using Frames On A Userform
• Scrollbars And Spinners On Userforms
• Using A Ref Edit Box On A Userform
• Creating A Multi-Page Userform
• Using A Tab Strip On A Userform
• Inserting Images On A Userform
• Setting The Tab Order
• Printing From A Userform
• Using Variables To Refer To Userform Objects
• Using Double Clicks To Close A Userform
• Centering UserForms On The Screen

Chapter 20 — Using Buttons And Objects On Sheets

The previous chapter showed you how to create dialog sheets and put such objects as scrollbars, list boxes, and buttons on a dialog box. Microsoft Excel also allows you to put such objects directly on a worksheet or chart. If the object is a button, a macro can be specified to run when the button is clicked. If the object is a list box, spinner or scrollbar, then they can be linked to formulas in your worksheets.

• Creating Buttons On Sheets
• Using One Macro For Many Buttons
• Assigning Macros To Other Objects
• Using Dialog Objects On A Sheet

Chapter 21 — Working With Charts

One of Microsoft Excel’s strengths is the ability to create charts. The more charts that you create, the more manual work you must do to make a common modification to each of the charts. These tasks can easily be automated by a macro.

The purpose of this chapter is to show you how to work with both stand alone chart sheets and embedded charts. This chapter will also provide several chart macro applications.

• Specifying Charts
• Naming Embedded Charts
• A Macro To Select Embedded Charts
• Printing And Previewing Charts
• Chart Objects And Properties
• About Series
• A Macro To Graph Data

Chapter 22 — Working With Toolbars

It is possible with Microsoft Excel to modify the toolbars so that the tools you need are the ones that are on the toolbars. Not only can you assign any of the many tool buttons to your toolbars, you can also assign your macros to buttons which can be added to an existing toolbar or to a new toolbar. And, you can have macros create toolbars and buttons when a workbook is opened and remove them when it is closed.

• Customizing Toolbars
• Assigning Macros To Toolbar Buttons
• Editing Button Images
• Working With Toolbars
• Working With Buttons
• Using A Macro To Create A Toolbar
• Using Your Own Button Images
• Getting Button Numbers The Fast Way
• Attaching A Toolbar To A Workbook

Chapter 23 — Modifying The Menus

Microsoft Excel has over 30 menus that help you in your use of Microsoft Excel. It is possible to further enhance the menus by adding additional commands and menus to them. And, you can add menu items that appear only when a given file is open. In fact, you can remove the Microsoft Excel menus and replace them with custom menus if you are creating a custom Microsoft Excel application.

• About Menus
• Displaying The Menu Editor
• Using The Menu Editor
• Where Menu Changes Are Stored
• Menu Editor Limitations
• Using Macros To Refer To Menus
• Menus And Menu Constants
• Adding, Displaying, And Deleting Menu Systems
• Adding Menus To A Menu Bar System
• Deleting Menus From A Menu Bar System
• Adding Menu Items And Sub Menus
• Deleting Menus And Menu Items
• Restoring Menus And Menu Items
• Menu Item Properties And Appearance

Chapter 24 — Automatic Procedures

Microsoft Excel allows you to create procedures that run automatically when certain events occur. For example, you can have a macro run whenever you open a workbook or when you enter data into a cell. This chapter covers how to create such procedures and provides a number of examples.

• Auto_Open Macros
• Auto_Close Macros
• OnSheetActivate And OnSheetDeactivate
• Redefining Keys
• On Entry Macros

Chapter 25 — Creating And Using Add-Ins

If you wish to distribute macros and applications that you have written, you may want to consider distributing them as an add-in file instead of as a workbook file. The main advantage of an add-in file is that the macro code cannot be accessed by a user. An add-in also makes it easier to use functions. Further, an add-in can be stored in any directory and set to load when Microsoft Excel starts up.

There are several disadvantages to add-ins. One is that most users are not familiar with them and thus how to load them. Another is that the macros in the add-in do not appear in any macro list. Please note that creating an add-in does not prevent a user from seeing your macro code — it just makes it very difficult.

• How To Create An Add-In File
• Modifying Your Macros For Add-In Use
• Loading An Add-In File
• Unloading An Add-In File
• Updating An Add-In File
• Adding Information Descriptions To Your Add-Ins
• Add-Ins And Toolbar Buttons
• Deleting Add-Ins From The Add-In List

Chapter 26 — Help Files

A number of times in this book I have mentioned Windows help files and associating them with macros, menu items, and dialog boxes. Help files provide an excellent means to document your macros and to provide extensive help on applications you may create.

• Creating Help Files
• Linking Help Files To Macros
• Referring To Help Files In Macros
• Accessing Help Files Via Macros And Menus