Creating Macros

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
Creating a macro and assigning it to a combination of keystrokes on the keyboard.

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
The Visual Basic Editor, where you write macros, allows you to enter very long macro lines, if desired. While it is possible to do that, it may not be the best for reading the macro later. It is much better to split the macro line so that it spans multiple lines. This tip explains how.

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.

Creating a Splash Screen
For those who are creating their own applications in Excel, user forms are a common way of presenting information to users. You can even create a basic splash screen through a user form, but if you want to get rid of some of the normal trappings of a dialog box (like the close button), you will need to get creative.

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
You can use worksheet functions from within a macro, but you may not always get the results you expect. This tip explains how using the ATAN2 worksheet function generates an error in a macro and what you can do to correct the problem.

Excel 2007 VBA Differences
Macros are a great way to extend what you can do with Excel. However, Microsoft often introduces subtle (and sometimes not so subtle) differences in how the macro language is implemented from one version of Excel to another. If you need to know how VBA differs in Excel 2007 when compared to previous versions, you'll appreciate the information in this tip.

Hiding Entries in an InputBox
When entering a password into an InputBox, you may want to "mask" what is entered. The InputBox function doesn't provide this capability, but there are ways you can get around this shortcoming.

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
Macros often need to include a way to get information from the user. If you need to offer options to the user and then find out which of the options they choose, you can follow the example presented in this tip.

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
How to easily record an Excel macro.

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.

Self-Deleting Macros
Macros eventually lose their purpose and are no longer needed. There can be any number of reasons for them no longer being needed, such as a particular date being passed. If you want to delete macros after a particular date is passed, then the information in this tip will be of particular interest to you.

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.

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 a Macro for Drop-Down List Changes
Excel 97 has a bug that causes the Worksheet_Calculate event not to trigger when someone picks a value from a data validation drop-down list. This tip discusses the problem, along with some ways you can compensate for it.

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
As you can tell from reading various ExcelTips, macros are used for a wide variety of purposes that expand what you can do with Excel. But exactly what are macros and why should you be concerned with them? This tip provides a high-level introduction to what they are and why you really should care.

Understanding the For ... Next Structure
An explanation of For … Next structure of a VBA macro in Excel.

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 feature of Excel, allowing you to refer to individual cells or ranges of cells with a plain-English name. You can also refer to named ranges in your macros, using either of two methods described in this tip.

Writing a Macro from Scratch
How to write a macro for use in an Excel workbook.

 

More Information

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