Macros are crucial in Excel for performing what would be extremely time-consuming tasks for you to complete manually. There are many different purposes and techniques for using a macro in Excel, so check out the following articles to learn how to utilize this powerful asset.

Tips, Tricks, and Answers

The following articles are available for the 'Macros' topic. Click the article''s title (shown in bold) to see the associated article.

   Adding a Macro to a Toolbar
A great way to customize Excel is to add your macros to a toolbar. That way you can run them quickly and easily.

   Adding Dashes between Letters
When processing some text data, you may need to perform some esoteric function, such as adding dashes between letters. While this can be done with formulas, it is better to use a macro-based approach.

   Assigning Macros to Graphics
The graphics you place in a worksheet can do more than just look pretty. You can also assign macros to a graphic, which are triggered when the graphic is later clicked.

   Automatically Opening Macro Workbooks when Using a Shortcut Key
Click a button on a toolbar and Excel will go so far as to open a another workbook in order to run a macro associated with the button. Excel is not as accommodating when running a macro associated with a shortcut key. Here's the reason why and what you can do to get around this apparent shortcoming.

   Changing Directories in a Macro
Need to specify which directory on your hard drive should be used by a macro? It's easy to do using the ChDir command.

   Changing Macro Cell References Based on Edits
Place a cell reference in a macro, modify the structure of your worksheet, and you may soon find that the cell reference in the macro is no longer accurate. While Excel doesn't automatically update cell reference in macros according to changes in a worksheet, you can avoid the problem by changing how you reference cells.

   Changing the Default Drive
Do you have a macro that need to read and write files? If so, then there is a good chance you need to specify the default drive on which those files reside. Here's how to do it.

   Checking for the Existence of a File
The data stored in a worksheet can often correspond to information external to that worksheet. For instance, you might collect data that represents filenames in a directory somewhere. If you want Excel to check whether those collected filenames exist, it's easy to do using a simple macro.

   Clearing the Undo Stack in a Macro
Excel keeps track of the actions you take so that you can undo those actions if any are taken in error. You may want to clear that list of actions (called the undo stack); it's easier than you think.

   Comparing Strings
As your macro is processing information, there will doubtless be times that it will need to compare information in strings. Here's a couple of handy ideas on how that can be best done.

   Controlling the Behavior of the Mouse Wheel
The mouse wheel, by default, controls scrolling vertically through your worksheet. If you don't want the wheel to control scrolling, you can turn the capability off.

   Controlling the Printer in a Macro
Need to access the advanced capabilities of a printer from within an Excel macro? You may be out of luck, unless you apply the technique described in this tip.

   Converting Numbers to Strings
When creating macros, it is often necessary to change from one type of data to another. Here's how you can change from a numeric data type to a string data type.

   Converting Strings to Numbers
When working with data in a macro, there are two broad categories you can manipulate: numbers and text. Sometimes you need to convert information from one category (data type) to another. Here is how you convert text to numbers.

   Copying Named Ranges
Named ranges are a great tool to use in developing formula-heavy workbooks. You may want, at some point, to copy your named ranges from one workbook to another. Here's the best ways to do it.

   Copying Pictures with a Macro
Copying information using a macro is rather simple, although there are multiple ways you can do the copying. The most versatile (and the one that will copy pictures) is to use the Copy and Paste methods.

   Copying Worksheet Code Automatically
When creating a workbook to be used by others, you may want any worksheets they add to the workbook to contain some special macro coding. Here are some ideas on how you can make sure that the worksheets contain just what you need.

   Counting Commas in a Selection
If you have a range of cells in which you want to count all the commas, there are several ways you can derive the figure you need. This tip examines different methods to achieve the count, and you can easily adapt the methods to count other characters.

   Creating a Directory in a Macro
One of the things you can do with macros is to work with disk files. As you do so, you may have a need to create a new directory in which your files can be stored. Here's how to do it.

   Creating a Photo Catalog from a Folder of Photos
Excel is great for collecting all sorts of information. You might even use it to create a catalog of your photos. Working with hundreds or thousands of photos can be a challenge, though. Here's how you can work with all those photo files.

   Creating a Plus/Minus Button
Want a quick way to convert positive values to negative and vice versa? You can create your own plus/minus button by using a small macro.

   Creating a String in a Macro
