Tips, Tricks, and Answers

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

   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.

   Adding Ordinal Notation to Dates
Want to add an ordinal suffix to a number, as in 2nd, 3rd, or 4th? Excel doesn't provide a way to do it automatically, but the ideas presented here can be helpful in devising a way to get the desired notation.

   Adjusting Comment Printouts
Need to print out comments, but in a way that you control what is included in the printout? Here's a way you can extract the comments, place them in their own worksheet, and then format the printout to appear as you want.

   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 Copying Formatting
It's easy to automatically set the contents of one cell to be equal to another cell. But what if you want to copy the formatting as well? This tip explains how you can automatically copy the contents and the formatting of one cell to another.

   Calculating the Interval between Occurrences
With a long list of items in a worksheet, you may want to determine the last time a particular item appeared in the list. There are a couple of ways you can tackle this problem, as described in this tip.

   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 Months in a Workbook
When you copy a worksheet and then need to make changes to information in that worksheet (such as changing month names), the task can sometimes be daunting. Here are some ideas on how you can make the changes easier.

   Choosing Direction after Enter On a Workbook Basis
Excel lets you specify how it should behave when you press Enter. If you change this behavior, Excel assumes you want it changed for all workbooks on which you might be working. Here's how to adjust it so that the behavior can vary based on which workbook you are using.

   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.

   Combinations for Members in Meetings
Got a large group of people listed in a worksheet and you want to make sure that each person has met with every other person in the list? This tip explains a scenario in which you can figure out who needs to meet with whom.

   Condensing Multiple Worksheets Into One
Want a quick way to combine your worksheets? Excel provides a tool to make the task easier, but you could also bypass the tool and create a simple macro to do the same task.

   Conditional Page Breaks
Need to have your worksheet printout start on a new page every time a value in a column changes? There are a couple of ways you can accomplish the task, as described in this tip.

   Conditional Printing
If you need to make what Excel prints be based upon conditions in a worksheet, you'll love the information in this tip. There are a variety of ways you can approach the task and get just what you need.

   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.

   Controlling Display of Toolbar Buttons
You can assign your macros to a series of custom toolbar buttons, but you may only want those buttons to be visible when a particular worksheet is displayed. Here's how to make that happen.

   Converting an Unsupported Date Format
Excel makes it easy to import information created in other programs. Converting the imported data into something you can use can present a challenge. Here's how to make a change from an unsupported date format to one that Excel can work with.

   Converting Cells to Proper Case
When storing text in a worksheet, you may have a need to change the case of that text so that the initial letter in each work is capitalized. The macros in this tip will help to change the case as you require.

   Converting from Relative to Absolute
Addresses used in a formula can be either relative or absolute. If you need to switch between the two types of addressing, here's a convenient way to do it.

   Converting Imported Information to Numeric Values
If the information you import into Excel is treated as text by the program, you may want to convert it to numeric values. This tip explains some great ways you can approach the problem and get the numeric values you need.

   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 Text Case
Ever notice that if someone types in all CAPS, it appears they are shouting? If your worksheets include lots of text, you may need this handy macro to tame the shouting and convert large ranges of text from uppercase to lowercase.

   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.

   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 Cells with Text Colors
Got a bunch of cells that have different colored text in them? Here's a great way to count the occurrences of certain colors in those cells.

   Counting Shaded Cells
Ever want to know how many cells in a worksheet (or a selection) are shaded in some way? You can create a handy little macro that will do the counting for you.

   Creating a Center Across Selection Button
The ability to center text across a range of cells has long been a staple of experienced Excel users. Here's how to create a tool that can handle this type of formatting for you.

   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 Charts in VBA
Most charts you create in Excel are based on information stored in a worksheet. You can also create charts based on information supplied by a macro, however. This tip explains how.

   Creating Individual Workbooks
Workbooks can contain many worksheets. If you want to pull a workbook apart and create a whole series of workbooks based on each worksheet, here's the information you need.

   Creating Superscript and Subscript Buttons
Want a quick way to apply superscript and subscript to selected text within a cell? This tip shows how the formatting can be done through a user form and a small set of macros.

   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 Unwanted Styles
Custom styles can be a great help in formatting a worksheet. You may, at some point, want to get rid of all the custom styles within a given workbook. Here's a quick macro that can make that task easy.

   Deleting Worksheets in a Macro
Processing workbooks using a macro often involves the possible creation and subsequent deletion of worksheets. When it comes to deleting worksheets in a macro, the way that Excel processes the Delete method can hinder the quick automation often desired when doing the processing. Here's how to speed up worksheet deletion so that your automation can run at top speed.

   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 If a File Exists
