How do you time how long it takes for VBA code to run ?

Simple..

Step1: At the start of your code, create a variable called "Start" (or whatever)

Set it = Timer (that takes a snapshot of the time)

Step2: At the End of your code, you can use a messagebox to display how long it took.

MsgBox Timer - Start & " Seconds"

Don't use VLOOKUP, INDEX, or MATCH from VBA when finding things on a worksheet with VBA.

• The problem with these is that when the function doesn't find something, it will return a VBA error which you then have to trap and handle successfully.
• This is a very messy way of coding, and prone to errors.

The best way to look for things on a sheet is to use .Find

An annoying feature of Excel is not resetting the the used range. What is the used range ? - well, it's meant to be a square around all populated cells.

- How can you select the Used Range for this example ?

Loops are fundamental to programming, and in this tip, we'll look at FOR loops.

If you have never programmed before then this step by step guide will show you how to write your first program.

Q. What is a loop ?

A. I think this is best explained using an example.

Using VBA, there is the functionality for Excel to track most of what a user is doing to a workbook.

Armed with this information, a system designer can make things happen that help make the user's life easier.

Today, we're going to program a simple example of tracking selection change events. i.e. when you select something different from what is currently selected, an event is triggered.

How do you reverse a name in Excel ?

This tip is very useful if you have a list of names in "Surname FirstName" format and you want to reverse that quickly.

Well, in Excel, there are a set of useful string manipulation functions you can use for this: LEN, FIND, MID, LEFT, RIGHT

How do you find out whether a cell contains a certain string ?

- In VBA, it's easy.. use the INSTR function, and test whether this is greater than 0.

In Excel, you need to use both the LEN and the SUBSTITUTE functions.

=LEN(SUBSTITUTE(StringCell,StringToLookForCell,"",1))<>LEN(StringToLookForCell)

Here’s an Excel tip on the LEN function.

Generally speaking, LEN returns the amount of characters that a cell displays.

However, lets look at a few examples and exceptions:

1. Normal text:

This is one of the most useful Excel tips that you can use in emails or presentations– How to Capture a Screen Shot of your Excel.

There is a quick and easy way of capturing an Excel screen shot without buying commercial software.

Step1: Launch PAINT by going: Start => All Programs => Accessories => Paint

This is an Excel tip for those of you who don't like using the mouse, (like me) and prefer to use only the keyboard. This can be a big time saver if you use Excel every day.

Step1: Press the WINDOWS key on your keyboard. This is the key that has the same symbol as your START button:

This tip is a followon from the previous Excel tip on Calculation

Today, we're going to look at different ways to calculate

There are a few different ways to calculate something in Excel, and you can use each differently.

This tip shows you how to do a random sort of a list in Excel.

Suppose you have a list of people and you need a way to find out who goes first. Maybe you're the HR department of an investment bank and you need to decide quickly who to make redundant.

Here's a screen shot:

Calculation in Excel is a big topic, but I am going to try to give you the essentials about calculating formulae.

Here are a couple of useful Time-related tips for Excel.

The first one is the NOW Excel function
Lets see how it works..

Step1:
Enter the following formula in any cell:
=NOW()
For me right now, this returns: 03/02/2009 09:22

NOTE - if the column width is too small, you will see something like this:

Today's tip is a short one, but one of the most used in finance - Toggling the formats from date to number and back again.

The default date format in Excel is very rubbish, so whenever I enter a date, I always reformat it immediately into something sensible.

Lets show you how..

Step1:
Enter a date in a single cell.
For example - enter doday's date by using the Excel date shortcut for TODAY: CTRL + ;

Adding 2 strings together is very easy in Excel.

There are a couple of ways.

Method1: use: &" "& NOTE - this adds a space between the 2 strings Here's an example:

Method2: use CONCATENATE

This is a follow-on from tip on how to calculate the 1st day of the month in Excel

Suppose we want to calculate the last day of the month... can we use the same function ?

YES, but we need to be a bit more cunning this time.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

This tip explains how to Calculate the 1st Day of the month in Excel.

We know how to get today's date... use: =TODAY()

For me right now, this returns 30-Jan-2009 ... so is there a way to get the 1st Day of the month from this ?

YES - use the DATE function.

This is a follow-on from tip on entering an array formula in Excel

Once you have entered and array formula in Excel, you will notice that Excel won't let you modify it one cell at a time.

This Excel tip explains step by step how to enter an array formula. Why do I need array formulae ? I hear you ask. Well, one of the reasons is that you need them for functions that do not return their results in a single cell. Lets take MINVERSE for example. The following screen shot shows the data we're using:

This is a follow-on from the first tip on debugging a large Excel function call

This Excel tip is really one of the most useful ones I can think of. I use it all the time, so make sure you understand it

Suppose you have a large function call, and you want to understand what is going on.

Easy tip today... it's about how to delete sheets.

To delete a worksheet:

If you have selected the worksheet, press in sequence: ALT the e then l

This is a follow-on from tip 1 on Data Validation

When adding data validation to cells containing percentages, there is a little trick you need to know. If you're calculating a proportion: 100% being everything, 0% being nothing, you need to set the data validation to DECIMAL with range 0 to 1.

This is a follow-on from yesterday's tip and relates to XL 2003

For XL 2003, there are many ways to find and remove duplicates from a sorted column of data, but this is my favourite as it doesn't involve leaving formulae on the sheet.

With a bit of practice you can become very quick at it.

Suppose you have the following data:

Today's tip is about saving you precious time.

Suppose you have the following data, and you're trying to fill all cells with the correct formula:

There is a way to open Excel safely which prevents macros from running. - Excel sheet calculations will still run.

Suppose you have a big string like: "XXX0000sdsds000000000" and you want to know how many times "0" appears.

Here's our data:

The formula we use is:
=LEN(B3)-LEN(SUBSTITUTE(B3,"0",""))

Suppose you have a list of Names, and you only want the initials of the people.

There's an easy way to do this in Excel

Here's our data:

The formula we use is:
=LEFT(B3)&MID(B3,FIND(" ",B3)+1,1)

Pivot tables are a truly amazing invention. They allow you to summarise, slice and dice data in many different ways without the need to write fiddly formulae.

Let's take the simplest of examples: