VBA Examples

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
If you import some address data into Excel, you may notice that the leading zeroes from your ZIP Codes have been chopped off. Here's the reason why and what you can do about it.

Adding Ordinal Notation to Dates
If you use dates in your worksheet, you may want to add an ordinal indicator to the day of the month. This tip shows the best ways to achieve this result.

Adjusting Comment Printouts
Need to print out the comments in a worksheet, but you don't want the standard preface information Excel provides with each comment? You can copy the comments (without the name and colon) to a new worksheet using the technique in this tip.

Adjusting Values with Formulas
You can adjust values by using the Paste Special feature, but you may want to do it by applying a formula. This tip describes the rationale for such an approach, as well as a way in which it can be accomplished.

Appending to a Non-Document Text File
Do you need to write information out to a text file so it can be used by other programs? You can easily accomplish this task using a macro, as described here.

Automatically Closing a Workbook
Walk away from your computer, and your work is visible on the screen for all to see. For security purposes you may want a workbook to close automatically if it isn't used within a certain period of time. This tip discusses how you can create macros to accomplish that task.

Automatically Copying Formatting
You can easily copy the contents of one cell to another using a formula. There is no way to similarly copy formatting from one cell to another. There are a couple of workarounds you can use, relying either on macros or on the Camera tool.

Automatically Loading Add-ins
Want to load a particular add-in for use with a specific worksheet? Here's a quick way to do it using macros.

Automatically Printing a Range
If you want to automatically print a particular area of your worksheet at a specific time of day, you'll love this tip. With the use of two small macros, you can get just the automatic output you need.

Breaking Up Variable-Length Part Numbers
Part numbers can often be long, made up of other component elements. Breaking up part numbers into individual components is easy if each component is a particular length. The task becomes somewhat more difficult if the components can be variable lengths. This tip presents a variety of methods of extracting variable-length component elements of a part number when those components follow a discernable pattern.

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
Macros can do all sorts of manipulations of files and directories. One command you can use is the ChDir command, which allows you to change from one disk directory to another.

Changing Fonts in Multiple Workbooks
If you need to change fonts used in a lot of different workbooks, the task can be daunting—if you need to do it manually. This tip looks at a macro you can use to make the task more automatic.

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.

Changing Section Headers
Excel doesn't provide a very easy way to vary headers or footers during a printout. This tip explains how you can use a macro to do your printing and make the changes on the fly.

Changing the Shortcut Menu
If you want to change the Context menus used in Excel, on purpose, here's how to go about it. Just create a macro and make the change to the controls on a CommandBar.

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
The Undo stack is cleared automatically whenever you run a macro. This fact of Excel's behavior allows you to easily clear the stack from within the macro itself.

Closing a Read-Only Workbook
When you create a workbook that is read-only, users can still make changes to the workbook. When they exit, they are prompted to save their changes to another workbook (since yours is read-only). If you don't want users to be prompted to save their changes, you can use a simple macro that tricks Excel into thinking the workbook has already been saved.

Coloring Cells with Formulas
Easily seeing where all the formulas are in your worksheet can be handy. Here are some ideas on different ways you can color the formula-containing cells so that they stand out from the other cells.

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.

Combining Columns
Need to concatenate the contents in a number of columns so that it appears in a single column? Excel has no intrinsic way to do it, but a macro can make quick work of the task.

Comparing Workbooks
Do you need to compare two workbooks to each other? While you can use specialized third-party software to do the comparisons, a simple formula or two might be all that you really need.

Concatenating Ranges of Cells
Putting the contents of two cells together is easy. Putting together the contents of lots of cells is more involved, as discussed in this tip.

Condensing Multiple Worksheets Into One
Excel provides a consolidation tool that allows you to easily combine the data from a bunch of worksheets into a single worksheet. This tip explains how you can use this tool.