Before you have your macro open and read a file from disk, you'll want to check to make sure it is really there. Here's how to do it.

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

   Disabling Printing
Don't want your worksheets to be printed out? You can make it a bit harder to get a printout by applying the techniques in this tip.

   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 Selected Cell's Address
Need to know the address of the cell that is currently selected? There is no worksheet function to return this information, but it can be easily determined using macros.

   Dynamic Worksheet Tab Names
Need a worksheet's name to change based on the contents of a cell? You'll need to rely on a macro to do the changing, but it the task is rather easy as you discover in this tip.

   Easily Adding Blank Rows
Want to add a bunch of blank rows to a your data and have those rows interspersed among your existing rows? Here's a quick way to do it using Excel's sorting capabilities.

   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.

   Faster Text File Conversions
Want to make your importing of text data faster than ever? Here are some ideas you can apply right away.

   Finding Cells Filled with a Particular Color
Do you need to find cells that are formatted with a particular color? How you accomplish this task depends on your version of Excel.

   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 Parent Folder
Do you need to figure out the name of the parent folder of whatever folder a worksheet is in? Believe it or not, this can be done with a worksheet formula.

   Forcing Input to Uppercase
If you type information into a workbook, you may want to make sure that what you type is always stored in uppercase. There is no inherent way to do this in Excel, but you can add the capability with a couple of short macros.

   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.

   Full Path Names in Excel
Need to know what the full path name is for the current workbook? With a simple macro you can display the full path name in the title bar of the workbook's window.

   Full Path Names in Headers or Footers
Using a macro to add the full path name into a header or footer in an Excel worksheet.

   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.

   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 Extra Quote Marks in Exported Text Files
If you don't like the way that Excel exports information you intend to use with other programs, then your best bet is to create your own export routine. This tip shows how easy this task can be.

   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.

   Inconsistent Output for Empty Columns in a CSV File
When you create a CSV file in Excel, the information stored in the file may not contain all the fields that you think it should. This tip examines the reasons behind this behavior, along with several different ways to work around the problem.

   Inserting the Saved Date In a Header or Footer
When preparing a worksheet for printing, you may want to include in the header or footer the last date the workbook was saved. This can't be done directly, but it can be done with the macro in this tip.

   Jumping to Alphabetic Worksheets
Got a workbook with a lot of worksheets in it? Here's some handy ways to jump to the worksheet you want, alphabetically.

   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.

   Maintaining the Active Cell
Move from one worksheet to another, and Excel selects whatever cell was last used in the worksheet you are selecting. If you don't want this behavior (you want to have the same cell selected on the new worksheet as on the old), then you can apply the techniques in this tip.

   Making a Cell's Contents Bold within a Macro
When your macro is processing information in a worksheet, do you need to periodically make the contents of a cell bold? You can do so by adding one simple statement to your macro, as demonstrated 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.

   Naming Tabs for Weeks
Need to set up a workbook that includes a worksheet for each week of the year? Here's a couple of quick macros that can do the hard work for you.

   Non-standard Sorting
Information in a cell can be entered using line feeds, which results in multiple lines of data in the same cell. If you later want to sort that information, there are a couple of ways you can approach the problem.

   Noting the Workbook Creation Date
You may want to add, to your worksheet, the date on which a particular workbook was created. Excel doesn't provide a way to do this, but you can use one or two simple macros to insert the information you need.

   Opening an HTML Page in a Macro
Excel allows you to open HTML pages within the program, which is great for some purposes. What if you want to open a browser window, however, from within Excel in order to display an HTML page? This tip highlights two methods you can use, within a macro, to perform the task.

   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.

   Printing a Single Column in Multiple Columns
Ever printed out a worksheet only to find that you have text only at the left side of each page? You can use more of each printed page by applying the techniques in this tip.

   Printing a Worksheet List
Want a list of all the worksheets in your workbook? Here's a short, handy macro that will place all the worksheet names into a single worksheet where you can work with them.

   Printing Multiple Worksheets on a Single Page
Got a bunch of worksheets and you want to save paper by printing multiple worksheets on a single piece of paper? There are several ways to approach the issue, and the one you choose will depend (to a degree) on the characteristics of the data you need to print.

   Printing Selected Worksheets
When you accumulate quite a few workbooks in folder, you might need to print out selected worksheets from all of the workbooks. Here's a macro that will load each workbook and print the desired worksheets.

   Printing Workbook Properties
Want to create a printed record of the properties associated with a workbook? There is no easy way to do it in Excel. Here's a macro, however, that you can use to create a worksheet that contains all your workbook properties.

   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 Cell Names into VBA
