Tips, Tricks, and Answers

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

   Assigning a Macro to a Keyboard Combination
Macros are a great way to expand what you can do with the data you place in Excel. You can make those macros even handier by assigning them to a shortcut key, as described in this tip.

   Automatically Changing References to VBA Libraries
VBA libraries are used to provide some functions to your macros. These libraries are often tied to the version of Excel you are using, and can thus make it difficult to run the same macro on a different version of Excel. This tip provides a few ideas on how you can get around problems relatied to which VBA library reference is used in a workbook.

   Continuing Macro Lines
Sometimes a macro command line can get very, very long. This can make it hard to understand when you look at it a month or so from now. It is better to break your command lines up into individual lines that are easier to understand and document.

   Copying Worksheets in a Macro
Copying worksheets (one or many) is easy to do manually. What is not well known is that it is even easy to make the copies in a macro, as described in this tip.

   Deleting Macros from within a Macro
Macros are really helpful, but you may want to actually delete a macro from within another macro. This is not as easy as it sounds, as this tip points out.

   Error Using ATAN2 Function in Macro
Excel allows you to use worksheet functions from within macros. This is helpful, especially when you are trying to perform mathematical functions. If your usage generates an error, however, you may be unclear as to why this occurs.

   Exiting a For ... Next Loop Early
If you use For ... Next loops in your macros, make sure you give a way to jump out of the loop early. That way you can limit the amount of time spent by the macro in the loop and speed up performance.

   Hiding Entries in an InputBox
Requiring users to input a password in Excel increases the security of the worksheet and can prevent someone from running a macro without authorization. This tip explains how to accomplish this task.

   Macro Fails after AutoFilter
When developing a macro that others may use, you might want to test it out to make sure it works properly if an AutoFilter is in effect. If it doesn't, you can turn off the AutoFilter with a simple single-line command.

   Maximum Length Limit for a Macro
Make your macros too long, and Excel may just refuse to run them at all. This tip explains what the limit is for macros, and provides some guidance on how you can get around the limit.

   Offering Options in a Macro
It is often helpful to get user input within a macro. Here's a quick way to present some options and get the user's response.

   Official Color Names in VBA
When creating macros, you can use the ColorIndex and Color properties to set or determine the colors in a cell. Excel provides eight constants that can be used to specify colors, but that is not the limit on how you can work with colors. This tip provides some general guidance on colors and color names in VBA macros.

   Opening a Workbook but Disabling Macros
Macros that run automatically when you open or close a workbook are quite helpful. You may not want them to run, however, when you open or close a workbook from within another workbook's macro. This tip explains how you can stop a particular macro (AutoClose) from running when accessing a workbook in this manner.

   Recording a Macro
One of the most common ways of creating macros is to use Excel's macro recorder. This tip shows how easy it is to use the macro recorder to record the actions you take within your workbook.

   Retrieving Drive Statistics
Need to gather some information about the drives on a system? It can be pretty easy to do using a macro, as shown in this tip.

   Selecting a Range of Cells Relative to the Current Cell
When processing information in a macro, you often need to select different cells relative to the currently selected cells. It's easy to do once you know how to use offsets in your macro statements.

   Selecting Visible Cells in a Macro
Many times you need to select just the visible cells before taking some action. It is helpful to know how to make this specialized selection within a macro.

   Self-Deleting Macros
Macros are very powerful, but you may not want them to always be available to a user. Here are some ways you can limit their usage based on what date it is.

   Specifying Location for a Message Box
When writing macros, you may want to position a message box at a specific location on the screen. This can't be done in Excel, but there are ways around the problem.

   Stepping Through a Macro with a Worksheet Visible
When developing a macro, it is often necessary to step through the various code lines so you can see what is happening on the worksheet. This is easy to do; just display the necessary windows on the screen at the same time.

   Switching Windows in a Macro
When you have multiple workbooks open at the same time, Excel allows you to easily switch between those workbooks. How you do the switching within a macro is a bit more complex, however.

   Telling which Worksheets are Selected
If your macro processes information on a number of worksheets, chances are good that you need your macro to figure out which worksheets are actually selected. This tip explains how you can determine that info in your macro.

   Triggering an Event when a Worksheet is Deactivated
One way you can use macros in a workbook is to have them automatically triggered when certain events take place. Here's how to trigger a macro that runs when a worksheet is deactivated (when it loses focus).

   Trouble Recording Paste Special Formula
Sometimes, when you upgrade to a new version of Excel, you could run into a problem recording macros that you had no problem with in the earlier version. There could be any number of reasons for the problem, but sometimes it is just easier to bypass recording and create the macro from scratch.

   Understanding Macros
What is a macro? Ever wonder what these are and how to use them? This tip answers the basics of what a macro is used for, different ways you can create them, and where they can be stored.

   Understanding the For ... Next Structure
Part of the power of VBA is being able to control when some of your code executes and when it doesn't. A primary way to do this is with the For...Next structure.

   Using Macros in Protected Workbooks
Having problems with using macros in a protected workbook? There could be any number of causes (and solutions) as described in this tip.

   Using Named Ranges in a Macro
Named ranges are a great capability provided by Excel. You can define all sorts of named ranges in a workbook, but how do you access them when creating macros? This tip explains the different ways you can access those ranges.

   Writing a Macro from Scratch
Recording macros is a great approach to getting started with macros, but at some point you'll need to create one from scratch. Here's how to do it.