Conditional Page Breaks
When printing a report from Excel, it is not unusual to want to start a new page when something changes in your data. Using the Subtotals feature of Excel, you can easily have your report paginated on any field in your data. If you prefer, you can instead use the macro provided in this tip to insert the page breaks necessary for your printout.

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 Deleting Rows
Want to delete a bunch of rows in a worksheet based on the value in a certain cell of each row? There are a couple of ways you can approach the problem, as discussed in this tip.

Conditionally Displaying a Message Box
If you want to display a message box when a particular set of criteria are met, you'll be interested in the techniques in this tip. The approach discussed here relies on the use of the Change event for a worksheet.

Controlling Display of Toolbar Buttons
This tip contains a macro that will enable custom toolbar buttons whenever a worksheet is visible.

Converting an Unsupported Date Format
If you import data produced by a program other than Excel, it might include dates stored in a format not automatically recognized by Excel. This tip shows how you can do the necessary data conversion so that Excel can recognize the dates.

Converting Cells to Proper Case
An Excel macro to change cells from uppercase to lowercase.

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 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 Text Case
If your worksheets include lots of text, you may need this VBA macro to convert large ranges of text from upper to lower case.

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 Comments to Cells
Need to copy whatever is in a comment into a cell on your worksheet? If you have lots of comments, manually doing this can be tedious. Here is a macro solution that will make quick work of the copying.

Copying Headers and Footers
Need to copy headers and footers from one worksheet to another? How about from one workbook to another? Here are some techniques you can use to make the copying easier.

Counting All Characters
If you need to count the number of text characters in a worksheet, the macro in this tip can come in very handy. It looks at not only the text in cells, but also in text boxes.

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.

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.

Counting Unique Values
If you have a list of values in a range of cells, you may want to know how many unique values appear in the list. The ways you can go about making that determination depend on whether the list has blanks in it or not. This tip explains your options.

Counting Words
Do you need to know how many words are in a range of cells? Excel provides no intrinsic way to count the words, but you can create a quick macro that will provide the figure.

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
When you create macro, you might want to use it to create a folder on your hard drive. You can accomplish this with one simple command, as described in this tip.

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 a File in a Macro
Need to delete a file within your macro? If you know the path and name of the file, it is easy to do with a simple command.

Deleting All Names but a Few
Want to get rid of most of the names defined in your workbook? You can either delete them one by one or use the handy macro described in this tip.

Deleting Blank Columns
Import data from another program, and you could end up with a lot of blank columns in your data. Here's the quickest way to get rid of these empty columns.

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 Every X Rows 2
A macro comes in handy when certain rows need to be removed from a worksheet.

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.

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.

Detecting Types of Sheets in VBA
When processing workbook information in a macro, you may need to step through each worksheet to make some sort of changes. It is helpful to know what type of worksheets you are working with so that you don't try to make changes on an inappropriate worksheet.

Determining a Random Value
One of the better-known VBA functions is Rnd, which allows you to generate a random value between 0 and 1. You can use this for all sorts of purposes, as described here.

Determining Differences Between Dates
Need to do a bit of "date math" in a macro? It's easy using the DateDiff function, described in this tip.

Determining If a File Exists
Macros can be used to access and manipulate data files on your hard drive. It is a good idea for the macro to determine if the file exists before actually trying to do anything with the file. This tip shows a way you can determine if the needed file is actually on the hard drive.

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 Caps Lock is On
If you have a macro that requests user input, you may want to make sure that the user doesn't make entries if the Caps Lock key is engaged. This tip explains how you can check the condition of the key so that you can make decisions in your macro.

Determining the Current Directory
When creating a macro, you may need to determine what the current directory is according to Windows. You can easily figure this out by using the CurDir function.

Determining the Hour of the Day
Need to know what hour of the day a macro is running? You can determine the information by using the Hour function, described in this tip.

Determining the Length of a String
Macros are great a 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
One of the properties that Excel maintains for a workbook is the date it was last modified or saved. This tip explains how you can access that date and place it in a header or footer for a worksheet. (This can only be done with a macro.)

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 Headers and Footers
Do you want to change the headers and footers that appear on different pages of your printout? Here's how you can get just what you want.