Excel allows you to define names that can refer to either 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.

   Putting Cell Contents in Footers
Referencing information between cells in a worksheet is a piece of cake using some elemental formulas. You cannot, however, use formulas to reference cell information in page footer. Here's a way you can get around this apparent shortcoming.

   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's the techniques you need in order to make the desired selections.

   Removing Conditional Formats, but Not the Effects
Conditional formatting is very powerful, but at some point you may want to make the formatting "unconditional." In other words, you might want to remove the conditions on which the formatting is based, but still keep the formatting that you see for those cells. This can only be done via a macro, as described in this tip.

   Reorganizing Data
If you need to consolidate a single column of data into multiple columns of data, you'll love this macro. It provides a way for you to move data to as many columns as you want.

   Replacing and Converting in a Macro
When you use a macro to process data you always run the risk of making that data unusable by Excel. This is especially true if you are using the macro to convert from one numbering system to another. This tip examines one way in which this can occur and what you can do about the problem.

   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.

   Returning Zero when a Referenced Cell is Blank
Reference a cell in a macro, and if that cell is blank Excel normally equates that to a zero value. What if you don't want Excel to make that assumption? Here's ways you can make sure that Excel doesn't miss the distinction.

   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 Workbook is Closed
One of the automatic macros you can set up in Excel is one that is triggered when a workbook is closed. This tip explains how you can create a macro that runs whenever the workbook is closed.

   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.

   Saving Information in a Text File
The VBA programming language provide 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 a Workbook by Default, Take Two
How to create a macro that will display the correct Find and Replace box to set searching parameters.

   Selecting All Visible Worksheets in a Macro
Do you need your macro to select all the visible worksheets (and just the visible ones)? It's not as easy as it sounds, but this tip uncovers the best way to do it.

   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.

   Selective Headers and Footers
Want to print different headers or footers on different parts of your worksheet? Excel has no inherent way to do this, but a short macro can make sure that the headers and footers are printed on only the pages you want.

   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.

   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.

   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.

   Simultaneous Scrolling
If you have two worksheets displayed at the same time, you might want those worksheets to remain visually "in sync" with each other. Here's a couple of small macros you can use to scroll the worksheets simultaneously.

   Sizing Text Boxes and Cells the Same
Adding a text box to a worksheet is easy. Making sure that text box is the exact size of a cell in the worksheet may not be as easy. Here are the easiest ways to make sure the text box matches the cell (or cells) you place it over.

   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.

   Snapshots of Excel Worksheets for PowerPoint
If you need to get lots of information from Excel to PowerPoint, the task can be daunting. This tip explains different approaches you can take when you need to get data from here to there.

   Specifying Date Formats in Headers
Don't like the default date format used by Excel when you place the date in a header or footer? You can use a macro to get just the exact type of date format you want.

   Specifying the Size of Chart Objects
Unhappy with the default size that Excel uses for embedded chart objects? You can't change the size at which they are originally created, but you can use the techniques in this tip to resize them all at the same time.

   Spreading Out a Table
If someone sends you a worksheet that has lots of data in it, you might want to "spread out" the data so you can have some blank rows included between each existing row. This is easy to do using the handy macro in this tip.

   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 technique introduced in this tip.

   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.

   Unhiding Multiple Worksheets
You can hide a bunch of worksheets at the same time, but Excel makes it impossible to unhide a bunch at once. You can, however, create your own method for unhiding a group of worksheets all at one time.

   Unique Name Entry, Take Two
If you need to make sure that a column contains only unique text values, you can use data validation for the task. This won't help when someone decides to copy and paste information, however. This tip addresses how you can block such actions using a macro.

   Unlocking Charts
Objects within a workbook are often locked as a form of protection. Your macro, however, may have a need to work with some of those locked objects. You'll need the information in this tip so you can create the macro code necessary to unlock the objects.

   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.

   Using a Progress Indicator in Macros
A few tips on adding a progress indicator that runs during long macro calculations.

   Using BIN2DEC In a Macro
Need a way, in a macro, to convert binary numbers into their decimal equivalents? There are two ways you can get the desired conversion, as described in this tip.

   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 Multiple Print Settings
Do you have a worksheet from which you need to print only portions of the data available? There are two ways you can control what gets printed—by using a macro or by using custom views.

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

   Working With Multiple Printers
If you have multiple printers accessible to your computer, you may need a way to quickly print your worksheet on a specific printer. This is an easy task for a macro, as described in this tip.

   Zooming with the Keyboard
Excel doesn't provide a keyboard shortcut that allows you to zoom in or out on your workbook. It is easy, however, to create a couple of short macros that can do the zooming and be tied to keyboard shortcuts.