Need to put together a bunch of characters to create a text string? You can do it in your macros by using the String function, explained here.

   Creating Add-Ins
Want to create your own add-in? Excel makes it easy to do. Here are all the steps you need.

   Creating and Naming a Worksheet Using a Macro
You can use macros to make your common Excel tasks easier and faster. For instance, if you routinely need to create new worksheets based on an existing worksheet, you might want to use a macro to perform the task. Here's a couple of suggestions for just such a macro.

   Creating Worksheets with a Macro
Using a macro to add worksheets to your workbook is easy. This tip provides two different methods you can use.

   Debugging a Macro
Part of writing macros is to make sure they work as you expect. This involves a process known as debugging. Here's how you can make sure your macros are free of any errors that may crop up.

   Deleting a File in a Macro
Macros give you a great deal of control over creating, finding, renaming, and deleting files. This tip focuses on this last aspect--how to get rid of an existing file.

   Deleting a Macro
Don't need that old macro any more? Here's how to get rid of it so that it is no longer a part of your workbook.

   Delimited Text-to-Columns in a Macro
The Text-to-Columns tool is an extremely powerful feature that allows you to divide data in a variety of ways. Excel even makes it possible to use the tool in your own macros, as described in this tip.

   Deriving an Absolute Value in a Macro
Need to figure out an absolute value within your macro code? It's easy to do using the Abs function, described in this tip.

   Deriving the Worksheet Name
Excel doesn't provide an easy way to grab the worksheet name for use within a worksheet. Here are some ideas on ways you can get to this information, though.

   Determining a Worksheet's Number
When you add a new worksheet to a workbook, it receives a meaningful name such as "Sheet4" or "Sheet17." If you want to determine a worksheet's number (even after you change the worksheet's name) here's how to do it.

   Determining an ANSI Value in a Macro
Need to know the character code used for a particular character? In a macro you can use the Asc function to determine the code.

   Determining an Integer Value
When creating macros, you often need to process numbers in various ways. VBA allows you to convert a numeric value to an integer by using the Int function, described in this tip.

   Determining How Many Windows are Open
Does your macro need to know how many windows Excel has open? You can determine it by using the Count property of the Windows group.

   Determining if Calculation is Necessary
When processing a worksheet with a macro, it may be helpful to periodically recalculate the worksheet. Wouldn't it be nice if you could only recalculate if Excel tells you that recalculation is necessary? Here's some ideas on how you can figure out when you need to do a recalc.

   Determining Mouse Cursor Coordinates On a Graphic
Add a graphic to a worksheet as part of an Image object, and you can use some very handy event handlers to figure out the coordinates of the mouse pointer. Here's how to make it work.

   Determining the Hour of the Day
Need to know the current hour of the day? You can derive the information in your macros by using the Hour function, as described in this tip.

   Determining the Length of a Text File
When processing plain text files in a macro, it is often helpful to know how much data the file contains. The normal way to accomplish this task is through the use of the LOF function, described in this tip.

   Determining the RGB Value of a Color
Excel allows you to fill a cell's background with just about any color you want. If you need to determine the RGB value of that color, you'll appreciate the techniques covered in this tip.

   Developing Reciprocal Conversion Formulas
When converting between measurement systems, you might want to use two cells for each type of measurement. Make a change in one cell and the result appears in the other, and vice-versa. This is, unfortunately, easier said than done.

   Digital Signatures for Macros
The security features built into Excel 2002 and 2003 allow you to digitally sign your macros so that users can rest assured that they remain unchanged since you saved them. Understanding digital signatures and the certificates on which they are based can be a bit perplexing, but the information in this tip can go a long way toward clearing the air.

   Disabled Macros
Do your macros seem to be disabled on your new machine? It could be because of the security settings in Excel. Here's where they are located and how to change them.

   Displaying a Set Column Range
Do you want to display a particular range of columns within the Excel window? Here's a couple ways you can accomplish the task.

   Displaying Messages When Automatic Data Changes
It is possible to develop macros that update the information in your worksheets automatically. In such instances, you may want to be notified if the information in a particular cell is changed. Here's how to do it.

   Displaying the First Worksheet in a Macro