Dynamic Worksheet Tab Names
Would it be helpful if your worksheet tab names were determined by the contents of a cell in your worksheet? Using the macros described in this tip, you can easily make the worksheet tabs just as dynamic as any other part of an Excel worksheet.

Easily Adding Blank Rows
If you need to "open up" the data in a worksheet so that there are blank lines available, you'll like this tip. It explains the easiest ways to add just the space you need.

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.

Exporting Black and White Charts
Excel's charts are normally created in color, but you can print them in black and white. You may be looking for a way to export the black and white version of a chart so that it can be used in a different program. This tip explains the different ways you can accomplish your desire.

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
When importing a text file into Excel, you have the opportunity to define the characteristics of the data being imported. If the data has quite a few fields in it, specifying the characteristics of each field can be tedious. This tip explains a couple of ways you can make faster work of this importing process.

Finding and Deleting Links
A VBA macro to find and delete external links.

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. You can implement this type of feature by using 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 writing macros, you need to know how to convey error messages to the user should an error actually crop up. This tip examines one technique that makes the task fast and easy.

Full Path Names in Excel
If you want to see the full path name for a particular workbook, you'll like the macro in this tip. It allows you to display the path on Excel's status bar. Assign the macro to a toolbar button, and your desired information is only a quick click away.

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
Creating a macro to compile a list of macros in an Excel workbook.

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 Input from a Text File
You can use a macro to read information from a text file. The steps are easy, and then you can use that information in any way you see fit.

Getting Rid of Extra Quote Marks in Exported Text Files
In an effort to be helpful, Excel may add extra quote marks around text that it exports to a text file. If you don't like the format in which text is exported, then the solution is to create your own export capability with a macro, as described in this tip.

Getting Rid of Many Hyperlinks
Got a bunch of hyperlinks you need to get rid of? Here's a handy (and simple) macro that can do the task for you.

Getting Rid of Non-Printing Characters Intelligently
Is your worksheet, imported from an external source, plagued by non-printing characters that show up like small boxes on-screen? You can get rid of them using the various techniques described in this tip.

Grabbing the MRU List
Want to use the list of most recently used files in a macro? You can access it easily by using the technique presented in this tip.

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.

Highlighting the Rows of Selected Cells
If you lose your place on the screen quite often, you might find it helpful to have not just a single cell highlighted, but the entire row. This tip provides a couple of ways you can achieve this goal.

Hyperlinks to Charts
You can create hyperlinks to all sorts of worksheets in a workbook, but you cannot create a hyperlink to a chart sheet. This tip provides a workaround that should display just what you want hyperlinked in the first place.

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 and Copying Rows
Want an easy way to insert a new row in a worksheet and copy everything from the row above? (You end up with two identical rows this way.) Here's a handy macro that can do this edit in one quick step.

Inserting Dashes between Letters and Numbers
If you need to add dashes between letters and numbers in a string, the work can quickly get tedious. This tip examines some ways you can make the work faster and easier.

Inserting the Saved Date In a Header or Footer
Using a macro to set the date the workbook is saved in the header or footer.

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.

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.

Last Saved Date in a Footer
When printing out a worksheet, you may want Excel to include, in the footer, the date the data was last saved. There is no easy way to include that information without using a macro. Here's how you can get the output you need.

Limiting Entry of Prior Dates
You can use the data validation feature of Excel to limit what is entered in a cell, including the way that dates are entered. This tip provides an advanced way to limit exactly which dates can be entered.

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
If you are formatting or processing data within a macro, you might want to make the contents of a particular cell bold. This tip explains how easy it is, requiring only a single line in your macro code.

Making a Cell's Contents Italics within a Macro
Need to make the contents of a cell an italic typeface? It's easy to do when you are processing information in a macro.

Multiple Line Headers and Footers
Using a macro code to set up a multi line header or footer.

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.

Numbers Spelled Out
It is not uncommon to have a need to spell out numbers, such that "123" becomes "one hundred twenty three." There is no intrinsic way to do this in Excel, but by using a macro you can do the desired conversion.

