Macros

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
Macros can make Excel much more powerful, allowing you to quickly perform repetitive tasks, process data, and do just about anything else you can think of. To make your common macros easier to use, it helps to add them to a toolbar where they are only a click away.

Assigning Macros to Graphics
Depending on your version of Excel, macros are usually added to toolbars and menus. You can also add them to graphics, so that the macro is executed when someone clicks on the graphic. This tip explains how to establish that relationship between a graphic and a macro.

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
Macros can do all sorts of manipulations of files and directories. One command you can use is the ChDir command, which allows you to change from one disk directory to another.

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
Macros often need to work on various files on your disk drive in order to process data in a worksheet. You can specify the drive that your macro should use as "default" by using the command described in this tip.

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
The Undo stack is cleared automatically whenever you run a macro. This fact of Excel's behavior allows you to easily clear the stack from within the macro itself.

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
If you have a mouse that has a track wheel between the two buttons, you can use that wheel to help navigate around your worksheets. In order to do that, you need to make a small configuration change to Excel, as described in this tip.

Controlling the Printer in a Macro
Want to access the advanced features of your printer from within a macro? You may be out of luck, as those features are often not made accessible to VBA macros. This tip examines why this is the case.

Converting Numbers to Strings
Need to convert a numeric value into a string? It's a snap to do by using the Str function in your macros.

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.

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.

Counting Precedents and Dependents
Do you need to know how many precedents or dependents there are on a worksheet? You could count them manually, or you could let a short macro derive the information for you.

Creating a Directory in a Macro
When you create macro, you might want to use it to create a folder on your hard drive. You can accomplish this with one simple command, as described in this tip.

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
The plus/minus button on a calculator provides a quick way to change a value from a positive to a negative value, and back again. There is no such button in Excel, but you can create one easily by applying the macro in this tip.

Creating a String in a Macro
In VBA, the String function can be used to create a string consisting of almost any number of characters. This tip explains how to use the function in your own macros.

Creating Add-Ins
Add-ins are special Excel programs that enable features or capabilities not in the original program. This tip explains how you can create your own custom add-ins from a regular workbook.

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.

Ctrl+Break Won't Work to Stop a Macro
When you need to stop a macro from running, you normally press Ctrl+Break. What are you to do if the keypress doesn't stop your macro? Here's an explanation of why this may happen and what you can do about it.

Debugging a Macro
Develop your own macros and, sooner or later, you will need to debug them. The VB Editor provides a couple of fundamental debugging tools, introduced in this tip.

Default Worksheet when Opening
When opening a workbook, you may want to make sure that a particular worksheet is always displayed first. The only way to ensure this is through the use of a macro, described here.

Deleting a File in a Macro
Need to delete a file within your macro? If you know the path and name of the file, it is easy to do with a simple command.

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
Macros, in processing data, may need to spread text out among different columns in a worksheet. This tip explains how you can utilize the power in Excel's Text-to-Columns tool from within your own macros.

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
Using a macro to reference the worksheet tab name in a worksheet cell in Excel.

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
If you need to know the character code of a particular character, you can use the Asc function. This tip shows you how.

Determining an Integer Value
You can use the Int function, in a VBA macro, to return the integer portion of a value. This can be very handy in many types of data processing.

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 what hour of the day a macro is running? You can determine the information by using the Hour function, described in this tip.

Determining the Length of a Text File
When creating a macro to process a text file, you may need to know the length of that file. You can figure this out by using the LOF function, as described in this tip.

Develop Macros In Their Own Workbook
If you develop macros and edit them quite a bit, you may be running the risk of causing problems with the macros or with your workbooks. This tip explains a general way that you can avoid the potential problem.

Developing Reciprocal Conversion Formulas
Would you like to develop a worksheet that has two cells that will convert whatever is entered in either of the cells? For instance, you might put a measurement in inches in one cell and have it converted into millimeters in the other, and vice versa. This tip explains how you can (and why you must) do this using macros.

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
You may want to display a specific range of columns in Excel's program window. You can do this with a simple macro that makes use of the Zoom feature built into Excel.

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.

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
Do you need to execute some DOS (command line) commands from within a macro? You can do so by using the Shell command, as described in this tip.

Easily Changing the Default Drive and Directory
Need to change the drive and directory considered the "default" by the operating system? This tip explains how easy it is to do this within a macro.

Editing Macros
If you start using macros to process data in Excel, you'll eventually have to edit those macros. You can also display macros developed by other people (or created with the Macro Recorder) to see how particular actions are performed in the VBA code. This tip shows just how easy it is to display and edit a macro.

Engineering Calculations
In an engineering or scientific environment, it is not unusual to need data "normalized" in some manner. This tip examines a couple of different ways you can normalize your data, including a method that adds units and the appropriate prefixes for those units.

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.

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.

Forcing a Macro to Run When a Worksheet is Recalculated
Normally a macro is only calculated when you specifically tell Excel to calculate it. Some macros need to be calculated whenever your worksheet is recalculated. This won't happen unless you include a very specific command within the body of your macro.

Generating Random Testing Data
Testing is an intrinsic part of developing any software or any worksheet that will be used by others. If you need to create data to use in your testing, the information (and macros) in this tip will be useful.

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.

