Macros (Microsoft Excel)

Aborting a Macro and Retaining Control
If you need to exit a macro before it is finished running, you can do it using a brute force method, or you can build in a way to gracefully exit. This tip discusses the latter way to get out of a macro and still retain a modicum of control.

Adding a Calendar to a Worksheet
Using a specialized calendar control is a great way to let users add dates to a worksheet. Unfortuantely, Microsoft hasn't made it very simple to use such a control, as detailed in this tip.

Adding a Macro to the Quick Access Toolbar
The Quick Access Toolbar is a great place to put anything for which you want quick access. (Duh!) Here's how you can add your macros to the QAT so they can be run lickety-split.

Adding and Formatting a Shape via Macro
Excel makes it rather easy to add shapes to your worksheets. If you add a lot of shapes, you quickly discover that it can be a pain to do all the formatting to those shapes over and over again. This tip provides a way to insert a standard shape (an oval) and have it be formatted automatically.

Adding Buttons to Your Worksheet
You can easily add a button to your worksheet that will allow you to run various macros. This tip shows how easy it is.

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.

Adding Differently Formatted Text to a Cell
Want to add or replace some text in a column with text that is formatted differently? The ideas presented in this tip can go a long way toward accomplishing your task.

Adding Leading Zeroes to ZIP Codes
Import a bunch of ZIP Codes into Excel, and you may be surprised that any leading zeroes disappear. Here's a handy little macro that can add them back.

Adjusting a Path Based on System and User
It is not uncommon to set variables in a macro based on other values, such as time or date. You could also set variables (such as a path on a drive) based on information such as a computer's name or the name of a user logged into a system. Here's how to determine those names (computer and user) so you can test the values in your macro.

Adjusting Values with Formulas
Paste Special is a great tool that allows you to modify the values in a range of cells in your worksheets. You may want, however, to instead change the values in cells into formulas that show how your modification was done. This tip examines how you can do this.

Appending to a Non-Excel Text File
Does your macro need to add information to the end of a text file? This is called appending and is done using the technique presented in this tip.

Automatically Enabling Macros for Specific Workbooks
On your system you may have workbooks that contain macros you know are safe to use. Microsoft provides two things you can do with such workbooks that will cause those workbooks to load without impediment. This tip looks at both trusted locations and provides a link to additional information about digital signatures.

Automating Copying Macros
You can manually copy macros from one workbook to another, but what if you want to automate the copying process? Here's some ideas you can try out.

Automating the Importing of Macros
Macros are great when it comes to automating how you work with your workbooks. What if you want to fundamentally change the nature of a workbook from within your macro, however? This tip examines how you can save a workbook as an XLSM file and then import macros into it.

Bypassing the BeforeClose Event
Hold down the Shift key as you open a workbook, and Excel bypasses any "startup macros" that may be in the workbook. If you want to bypass automatic macros triggered when closing the workbook, then you need to get a little more creative.

Calculating an ISBN Check Digit
ISBNs are used to uniquely identify books. You may need to know if an ISBN is valid or not, which involves calculating the check digit used in an ISBN. This tip presents a user-defined function that can be used to figure out the validity of a check digit and, therefore, the ISBN itself.

Calculating Only the Active Workbook
When you enter information into a workbook, Excel automatically recalculates every worksheet in every open workbook on your system. If you want to limit what is recalculated, you'll need some of the techniques described in this tip.

Calculating the Distance between the Top of the Window and Row 1
Normally Excel positions a UserForm in the center of your screen. You may want to position the form elsewhere, more specific to your purpose in using the UserForm. This tip provides some guidance on how you might be able to achieve this.

Calculating Time Differences between Two Machines
Want to know how much of a time difference there is between your machine and a different machine? This tip provides some suggestions on how you can find an answer.

Calling a Macro from the Workbook_Open Event
You can run a Personal.xlsb macro from within your Workbook_Open code, but you may get an error if you don't make sure everything is perfect. This tip looks at the one thing you need to include to make sure it works as expected.