Opening an HTML Page in Excel
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.

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.

Parsing Non-Standard Date Formats
If you import information into a worksheet from a different program, you may be surprised when Excel doesn't parse the dates correctly. This tip describes some ways in which you can help Excel to make sense of the imported data.

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.

Positive and Negative Colors in a Chart
When creating a line cart, the line can show values both positive and negative values. This tip explains how you can use different colors to display that portion of the line that dips below zero into negative territory.

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
The data in some worksheets consists of just a column or two of data, extending for many rows. When you print these worksheets, you end up with a printout that has a lot of white space on the right side of the page. You could use less paper if you were able to "snake" your column on the page so that it occupied multiple columns. This tip shows you how.

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
Do you want to "condense" how Excel prints a workbook so less paper is used? This tip describes a couple of things you can do to get the desired results.

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
When you take a look at the Properties dialog box associated with any workbook, you'll notice that Excel tracks quite a bit of information about the workbook itself. This tip shows how you can get this property information into a worksheet so you can print it out.

Progression Indicator in a Macro
If you use macros to process data in a workbook, it can often appear like the macro is doing nothing, even though it is working hard behind the scenes. This is the time when you need some sort of indicator on-screen to let users know that the macro is, indeed, working away.

Pulling Apart Cells
The Text to Columns tool is a great boon for analyzing textual data in a worksheet. You can use the tool to strip text into component parts that you can work with easier. This tip explains how to use the tool and introduces a macro you can use when the tool won't work just the way you want it to.

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

Pulling Formulas from a Worksheet
The formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can then easily make a copy of those formulas for documentation or analysis by applying the ideas in this tip.

Putting Cell Contents in Footers
Do you need the contents of a particular cell to always appear in the footer of a worksheet? You can take care of this using a macro, as described in this tip.

Quickly Changing Windows
Need to quickly move from one open Excel window to the other? The program provides a couple of handy shortcuts you can use, but if you want to create a toolbar button you can use instead, you'll want to use the macro in this tip.

Referencing External Cell Colors
If you want to reference cell colors external to your current workbook, there is no way to do it using Excel functions. You can, however, create your own macro that will do the referencing for you.

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
Do you need to completely reformat the data you import into Excel? This tip shows how you can break a single column of data into multiple columns of input.

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 a Text Box in a Macro
Text boxes are easy to add to a document and manually resize, as needed. If you want to resize the text box in a macro, however, the way to do it effectively may seem elusive. This tip explains how you can resize a text box to cover a desired range of cells.

Resizing Checkboxes
User forms, created in VBA, can be very helpful for a user interface. If the default checkboxes in the form are too small for your liking, there are only a limited number of things you can do, as described 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.

Retrieving Worksheet Names
Want to grab the names of all the worksheets in a workbook? Here's how you can stuff all those names into the cells of a worksheet.

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 Macro When a Worksheet is Activated
Need Excel to run a particular macro whenever a worksheet is selected? It's easy to do, as you'll discover in this tip.

Running a Procedure when a Workbook is Opened
Do you have a macro that you need to run whenever you open a workbook? The easiest way to do this is to use the special Auto_Open function, as described in this tip.

Saving Information in a Text File
When writing macros, VBA includes quite a few different commands you can use to manipulate text files. This tip shows how easy it is to use some of those commands to save information into a text file.

Searching a Workbook by Default
When you display the Find tab of the Find and Replace dialog box, you'll notice that any search, by default, will be on the current worksheet. If you want Excel to default to searching the entire workbook, you may be out of luck.

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 Cells of a Specific 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.

Selecting Random Names
Got a tone of names from which you need to select a few random names? There are several ways you can extract what you need; several different ideas are explained in this tip.

Selecting the First Cell In a Row
Write enough macros, and it is inevitable that you will need to select the left-most cell in a given row. This tip explains the technique to use.

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
Got a huge amount of data you need to import into Excel? Sometimes the best way is through the use of a macro, as described in this tip.

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
Need to change the layout of your worksheet in a macro? You can adjust the width of individual columns by using the ColumnWidth property. This tip shows you how.

