How to use the DateAdd function

To solve this problem we will use one of Excel's built-in functions DateAdd. This function provides the ability to Add and Subtract values from a given date. In this request we need to Add 4 years and 1 day to the date in a particular cell. So lets say we start with a spreadsheet that has the following dates that need to have 4 years and 1 day added to them.

Read More »

Extract Workbook Name from Full Filename

If you only have a full filename reference to a workbook the following macro can be used to extract the Workbook name. This can be useful if you want to test if a Workbook is already open before attempting to open it. This is good practice and avoids receiving the standard Excel warning messages if you attempt to open a Workbook that is already open.

Read More »

How to check a Workbook Exists

This macro will provide a quick and easy way to check that a Workbook exists before you attempt to access it. This can be a problem when trying to open a Workbook from within another Workbook, or a Workbook that is required has been deleted or moved. If you try to access a Workbook that does not exist your macro will fail with the following debug message

Read More »

How to use Data Validation

Invalid and inconsistent data is one of most common problems in Spreadsheets. If the data supplied is not in the right format or is not entered consistently then any following formula's or calculations will either not work or worse still provide a result that is not correct. Excel provides a built-in Data Validation feature that can be included to any spreadsheet. Data Validation provides error checking and instructions for the user as they enter data to a spreadsheet. In this post we will demonstrate some simple examples of Data Validation on a customer address list.

Read More »

How to print Worksheets to different Printers

The quickest and simplest way to solve this problem is by using a Visual Basic Macro. In this case we need to set specific commands to print Sheet A to one printer and then Sheet C to another printer and not printing Sheet B.

Read More »

How to use Day, Month and Year Functions

In many cases when working with Excel the we use dates to calculate formulas or display a report. The ability to use the built-in Excel functions that relate to dates and to extend them is a fundamental skill in Excel. This post is the first in a series that will describe the basic use of key functions in Excel for beginners.

Read More »

Disable Alerts and Warning Messages in Excel

When building macros there can be times when the standard Excel warning alerts need to be disabled in order for the macro to complete it's intended function. An example may be where you have developed a macro that needs to create a temporary worksheet to store some values and then before completing the macro you need to clean-up and delete that sheet. The standard Excel warning alert would be presented unless you include some code to disable that alert.

Read More »

How to check a Worksheet Exists

The following simple macro will provide a quick and easy way to make sure a Worksheet exists in a Workbook before you start any changes or other processing on that worksheet. This can be a problem when working with large numbers of worksheets that are created during a macro process. If you try to create a worksheet name that already exists your macro will fail with the following debug message

Read More »