Calling a Subroutine from a UDF
Excel allows you to create a special type of macro called a user-defined function (UDF). These can let you add to the plethora of built-in functions provided in Excel. There are some things, however, that you cannot do from within a UDF. This tip examines why this is.

Can't Set Custom Format in VBA
You can, in a macro, specify a custom format for a range of cells. If the custom format doesn't seem to "stick" (so to speak), then something else in your workbook could be causing the issue.

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 needs 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.

Checking if a Workbook is Already Open
Knowing if a workbook is already open can be a prerequisite to your macro working correctly. Here's how to check it out.

Checking the Scope of a Defined Name
When creating a macro to work with the names of defined ranges, you may need to know whether the scope of the name is for the entire workbook or for a specific worksheet. This tip shows you how easy it is to figure out that scope just from the name of the range.

Clean Up Your Macro List
Got a workbook cluttered with all sorts of macros? Delete them and you'll make your workbook easier to manage.

Clearing the Clipboard in a Macro
You may want your macro to clear the clipboard so that people cannot access anything left in the clipboard. That is simpler said than done, however, as there are actually four clipboards available to an Excel user on modern systems. This tip provides ideas for clearing all of them.

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.

Conditionally Displaying a Message Box
You can, from within your macros, easily display a message box containing a message of your choice. If you want to display that message box only when specific criteria are met, the challenge becomes a bit trickier.

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.

Controlling Window Size when Opening Additional Workbooks
When you open multiple workbooks, the way in which Excel sizes them is not the best for your needs. This tip looks at a way that you can specify how you want an opened workbook to be sized and positioned on the screen.

Converting HSL to RGB
When working with colors in Excel you can specify them using either RGB or HSL values. Converting from HSL to RGB can be a challenge, but the information in this tip should help.

Converting Numbers Into Words
Write out a check and you need to include the digits for the amount of the check and the value of the check written out in words. Excel doesn't include a built-in way to convert those digits into words, but the macro in this tip can do the job for you.

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 Phone Numbers
Sometimes you receive a phone number that contains alphabetic characters and you need to convert it to a purely numeric phone number. You can easily do that using the macro in this tip.

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.

Converting Text to Numbers
Import information from a program external to Excel, and your numbers may be treated as text because of the way that the external program formats them. Here's a quick way to take that text apart so it can be treated like the number it really is.

Copying a Set Range from Multiple Worksheets to a New Worksheet
Want to create a summary worksheet that pulls a single row of data from each worksheet in the workbook? Here are a couple of ways to accomplish the task, one without a macro and one with.

Copying 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 copy data from one worksheet to another, under macro control. Fortunately, making the copy is as easy as identifying the source and target ranges.

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's some ideas on how you can make sure that the worksheets contain just what you need.

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.

Counting All Characters
Need to know how many characters there are in a workbook? You can find out easily with the handy macro introduced in this tip.

Counting Atoms in a Chemical Formula
Chemical formulas use a notation that shows the elements and the number of atoms of that element that comprise each molecule. Can Excel be used to count the number of atoms represented in a chemical formula? This tip looks at the considerations of such a task and how you can do it.

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 Empty Colored Cells
There are a variety of ways that you might want to count the cells in your worksheet. One way is to figure out how many of your cells are filled with a color but are empty. Here are several ways you can accomplish the task.

Counting the Times a Worksheet is Used
Do you need to know how many times a worksheet has been used? Excel doesn't track that information, but you can develop some ways to track it on your own.

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 Floating Macro Button
Macros can make your use of Excel much more powerful. If you have a macro that is triggered by an on-screen button, you may want that button to always be located near the cell on which you are working. This tip explains how to realize this desire.