Shading Rows for Ease in Reading Output
Shading every second, third, or fifth row of a printout can be helpful for reading data. This tip describes how to use a macro to get the formatting you desire.

Sheets for Days
Need a quick way to have a worksheet for each day in a month? Here's a macro that makes the worksheet creation a snap.

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.

Shortcut for Pasting Only Values
Excel's Paste Special command is used quite a bit. If you want to create some shortcuts for the command, here's some ways you can approach the task.

Shortening ZIP Codes
US ZIP Codes can be of two varieties: five-digits or nine-digits. Here's how to convert longer ZIP Codes to the shorter variety.

Showing Filter Criteria on a Printout
When you print out a filtered worksheet, you may want some sort of printed record as to what filtering was applied to the worksheet. Here's a couple of ways you can get that printed record.

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.

Sorting Data Containing Merged Cells
When formatting the layout of your worksheet, Excel allows you to easily merge adjacent cells together. This can cause havoc later, however, since you can't sort ranges that contain merged cells. This tip provides some guidance on how you can get around this limitation.

Sorting Data on Protected Worksheets
Protect a worksheet and you limit exactly what can be done with the data in the worksheet. One of the things that could be limited is the ability to sort the data. This tip explains how you can allow that data to be sorted.

Specifying Date Formats in Headers
Dates in headers and footers are formatted using the same pattern used in Windows for the system date. If you need to use a different format, you'll appreciate the information in this tip.

Specifying the Size of Chart Objects
Create a new chart object in an existing worksheet, and Excel automatically makes the object 25% of whatever the screen size is. If you want a different size (without manually resizing), you are out of luck unless you use a macro to create the chart object. This tip explains how you can do this.

Spreading Out a Table
Need additional space in a table? You can quickly add any number of rows desired between each row of an existing table by using the handy macro presented in this tip.

Suppressing the Reviewing Toolbar on E-mailed Workbooks
If you get tired of the Reviewing toolbar popping up whenever you open a workbook you received via e-mail. You'll love the information in this tip. Add the provided macro, and you can turn the toolbar off just as soon as it appears.

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.

Toggling AutoFilter
Want a tool that will help you toggle AutoFilter on and off? Excel provides some tools you can use, but you need to be careful that you choose the right ones.

Trimming Spaces from Strings
It is not unusual for user-generated input or for text from unknown sources to include either leading or trailing spaces. VBA includes a trio of functions that allow you to easily remove any such spaces from the text.

Turning Off AutoFill for a Workbook
Don't want people using your workbook to be able to use AutoFill? You can add two quick macros that disable and enable the feature when your workbook is activated and deactivated.

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 the If ... End If Structure
An explanation of If … End If structure of a VBA macro in Excel.

Unhiding a Single Column
In a worksheet with lots of hidden columns it is a real pain to try to unhide just one or two columns. The best solution is to use a macro to do the unhiding, as described in this tip.

Unhiding Multiple Worksheets
Excel allows you to only unhide a single worksheet at a time. The way around this is to use a macro that allows you to unhide lots of worksheets at once.

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 Different Footer on Secondary Pages
When printing a worksheet, you may want to have the footer different on the first page of your document than it is on subsequent pages. Here's a couple of ways you can get the desired changes.

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

Using an Exact Number of Digits
Excel allows you to format numeric data in all sorts of ways, but specifying a number of digits independent of the decimal point is not one of the ways. If you need this very specific type of formatting, you can use some of the ideas in this tip.

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 Excel for Timing
Excel allows you to store times in a worksheet. If you want to use Excel to time certain events, there are a couple of ways you can approach the task.

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 writing to or reading from a text file in a macro, you may want to specify where the writing or reading is to take place. The command you use to do this is the Seek command, described in this tip.

Using the Camera in VBA
The camera tool allows you to capture dynamic "pictures" of portions of a worksheet. If you want to use the camera tool from within a macro, you'll need to apply the techniques discussed in this tip.

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.