Topic: Creating Macros

The following are additional topics related to the category listed above. A bracketed number after the topic indicates how many articles are related to that subject.

Tips, Tricks, and Answers

The following articles are available. 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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Copying Worksheets in a Macro Do you need to write a macro that copies worksheets from one workbook to another, or even within the same workbook? If so, you need to become familiar with the Copy method, described in this tip. There are wide number of ways you can use the method to perform a copy just as you want. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Deleting Macros from within a Macro Macros are most often used to change data within a worksheet. However, macros can also be used to make changes to macros—including deleting entire procedures. This tip explains how you can make such changes. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Recording a Macro How to easily record an Excel macro. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Telling which Worksheets are Selected Macros are often used to process data in a workbook. If the macro needs to perform some task relative to various worksheets in the workbook, it is important to be able to figure out which sheets are selected. This tip provides an example of how you can tell the names of the selected worksheets. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Understanding the For ... Next Structure An explanation of For … Next structure of a VBA macro in Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using Macros in Protected Workbooks When you protect or share worksheets and workbooks, it can affect how you use macros. This tip explains the different ways in which your macros can be affected. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Writing a Macro from Scratch How to write a macro for use in an Excel workbook. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

More Topics

Tips, Tricks, and Answers

An Amazing Resource!

Helpful Links

Great Info!

RSS Feeds