Creating a Function Inventory for a Workbook
Your worksheets are very often made up of formulas and these formulas are made up of functions. If you ever want to create a list of all the functions used in a workbook, you'll find the macro in this tip to be helpful.

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 an Animated Count Up
You might want to display a value in a cell as an upward-counting value. This might seem difficult but can be done rather easily with a macro. This tip looks at a couple of approaches you can use.

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 are a couple of suggestions for just such a macro.

Creating Dependent Cells
Making the values in two cells mirror each other may seem like a desirable thing to do. It can be done, as discussed in this tip, but it may not be the best way to work with your data.

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.

Cropping Graphics in a Macro
Excel allows you to easily paste graphics into a worksheet. Once added, you may want to quickly process the graphics by cropping them. Here's how to do it with a macro, along with a caution.

Ctrl+Break Won't Work to Stop a Macro
When you need to stop a macro while it is 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
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.

Declaring Variables
Macros depend on the use of variables to do their work. This tip examines how variables are declared in a macro, using the Dim keyword.

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 as described here.

Delaying in a Macro
At times, you may want your macro to pause execution. This tip presents four different ways you can add a delay into your macro.

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 Blank Rows
Got some pesky blank rows in your data that you want to get rid of? This tip provides a wide variety of methods you can use to get rid of them.

Deleting Every X Rows
Grab some info from a source other than Excel, and you may find the need to delete a certain pattern of rows from a worksheet. The macro presented in this tip allows you to delete every other row, every third row, etc.

Deleting Old Data from a Worksheet
If you keep on-going data in a worksheet, some of your data�"over time�"may need to be deleted. If you have an "expiration date" attached to your information, you can use a couple of different methods to get rid of the information that has expired.

Deleting VBA Code in a Copied Worksheet
VBA makes it easy to copy a worksheet from the current workbook into a brand-new workbook. You may want to delete some macro code from the copied worksheet, though. If so, then you'll appreciate the ideas in this tip.

Deleting Worksheet Code in a Macro
When creating an application in VBA for others to use, you might want a way for your VBA code to modify or delete other pieces of your VBA code. In theory this can be easy to do; this tip explains how.

Deleting Zero Values from a Data Table
Want to get rid of all the zero values in a range of cells? This tip provides a couple of different ways you can accomplish the task.

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.

Determining a Format's Currency Symbol
Excel allows you to format numeric values in many different ways, including as currency. If you want to determine, in a formula, how the currency value is formatted, you'll appreciate the user-defined function included in this tip.

Determining a Random Value
Random values are often needed when working with certain types of data. When you need to generate a random value in a macro, the Rnd function is the way to go.

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 Differences Between Dates
Macros are often used to process the data in a worksheet. If that data includes dates that need to be processed, you'll appreciate the ideas in this tip about how to determine the differences (time span) between two dates.

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 a Number is Odd or Even
If you need to know whether a particular value is odd or even, you can use this simple formula. Designed to be used in a macro, it returns either True or False depending on whether the value being examined is even or not.

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 the Current Directory
When you use a macro to do file operations, it works (by default) within the current directory. If you want to know which directory is the current one, you can use the CurDir function.

Determining the Day of the Month
Want to figure out the day of the month represented by a particular date? You can use the Day function in VBA to get the value you need.

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 String
Macros are great for working with strings, and one of the most commonly used string functions is Len. This tip explains how to use this function to determine how many characters a string contains.

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 Number of Visible Columns
When using a macro to process information in a worksheet, you may want that macro to figure out how many columns are visible on the screen at a given moment. This is relatively easy to accomplish, using the techniques 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.

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.

Digital Signatures for Macros
The security features built into Excel 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.

Disabling All Function Keys Except One
Disabling function keys is rather easy to do when you rely on the OnKey method in a macro. This tip looks at how you can develop the macro to disable all function keys except one of them.

Disabling Shift Key Use when Opening a Workbook
Open up a workbook, and Excel normally runs the macros associated with that workbook. You can disable the automatic running of macros by holding down the Shift key while the workbook opens. If you want to defeat this ability, you may be out of luck.