When creating macros, you often have to know how to display individual worksheets. VBA provides several ways you can display the worksheet you want.

   Dissecting a String
VBA is a versatile programming language. It is especially good at working with string data. Here are the different VBA functions you can use to pull text strings apart in one way or another.

   Documenting Changes in VBA Code
Your company may be regulated by requirements that it document any changes to the macros in an Excel worksheet. Your options in automatically documenting such changes are virtually non-existent, but you are not out of luck. This tip discusses ways you can track what changes are made in your code.

   DOS From Macros
Need to run a DOS command from within one of your macros? The answer is the Shell command, described in this tip.

   Easily Changing the Default Drive and Directory
Need a quick way to change the default drive and directory in a macro you are writing? Here's the commands to do it and a shortcut you can use.

   Editing Macros
Even if you do nothing but record macros, sooner or later you will have a need to edit what you record. Here's how to get to the place where you can make the macro changes directly.

   Engineering Calculations
Need to normalize your data in some way so that all your values are in a given format? This tip presents a number of techniques you can put to work with your data.

   Filling a Range of Cells with Values
When writing a macro, you may want to fill a range of cells with different values. The easiest way to do this is to use arrays to fill the cells, as described in this tip.

   Finding Columns of a Certain Width
If you need to find out how many columns are set to be a specific width, you'll need a macro to help determine the info. Here's a couple that will help.

   Finding Other Instances of Excel in a Macro
When processing information using a macro, you may need to know if there are any other instances of Excel running on a system. This can be done a couple of different ways, as discussed in this tip.

   Finding the Path to the Desktop
