View Cart

Quantity
Discounts

Money back
guarantee

Macros Made Easy

Order online from our secure service - Get delivery in minutes!
Order Risk Free - Money back guarantee if not satisfied

   US $19.95  
Buy individually or as part of the Productivity Suite and save!

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

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.

The following are the topics in this chapter:

• 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

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!

The following are the topics in this chapter:

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

The following are the topics in this chapter:

• 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 orFalse) references to sheets cells and workbooks or sets of text or numbers. The names you use for variables are used to represent this information.

The following are the topics in this chapter:

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

The following are the topics in this chapter:

• 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 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 theSub andEnd Sub statements. This chapter will expand on using and creating macros and share with you techniques for calling one macro from another of the various options you can use when creating a macro and how to pass values to and from called macros.

The following are the topics in this chapter:

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

The following are the topics in this chapter:

• 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 vary difficult to get them right the first time. Sometimes the macros will work perfectly. But its 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. Your macro won't run until syntax errors have been fixed.

Run-time errors where the syntax is correct but the macro tried to get Visual Basic to do something it can't do. For example assigning a text string to a variable that is declared as an integer. Or incorrectly entering a formula in a cell.

Logic errors where the macro does what it is told to do not what you intended for it to do. For example not doing aDo..Loop because you used anUntil test instead of aWhile test. Or a situation you didn't think of occurred and the macro was not written to handle it.

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.

The following are the topics in this chapter:

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

The following are the topics in this chapter:

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

The following are the topics in this chapter:

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

The following are the topics in this chapter:

• 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 to these needs and provides you with a number of useful examples.

The following are the topics in this chapter:

• 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. For example you may want to copy the selection to the clipboard. Or you may want to bold the selected cell or range.

This chapter will first cover many techniques on how to specify cell references. Many examples will be given. The following chapter will then cover Visual Basic methods that perform actions on cells. It will also cover how to return information on cells and modify cell properties.

The following are the topics in this chapter:

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

The following are the topics in this chapter:

• 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. The past several chapters have expanded on these commands and taught you a number of fundamental commands related to cells sheets and workbooks. This chapter covers in detail important Visual Basic and Microsoft Excel commands that add power to your macros. A number of these such as theMsgBox andInputBox() functions have been demonstrated in the past chapters. They will be covered more extensively in this chapter. This chapter also 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.

Because there are so many commands in a number of cases we have given only one or two commands where there are obviously many more commands. This is because if you display the help on one of these commands the help screen will list related commands in the "see also" hot spot. For example the function that returns the sine of an angle is listed but the Tangent and Cosine functions are not since they are listed as related functions in the help on the sine function.

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.

The following are the topics in this chapter:

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

The following are the topics in this chapter:

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

The following are the topics in this chapter:

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

The following are the topics in this chapter:

• 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. And if the charts are embedded charts vs. stand alone chart sheets you must select each embedded chart before you can individually print them out. 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. And this chapter will also provides several chart macro applications.

The following are the topics in this chapter:

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

This chapter shares with you techniques and macros to accomplish the above customization. The following are the topics in this chapter:

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

The following are the topics in this chapter:

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

The following are the topics in this chapter:

• 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. This eliminates the need to put the file in Microsoft Excel's startup directory (typically Excel\Xlstart). And add-ins do not prompt to be saved when Microsoft Excel closes. Also add-ins files can have AUTO_OPEN and AUTO_CLOSE macros in them. Such macros can be used to create and remove menu items and toolbars.

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 which makes it difficult to manually assign an add-in macro to a button or to run. Thus your add-in should add menu items or create toolbars automatically via a Auto_Open macro. Also you can not display any of the sheets in an add-in. Thus you can not show graphs or manually edit a worksheet in an add-in. If you need to frequently modify your macros or add new ones to your collection you should not use an add-in as you can not modify macros in an add-in. You would need to load the file containing the code every time you need to modify the macros and then re-create the add-in file for it to be updated.

Please note that creating an add-in does not prevent a user from seeing your macro code. It just makes it very difficult. Thankfully those experts who have found ways to get at the code in an add-in have not publicized how to do so.

The following are the topics in this chapter:

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

The following are the topics in this chapter:

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