Displaying Excel's Developer Tab
The Developer tab of the ribbon is the gateway to many advanced features in Excel, including those features related to working with macros. The Developer tab is not visible by default in Excel, but here's how to make sure it remains displayed on your system.

Displaying the "Last Modified" Date
Want to know when a workbook was last modified? Want to put that date within the header of your worksheet? Here's how to access and use the information you need.

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.

Displaying the Print Dialog Box in a Macro
Want to print a document by using a macro? One way is to display the Print dialog box and allow the user to interact with it. This tip demonstrates how easy it is to actually pull up the desired dialog box.

Displaying the Selected Cell's Address
Need to know the address of the cell that is currently selected? The function and macro highlighted in this tip will come in handy for you.

Displaying Worksheets in a Slideshow Fashion
Want to step through the worksheets in a workbook, displaying them like a slideshow? The macros provided in this tip can make quick work of this task.

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.

E-mailing PDF Reports Results in Consistent Crash
It is possible to create macros that send out reports, via e-mail, from within Excel. Frank did this and ran into problems running the macro. This tip examines the crashing issue experienced by Frank and offers some potential solutions.

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.

Enforcing a Desired Font
If your workbooks are shared and used by a number of different people, you may end up with some formatting in those workbooks that you don't want. This tip looks at a simple way to make sure that a particular font is always used in a workbook.

Enforcing Moving Cells Up
When you design your worksheets, you probably want users to interact with those worksheets in specific ways. What happens, though, if users don't do so? This tip looks at ways you can enforce how users delete data in a worksheet.

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.

Error Creating Event Handlers
If you are getting an error when you try to create an event handler, it could be related to a long-known bug in Excel. This tip looks at this possibility and also what might cause the issue on a Windows system.

Excel Crashes when Running Macros
It can be frustrating when macros don't run as you expect. When it occurs, however, tracking down the cause can be even more frustrating. This tip provides a few ideas that may be helpful in doing the necessary detective work.

Executing a Macro Every 15 Minutes
Need to run a macro at a given interval? It's easy to do when you learn how to use the .OnTime method, described in this tip.

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.

Expiration Date for Excel Programs
If you use Excel to create a macro-based application, you may want to make sure that your programs cease working after a certain time. The easiest technique for "expiring" a program is described in this tip.

Extracting Proper Words
If you've got a list of potential words, and you want to know which of those potential words are real, you'll appreciate the techniques described in this tip. You can either manually perform the analysis, or use the handy macro provided.

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 Positions of Formatted Characters in a Cell
With a little bit of work, Excel allows you to format individual characters of the text you place in a cell. If you want to later find which characters are formatted as, say, bold or italic, you'll need to use one of the macros provided in this tip.

Finding the Last-Used Cell in a Macro
Ever wonder what the macro-oriented equivalent of pressing Ctrl+End is? Here's the code and some caveats on using it.

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.

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.

Forcing Manual Calculation For a Workbook
If you have a large, complex workbook, you may want to make sure that it is always calculated manually instead of automatically. You can do that by including just a couple of small macros in the workbook.

Friendly and Informative Error Handling
When creating macros, it is helpful to know what is going on within the macro itself in case an error crops up. Here's one way that you can build helpful error handling into your macros.

Generating a Keyword Occurrence List
Need to pull a list of words from a range of cells? This tip shows how easy you can perform the task using a macro.

Generating a List of Macros
Got a workbook that has lots and lots of macros associated with it? Here's a way you can get a list of all of those macros into a worksheet.

Generating Unique Numbers for Worksheets
You may need to automatically generate unique numbers when you create new worksheets in a workbook. Here are 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 a File Name
Does your macro need to allow the user to specify a particular file name that should be used by the macro? Here's a quick technique for getting this information, using Word's standard Open dialog box.

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.