Getting Big Macros to Run
Troubleshooting an Excel macro when it causes the entire computer to freeze.

Getting Rid of Alphabetic Characters
If you have some data that has a combination of alphabetic and other characters, and you want to get rid of only the alphabetic characters, you could be facing a huge editing job. Of course, you could rely upon a macro to do the editing for you, as described in this tip.

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
Placing values into a cell with a macro is easy when you use the Value property. This tip demonstrates how you can use the Value property in your own macros.

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.

Macro Slows Down on More Powerful Machine
Macros can run differently on different machines. This fact can be painfully obvious if a macro runs slower on a machine that you thought was faster. This tip explains how these sorts of discrepancies can happen, and some tips about what you can do to speed things up.

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
You may want to keep track of when changes were last made to a workbook, particularly if the workbook is accessible to multiple people who may change it. This tip discusses a couple of ways you can keep track of such information.

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.

Pausing Macros for User Input
Your macros may need to get some input from whoever is using those macros. The best way to get that input is through the use of the MsgBox and InputBox functions, described in this tip.

Playing with a Full Deck
Do you have a range of cells that you need to populate with a series of numbers in random order? If so, this is where a macro can come in handy. In fact, the macro in this tip will help you to get the desired results as quickly as possible.

Pulling Apart Cells
The Text to Columns tool is a great boon for analyzing textual data in a worksheet. You can use the tool to strip text into component parts that you can work with easier. This tip explains how to use the tool and introduces a macro you can use when the tool won't work just the way you want it to.

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
One of the core skills in VBA is the ability to use and manipulate variables. This tip explains how you can wipe out the contents of a variable array easily.

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 create a macro, you can add a shortcut key to the macro. Simply use the shortcut, and the macro is executed. If you want to later disassociate the shortcut from the macro, you'll need the information provided 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
If you are having a problem deleting pictures using a macro, it could be due to either bad syntax or different groupings of the images. This tip discusses both causes and what you can do about them.

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
Need to reverse what is in a cell? Use a quick user-defined function, presented in this tip, to accomplish the feat.

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
Got a long-running macro that seems to be tying up your machine? You can safely allow Excel macros to work away in a background window while you continue to work on other tasks in a different widow.

Running Macros on Hidden Worksheets
You can format a worksheet so that it is hidden from someone looking through the worksheets in a workbook. This means, however, that you cannot access information on that worksheet in a macro. This tip explains how to get around the problem.

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 Row Height in a Macro
Using the RowHeight property, your macro can adjust the height of any row in a workbook. This tip explains how to use the property properly.

Shortcut Key for Format Painter
The Format Painter tool is very handy when you want to quickly duplicate formatting form one place to another. The tool virtually requires the use of the mouse, however, as there is no built-in shortcut to use it. This tip describes some of the ways you can use the Format Painter or copy formatting by using the keyboard only.

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
If you have data stored in multiple lines in a single cell, you might want to expand that data so that it actually appears on multiple rows. There is no built-in function to do this in Excel, but you can create one using the macros highlighted in this tip.

Swapping Two Numbers
Need to exchange what is stored in two variables in a macro? It's easy to do if you apply the technique in this tip.

Testing for an Empty Worksheet
If you using a macro to print out a series of worksheets, you may want a way to determine if a particular worksheet contains information before printing it. This tip examines several techniques you can use to determine if a worksheet is empty or not.

Tools on Developer Tab are Unavailable
Want to add some macros to your Excel 2007 workbook? What do you do if you try to add the macros but the program has disabled the tools?

Understanding Functions in Macros
Functions are a common programming construct. They help you to create easy ways of processing information and returning a value. Here's how to create your own functions.

Understanding Phantom Macros
Knowing how Excel sees macros and how to fix phantom macros.

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
The programming language in Excel (VBA) provides some powerful structures that allow you to repeat portions of your program or modify the order in which statements are processed. This tip takes a look at the While...Wend structure, which allows a programming block to be repeated as long as a condition (that you specify) is true.

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
When you open a workbook that contains links to another data source, Excel asks if you want to update those links. This is great if you are opening the workbook manually, but not so great if the workbook is being opened by a macro. This tip discusses ways you can turn off the prompting so the macro will open and process the workbook with the delay.

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 Old Lotus Macros
Using a Lotus 1-2-3 macro in Excel.

Using SUM In a Macro
If you need to use the SUM worksheet function from within a macro, you can easily do it by using the techniques discussed in this tip. You can even use the function with a variable number of cells in a column.

Waiting for Update Completion
If you use Excel to grab data from an external source, you may notice that the macro doing the data grabbing doesn't always wait for the data to be ready. This tip explains why this occurs.

Workbook Events
You can program Excel so that when specific events happen, your macro is executed. Your event handlers can react to almost 30 different events relative to a workbook, as described in this tip.

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 program Excel so that when specific events happen, your macro is executed. Your event handlers can react to a variety of events relative to a worksheet, as described in this tip.

 

More Information

The following are additional topics related to the subject of 'Macros'. A bracketed number after the topic indicates how many articles are related to that subject.