Figuring out where Windows places certain items (such as the user's desktop) can be a bit frustrating. Fortunately, there are specific functions built into Windows that can help you determine the location you need.

   Finding Workbooks Containing Macros
Workbooks can contain macros, or not. It is entirely up to you whether they do or not, but at some future time you might want to find out which of your workbooks do contain macros. The way to find such workbooks can be as simple as searching for them.

   Fixing Macro Button Behavior in Protected Worksheets
When working with macro buttons, you may run into some bizarre behavior related to the macros without really understanding why. Here's one explanation of what is going on and how you can fix it.

   Generating Random Testing Data
Need to test your formulas? Then you need some testing data that you can use to see if the formulas function as you expect. Here's some different ways you can generate some random data for testing.

   Generating Unique Numbers for Worksheets
You may need to automatically generate unique numbers when you create new worksheets in a workbook. Here's a couple of easy ways to do it.

   Generating Unique, Sequential Names
Do you need to create a number of words or phrases where you only alter a few letters in each one? If the alterations follow a specific pattern, you could quickly do the creation within Excel. Here's an example.

   Getting Big Macros to Run
Troubleshooting an Excel macro when it causes the entire computer to freeze can be a tedious affair. This tip provides some ideas on things you can check when going through the process.

   Getting Rid of Alphabetic Characters
When you need to get rid of characters in the middle of a cell value, the best way to do it is through the use of macros. This tip examines how you can get rid of specific types of characters, just as you need.

   Getting Rid of the "Enable Macros" Notice
Do you get tired of the dialog box that says "do you want to enable macros" that is displayed when you open a workbook. You can get rid of it by getting rid of the modules that used to hold your long-gone macros.

   Getting User Input in a Dialog Box
Want to get some input from the users of your workbooks? You can do it by using the InputBox function in a macro.

   Inserting Worksheet Values with a Macro
Macros are often used to process information in a worksheet. You may need your macro to change the values stored in cells; here's how to do it.

   Item Not Available in Library
When sharing workbooks with others, you may find that the macros in those workbooks may not work as you expect. This tip focuses on one particularly common problem that you'll need to address.

   Macro Runs Slowly, but Steps Quickly
When you have a macro that processes a huge amount of data, it can seem like it takes forever to finish up. These slowdowns can be aggravating, but part of developing macros is figuring out where those bottlenecks occur. This tip provides a few ideas you can use.

   Macros in Template Files
People often place macros in template files to perform any number of tasks. This tip describes a situation where the link between a toolbar button and a macro was broken when a new document was created from a template. The problem was corrected by double-checking where the toolbar button pointed when referencing the macro.

   Making Common Functions Available to Others
When you use macros to create functions, you might want to share those functions with others, particularly if they perform tasks that are essential to your business. There are a couple of ways you can share them, as this tip describes.

   Mouse Click Event in VBA
Need to know if a particular cell is clicked with the mouse? Excel has no particular event handler for clicking in this way, but you could use one of the other event handlers provided by the program.

   Moving Macros from the Personal Workbook
Need to move a macro out of your Personal.xls workbook and into a regular workbook? You can do it using familiar editing techniques, as illustrated in this tip.

   Noting When a Workbook was Changed
Do you need to know when a workbook was last changed? There are a couple of ways you can go about keeping track of the change date, as discussed in this tip.

   Out of Memory Errors when Accessing the VBA Editor
It can be frustrating when you get error messages doing something that you previously did with no errors. If you get an out of memory error when pulling up the VBA editor, this tip may help you track down the cause.

   Pasting without Updating References
Do you need to paste formulas without updating the references in whatever you are pasting? You can accomplish this, depending on what you are pasting and where you are pasting it.

   Pulling Apart Cells
Separating text values in one cell into a group of other cells is a common need when dealing with text. Excel provides a handy tool that allows you to easily pull apart the cell values. Here's how to use it.

   Putting an X in a Clicked Cell
Need to click on a cell and have it replaced with an "X"? Macros make it easy to do, as illustrated in this tip.

   Quickly Dumping Array Contents
Variable arrays are used quite often in macros. If you use an array once in your macro and then need to reuse it for something else, it makes sense to clear it out. Here is an easy way to dump the contents of an array.

   Relative References when Recording Macros
When you record a macro, make sure that you know how Excel is recording your cell movements. This tip explains the problem and why you need to be concerned.

   Removing a Macro from a Shortcut Key
When you assign a macro to a shortcut key, you make it easy to run the macro without ever removing your hands from the keyboard. If you later want to disassociate the macro and the shortcut key, you'll need the information in this tip.

   Removing All Macros
Macros are stored as part of a workbook so that they are always available when you have the workbook open. If you want to get rid of the macros in a workbook, there are a couple of ways you can do it. This tip examines two quick ways to get rid of the macros.

   Removing Pictures for a Worksheet in VBA
Excel allows you to add pictures to your worksheet, even within a macro. However, you might have a bit harder time figuring out how to get rid of the pictures. Here are some ideas.

   Renaming a Macro
Got a macro that doesn't have quite the right name? You can rename the macro by following these simple steps.

   Replacing Some Formulas with the Formula Results
Macros are often used to process the data stored in a worksheet. Some of these processing needs can be pretty specific to both the data you are using and the audience for that data. This tip presents a macro that can be useful when you need to distribute your worksheets to other people who may not have access to the information external to the worksheet.

   Reversing Cell Contents
Macros are great at working with text. This tip presents an example that shows this versatility by reversing the contents of the text in a given cell.

   Reversing Names In Place
Do you want a way to reverse names within a cell, making them "last, first" instead of "first last?" Here's a handy macro that can take care of the switch for you.

   Running a Macro When a Worksheet is Deactivated
You can easily configure Excel so that it runs a specific macro whenever a worksheet is deactivated. Just follow the easy steps in this tip.

   Running Macros in the Background
Want to run a macro in Excel, but not sure if doing so will tie up your computer? Here's how macro processing really happens.

   Running Macros on Hidden Worksheets
Excel allows you to hide worksheets so that they aren't visible to those using your workbook. Hiding worksheets has a drawback, however, if you are using macros to process your worksheets. This tip discusses the drawback and provides you with a way around the drawback.

   Saving a Workbook in a Macro
Does your macro need to make sure that the workbook being processed is saved to disk? You can add the saving capability by using a single code line.

   Saving Changes when Closing
If your macro closes workbooks, you'll want to make sure that it will save any changes you made to the workbook. Here's how to do it.

   Selecting a Cell in the Current Row
Macros often need to select different cells in a worksheet. Here's how you can use macro commands to change which cell is selected in the current row.

   Selecting a Specific Cell in a Macro
Need to use a macro to select a specific cell in a different workbook? It's not as straightforward of a proposition as you might think. Here's how to get right to the cell you want.

   Selecting Columns in VBA when Cells are Merged
If you have a macro that selects different columns in a worksheet while processing information, you may get some undesired results if there are merged cells in the column. This tip examines different ways you can reference the cells in a workbook so that you don't run into problems.

   Setting Program Window Size in a Macro
The macro programming language used in Excel gives you a great many tools that allow you to modify the way that Excel appears on the screen. You can, for instance, easily adjust the size and position of the Excel program window. Here's how to do it.

   Setting Row Height in a Macro
Macros can be used to change the formatting of your worksheet, if desired. One change you might want to make is to the height of a row. Here's how you can use the RowHeight property to specify how high you want a row to be.

   Shortcut Key for Format Painter
The Format Painter is great for copying formatting from one cell to another. If you don't want to grab the mouse to use the Format Painter, you'll like the shortcuts described in this tip.

   Showing RGB Colors in a Cell
Excel allows you to specify the RGB (red, green, and blue) value for any color used in a cell. Here's a quick way to see the effects of different RGB values.

   Specifying a Paper Tray in a Macro
If you are using a macro to create your printed Excel output, you may need a way to specify that paper should come from a specific printer tray. That is not as easy as it may first seem. Here's a discussion on the options you have.

   Splitting Information into Rows
Got too much information in a single cell? Here's how you can use a macro to pull apart that information and put it into cells in the way you want.

   Stepping Through a Non-Contiguous Range of Cells
Using macros to step through each cell in a selection is a common occurrence. What if that selected range is made up of non-contiguous cells, however? Here's how to work with those types of selections, as well.

   Storing a User's Location before Running a Macro
Macros are often used to process information in a workbook. If your macro makes changes in what is selected in the workbook, then it is "good form" to restore whatever the user had selected before your macro was executed. Here's how to do it.

   Swapping Two Numbers
When programming macros, variables are used extensively. At some point you might want to exchange the values held by two macros. Here's the technique to accomplish the task.

   Testing for an Empty Worksheet
If you are using a macro to process a number of worksheets, you may have a need to know if the worksheet is empty or not. After all, it makes no sense to process an empty worksheet; you can move to the next one. Here are some techniques to determine whether there is data on a worksheet.

   Transferring Data between Worksheets Using a Macro
Macros can be used for all sorts of data processing needs. One need that is fairly common is the need to move data from one worksheet to another, under macro control. Fortunately, making the move is as easy as identifying the source and target ranges.

   Understanding Phantom Macros
When you delete all the macros in a workbook, Excel may still think you have some there. Here's why that happens and what you can do about it.

   Understanding the If ... End If Structure
One of the most basic of programming structures is the conditional structure: If ... End If. This tip explains how this structure works within a macro.

   Understanding the Select Case Structure
One of the powerful programming structures available in VBA is the Select Case structure. This tip explains how you can put this structure to work in your macros.

   Understanding the While...Wend Structure
Logical structures are important in programming, as they allow you to control how the programming statements are executed. Here's one handy structure that you can use to repeat steps.

   Unhiding or Listing All Objects
An Excel workbook can contain quite a few different objects. Sometimes those objects can be hidden so that they are not easily discovered. Here are some ideas on how you can uncover and list all those objects.

   Updating Automatically When Opening Under Macro Control
If your workbook contains links, you are normally given the opportunity to update those links when you open the workbook. When opening the workbook from within a macro, you probably don't want Excel to ask about updating the links. Here's how to stop Excel's normally inquisitive nature.

   Using Message Boxes
When creating a macro, one of the ways you can communicate with users is through the use of a message box. This tip explains what message boxes are and how to implement them in your own macros.

   Using SUM In a Macro
Want to use a worksheet function (such as SUM) from within a macro? Here's how easy it is to accomplish the task.

   Workbook Events
You can create macros that run whenever Excel detects a certain event happening within an entire workbook. This tip explains how this occurs and explains how to create the macros associated with those events.

   Working while a Macro is Running
If you have a macro that takes a long time to process a workbook, you might want to continue working in Excel while the macro is busy. Here's a way you can accomplish your desire.

   Worksheet Events
You can create macros that are automatically executed whenever certain events occur within a worksheet. This tip details what those events are and explains how to create the macros that are associated with each event.