Hiding Excel in VBA
Want to have you macro completely hide the Excel interface? You can do so by using the Visible property for the Excel Application object.

Hiding Macros
Need to hide some macros in your workbook? There are three ways you can do it, as covered in this discussion.

Highlighting Greater Than Average Dry Durations
If you need to find whether the duration between two dates is greater than the average of all durations, you'll find the ideas in this tip helpful. Not only can you use conditional formatting, but you can also use a macro to do the formatting.

Highlighting Pattern Violations
A common part of working with text strings in a worksheet is normalizing those strings so that they follow whatever rules you've established. This tip looks at ways you can highlight pattern violations in textual data.

Importing Based on a Partial File Name
A common task for macros is to open and process a file you want imported into your workbook. If you need to identify the import file based on a partial filename, there are a few techniques you can use. This tip discusses three of them.

Inserting a Page Break Every X Rows
As you format your worksheet, Excel allows you to add page breaks where you'd like. If you want to put in a series of page breaks at a specific interval, you'll appreciate the macro presented in this tip.

Inserting the Current Time with Seconds
If you need to insert the current time, with seconds, then you'll need the macro discussed in this tip. It's easy to use and can give you just the information you need.

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.

Jumping to the Start of the Next Data Entry Row
Want a quick way to jump to the end of your data entry area in a worksheet? The macro in this tip makes quick work of the task.

Limiting Scroll Area
If you need to limit the cells that are accessible by the user of a worksheet, VBA can come to the rescue. This doesn't involve the use of macros, but the setting of properties.

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

Macro for Month Name
Need to know how to generate a full month name based on a date? It's easy to do, as discussed in this tip.

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, while Running, Stops Excel from Responding
When running a macro, have you ever seen Excel appear to stop responding? This can be frustrating, but there are a couple of things you can try.

Macros in Two Workbooks Interfere with Each Other
Having macros in multiple open workbooks can sometimes produce unexpected or undesired results. If your macros are behaving oddly or interfering with each other, this tip provides a few ideas you can use to track down the problem.

Macros Not Available in Later Versions of Excel
What would you do if you had a macro-enabled workbook that refused to work properly on computers using later versions of Excel? Well, the solution could be as simple as checking the trust settings on the systems, as described in this tip.

Macros Run Fine Individually, but Not Collectively
Developing macros can be rewarding, but it can also be challenging. Getting individual macros to run properly is hard enough but getting them to work properly in conjunction with other macros can present an entirely different set of problems.

Macros Run Slower in Newer Excel?
If you run a macro you used in an older version of Excel on a newer system, it may seem like the macro runs slower. Here are some ideas of why that might be happening.

Magnifying Only the Current Cell
You can use the Zoom feature of Excel to magnify what Excel shows of your workbook, but it affects the entire screen. What if you want to only magnify a small portion of the screen, such as the selected cell? There are a variety of ways you can approach this problem, as you'll learn in this tip.

Making a Cell's Contents Italics within a Macro
You can use macros to process information in your worksheets. You may want to use that macro to apply the italic attribute to your text. Here's how you can do it.

Making a Macro Button Stay Put
Excel allows you to add buttons to your worksheet that can be used to trigger macros. If you don't want those buttons to move around, but want them to stay put on the sheet, here are some approaches you can use.

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.

Making Modal Dialog Boxes Appear in Front of Workbooks
Perhaps the most common way of communicating with programs is through the use of dialog boxes. We expect dialog boxes to be accessible when they appear, but if they aren't then there can be some problems using Excel.

Making Sure Cells are Filled In before Saving
When creating a workbook that will be used by others, you may wish to ensure that the user fills in some cells before saving. Here is a discussion on how you can enforce this data entry.

Making Sure Numbers Copy as Numbers
When you copy information from one worksheet to another using a macro, you might not get exactly what you want. This tip looks at how to make sure that your macro copies numbers as numbers, even if they start out as text.

Making Worksheet Copies for Daily Shifts
Creating lot of copies of a worksheet is a snap within a macro. This tip introduces a macro that will make three copies of a master worksheet for each day in an entire month.

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.

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.

Negating a Cell Using a Macro
There are two ways to create macros: recording them or writing them from scratch. Some things cannot be done in a macro when you use the recorder, but can be easily done by doing the writing yourself. Negating the contents of a cell is one such example.

Notification when Recalculation is Done
If you manually recalculate your workbooks, you are probably doing so because of the time it takes to do the calculations. If you would like Excel to audibly notify you when the recalculation is complete, then you'll need to implement one of the macro-based approaches provided in this tip.

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.

Opening a Workbook and Suppressing Automatic Macros
Want to stop Excel from running any automatic macros that may be stored with a workbook? Here's how to do it.

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.

Out of Memory Errors for Macros
Tracking down memory errors in a macro can be frustrating. The error message is inherently vague and correcting any errors may involve a lot of trial and error. This tip focuses on a few things you can check out if you have this type of error.

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.

Page Numbers in VBA
When you print a larger worksheet, Excel breaks the printout across several pages. You may want to know, before you print, which page a particular cell will print on. There is no intrinsic function that delivers this information to you, but you can develop a macro that should provide just what you are looking for.

Pausing Macros for User Input
Does your macro need to get some input from a user? Here are the ways that Excel provides for that input to be solicited.

Positioning a Column on the Screen
If you have static columns and dynamic columns on the screen, you may want the dynamic columns to always show a particular range. Getting just what you want is a snap when you apply the techniques discussed in this tip.

Preserving the Undo List
The undo list can be a lifesaver when working in a macro. Unfortunately, the undo list is not preserved when you run a macro. Here are some ideas on how you can "undo" any changes that might be introduced by your macro.

Progression Indicator in a Macro
When your macro is humming along, minding its own business, a user watching the screen may not see any activity and therefore may wonder if the macro is really working. One way to keep users up-to-date on what is happening is to have the macro provide some sort of progress indicator.

Pulling Apart Characters in a Long String
You can easily use formulas to pull apart text stored in a cell. For instance, if you need to pull individual characters from a text string into sequential cells, there are a number of ways you can approach the problem.

Pulling Cell Names into VBA
Excel allows you to define names that can refer either to ranges of cells or to constant information, such as formulas. If you have quite a few names in a workbook, you may want to derive a list of those names. The macro in this tip allows you to pull them all and place them in a worksheet where you can continue to work with them.

Pulling First Letters from Parenthetical Text
When working with text in Excel, you can slice and dice it in many ways. This tip shows how to pull first letters from within parenthetical text in a cell.

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.

Putting the Last Saved Date in a Cell
Do you need a cell in your worksheet to display the date on which the workbook was last saved? This can be a bit tricky, but the macros presented in this tip will do the trick for you.

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,

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.

Recovering Macros from Corrupted Workbooks
Workbooks get corrupted from time to time; that's a fact of life in an Excel world. If those corrupted workbooks contain macros that you need to recover, there are a few steps you can go through to get them back.

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.

Relative VBA Selections
Need to select a cell using a macro? Need that selection to be relative to the cell you currently have selected? Here are the techniques you need in order to make the desired selections.

Removing a Directory
Macros allow you to perform all sorts of file-related operations. One such operation allows you to delete a directory. This tip shows how easy this can be.

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 File
Need to rename a file in a macro? It's easy to do using the Name command, as discussed in this tip.

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

Renaming Worksheets Based On a List
Renaming a worksheet within a macro is a relatively easy task. When you start renaming based on a range of names, though, the process may get a bit trickier, as demonstrated in this tip.

Replacing Commas with Periods
Want to replace all commas in a formatted number with periods, and vice-versa? There are a couple of approaches you can use, as detailed in this tip.

