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.

Step1: Open a blank workbook, and press ALT + F11 - this brings up the VBA editor.

Now, double click on "Sheet1".. in blue in the picture.

- You are now in the code module of "Sheet1"

Step2: From the middle dropdown that says "General", select "Worksheet"

- VBA editor presents you with a subroutine that is designed to pick up the event that the selection has been changed.

Step3: In the empty line after "Private Sub", type the following: MsgBox "Hello" Press ALT + F11 again to return to sheet1, and press the DOWN ARROW to change the selection.

- You will now see the message box confirming that you have successfully trapped this event.

Here's an example sheet to play with

This functionality is used to great effect in my game of Noughts and Crosses, and in this Smart Table Sorting

Training Video - Worksheet Events

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.

Suppose you're at a farm, and a pig has just given birth to 10 piglets. The farmer asks you which is the biggest piglet. In order to answer this question, you will look at how big each piglet is, and once you have looked at all the piglets, you'll make your decision. A loop is the programming equivalent of looking at each piglet. Now lets use this example.

Step1: When you have the data as shown in the picture, Press ALT + F11 to bring up the VBA editor. Step2: You'll now need to insert a module, so right click on the blue area shown in the screen shot, and go: insert=>module

Step3: Now, in the white space provided, type the following code:

Step4: Click anywhere within the code, and press F5 - F5 runs code. You will now see a message

box showing which piglet is the biggest.

Download workbook to play with

VBA training Video - FOR Loops

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 ?

Step1: Press CTRL + END - This will select the last populated cell

Step2: - Now press CTRL + SHIFT + HOME

... all your data is now selected. Now, here's the annoying thing. Suppose we delete the last 4 rows, and press CTRL + END again. - the last cell that was populated (but isn't now) is selected.

To reset the used range, we need to resort to using VBA.

If this code is in the worksheet's macro module, and is run, pressing CTRL + END will now go to the correct cell.

Download workbook with code on how to reset the used range..

Check out this tip on how to view the worksheet's code if you are new to VBA

Training Video on how to reset the used range:

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

Here's an example that finds all whole instances of "Nick" on this sheet:

This code will find all instances of "Nick":

Download workbook with code on how to find all instances of a string on a worksheet

Training Video on how to use FIND in VBA:

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"

Download Spreadsheet to see example of how to Time VBA code

Training Video on how to time your code:

What can you do to make VBA code run faster? Well, when you run VBA code in Excel, you will notice that the screen flickerseach time an action is performed. Lets take a simple example:

If you run this, each time the cell below is selected, you will see that the screen has updated. This is fine as long as your macro is small, but if it is big, Screen Updating can take a long time, and turning it off can make VBA code run faster. Let’s prove that:

This code for me takes 0.64 seconds to run

Now, lets turn off Screen Updating and see the difference:

0.64 seconds vs 0.078 seconds The faster VBA code runs 8 times the speed of the slow code. If you run the same procedure many times a day, this can add up quickly, and allow you to go home earlier.

NOTE: I turned ScreenUpdating back on before displaying the messagebox... If you don't do that, you get something like this:

... which although it looks whacky is likely to annoy your users.

Download Spreadsheet to see how turning off Screen Updating speeds up VBA code

Training Video on how to make your code run faster by turning off ScreenUpdating:

I have a question about the FIND-VBA code.
How do add I an corresponding value to a new column?

For example next to every NAME the code adds an age looked up in a table.

Thanks Marcus