Replacing Letters with Numbers
If you have a text string that contains both letters and numbers and you want to convert those letters to numbers representing their position in the alphabet, doing the conversions can present a challenge. This tip examines several different ways you can get the results you need.

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.

Resetting Default Names for New Worksheets
When you add a new worksheet to a workbook, Excel gives it a default name that consists of "Sheet" followed by a number. If you delete worksheets from the workbook, Excel doesn't reset this number to reflect the reduced number of worksheets. Here are a couple of ways to deal with this oddity.

Resizing Checkboxes
If you create a user form in VBA that includes checkboxes, you may want to make the checkboxes larger. You can't adjust their size, but you can use the workaround provided in this tip.

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.

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 in a Number of Workbooks
Got a macro that you need to run on each of a number of workbooks? Excel provides a number of ways to go about this task, as described in this tip.

Running a Macro when a Worksheet is Activated
Want to run a macro when you first select a worksheet? You can do so by using one of the event handlers built into Excel, as described in this tip.

Running a Macro when a Worksheet is Deactivated
When you change from one worksheet to another, you may want to have Excel automatically run a macro for the worksheet you are leaving. You can easily create such a macro by using the Auto_Deactivate event.

Running a Macro while in Edit Mode
Excel doesn't allow you to run a macro while editing the contents of a cell. The only solution is to get out of Edit mode, as discussed in this tip.

Running a Procedure when a Workbook is Opened
Ever want to have Excel run a procedure whenever you open a workbook? It's not as difficult as you might think. Here's how.

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 an Unsavable Workbook
Macros can allow you to do some fancy data validation in your workbooks, such as checking to see if the user entered values in a particular cell (or cells). If your solution to those cells not being filled in correctly is to stop the saving of the workbook, this can cause problems when you are first developing the workbook. This tip looks at the problem and some ways around it.

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.

Saving Information in a Text File
The VBA programming language provided with Excel allows you to create and modify text files quite easily. Here's how to open a file, write information into it, and then close the file.

Searching Very Large Strings in a Macro
VBA provides a few different ways you can search for information within strings. This tip looks at the most efficient approach to searching for information in very, very large strings.

Seeing the Difference on the Status Bar
Do you often need to know the difference between two values in your worksheet? This tip shares a quick little macro that displays that difference on the Status Bar.

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 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 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.

Selecting the First Cell In a Row
When creating macros, you'll often have a need to select different cells in the worksheet. Here's how to select the first cell in the row.

Selecting to the Bottom of a Column in a Macro
Need to select a range of cells in a column? This tip can help, as it shows how to select from a specific cell all the way through the last-used cell in the column.

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.

Selectively Importing Records
Want to easily control which records get imported from a text file into Excel? It's easy to do when you write the macro that handles the importing.

Self-Aware Macros
Sometimes it may be helpful for a macro to know exactly where it is being executed. This tip provides a way that you can build such functionality into 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.

Separating Evens and Odds
If you have a series of values in a column, you might have a need to separate the values into even values and odd values. There are number of ways this can be done, as discussed in this tip.

Setting Column Width in a Macro
Does your macro need to change the width of some columns in a worksheet? Here's how to do it.

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.

Setting the Active Printer in VBA
Your macros can control where printed output is directed, but sometimes it can be difficult to get the settings correct. This tip explains why this is and shares some ideas on how you can make the setting correctly.

Sharing Macros with Others
If you develop some handy macros that you use on your system, you may want to share those macros with others. This tip discusses a few ways you can get macros from your system to someone else's.

Sheets for Months
One common type of workbook used in offices is one that contains a single worksheet for each month of the year. If you need to create these types of workbooks frequently, you'll love the macro highlighted 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.

Skipping Hidden Rows in a Macro
As your macro processes information in a worksheet, you may want to make sure that it skips over rows that are hidden. The key is to check the status of the Hidden attribute, as illustrated in this tip.

Specifying a Delimiter when Saving a CSV File in a Macro
You can, within a macro, save a workbook in several different file formats that are understood by Excel. However, you may not always be able to specify a full range of options in your saving. This tip examines one such situation and describes ways you can deal with it.

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.

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.

Stopping a Checked Box from being Unchecked
When creating user forms for use in Excel, you are provided with a range of controls you can add, including check boxes. If you want to make sure that a check box stays checked once selected, you can apply the code discussed in this tip.

Stopping Excel from Deleting Macros from a Workbook
When working with very large workbooks, it is possible for Excel to behave erratically. This tip looks at ways you can protect your macros in very large workbooks.

Stopping Fonts from Changing
There are multiple ways that Excel can create a workbook, and the formatting in each creation method can be different. In this tip you discover how to figure out what creation method a macro is using so you can track down the source of formatting in the new workbook.

Store Common Macros in the Personal Macro Workbook
Want your macros to be available regardless of the workbook on which you are working? Here's how to store them in the place Excel has set aside for this very purpose.

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.

Storing Macros in Templates
How Excel uses templates is different than how Word uses templates. This tip looks at those differences and discusses three ways you can make updated macros available to others in your organization.

Summing Only Visible Values
When you use SUM to determine the total of a range of values, Excel doesn't really pay attention to whether the values are visible or not. Here's why and what you can do about it.

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

Swapping Two Strings
Strings are used quite frequently in macros. You may want to swap the contents of two string variables, and you can do so by using the three-line techniques introduced in this tip.

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.

Testing if a Workbook is Open
Your macros can easily open and manipulate other Excel workbooks. If a workbook you are trying to use is already in use by someone else, it may cause errors in your macro. Here is a quick discussion on how you can check to see if a workbook is already opened by someone else.

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

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).

Trimming Spaces from Strings
Need to get rid of extraneous spaces before or after the text in a string? VBA provides three different functions you can use to make the removal quick and easy.

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.

Turning Off Screen Updating
Want a quick way to speed up your macros? All you need to do is to stop Excel from updating the screen while the macro is running. Here's how to do it.

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 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 Subroutines
When developing macros, you can create subroutines. This is a great way to reuse common code and make your programming tasks easier.

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 Personal Workbook Filename
The personal macro workbook is a great place to save your macros that you want to use with any workbook. The personal macro workbook is saved in a specific place using a very specific filename. This tip looks at where and how the special workbook is saved.

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.

Understanding Variables in VBA Macros
You can create and use all sorts of variables in your macros. This tip examines all the different data types you can specify.

Unprotecting Groups of Worksheets
Unprotecting a single worksheet is relatively easy. Unprotecting a whole lot of worksheets is harder. Here's how you can make the task simpler.

Updating a Web Query Based on a Schedule
Want to make sure that a web query is only executed during certain hours? It may be as easy as scheduling when to turn the query on and off, as explained in this tip.

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 InputBox to Get Data
Need your macro to get some input from a user? The standard way to do this is with the InputBox function, described in this tip.

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.

Using R1C1 Formula References in a Macro
Besides the regular way of displaying formulas, Excel can also display them using what is called R1C1 format. If you are comfortable with this style of putting formulas together, you may want to also do so in your macros. Here is the skinny on how to work with these types of formulas properly.

Using Seek In a Macro
When reading information from a text file, your macro may need to start reading at a place other than the beginning of the file. This is where the Seek function comes into play.

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.

Using the Status Bar
When developing a macro, you may want to display on the status bar what the macro is doing. Here's how to use this important screen area and the macro commands that enable that use.

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.

Writing a Macro from Scratch
Creating macros can help extend what you can do in Excel. If you work with macros, you know that creating macros from scratch results in the most flexibility. Here's how to create your own macros from scratch.

Macros

Tips, Tricks, and Answers

This Site

Newest Tips

Subscribe

Links and Sharing

OUR COMPANY

OUR PRODUCTS

OUR SITES