Starting out with VBA

Opening the VBA Editor

If you are new to VBA, you will need to know where to start.  The first thing to do is open the VBA Editor.  The easiest way to do this is to start Excel, then press Alt-F11.  This will bring up the VBA Editor which is made up of 4 main windows.

1. The top left window is the VBAProject window that lists all the VBA objects that can contain code.
2. In the bottom left  is the Properties window.  When you select a module, form or other object, all the available properties that can be set will be visible here.
3. In the bottom right is the Watches window.  When you debug VBA code, you can "watch" the value of variables and other items so that you can step through the code and see how these variables change throughout code execution.
4. The top right window is the main code window.  This is where you place any VBA code that you write.

Adding a new Module

To get started, you will need to add a new Modue to your workbook.

1. First of all, you should select the VBA Project that refers to the workbook where you want to put your VBA code.  In this example, it is Book1.
2. Once you have selected the correct workbook, then click the drop down arrow next to the "insert object" button.  From here you can choose what type of object to insert.  In this case, you should insert a "module".

Adding VBA Code

Once you have your module added to your workbook, you can add some VBA code into the code window.

1. If you have just added the module, then the right hand VBA code window will already display the code page.  Otherwise, you can double click on the module object in the VBAProject window in order to display the correct VBA code window on the right.  Be careful - if you only single click on the VBAProject on the left, then the window on the right may not display the correct module on the right.  You have to double click to make the associated code window appear.
2. Once you have the correct code window on the right, you can add your VBA code.  Try typing in "sub SampleSub()" into the code window.  You will notice that VBA automatically adds "End Sub" to your code, and places a blue line to separate your new code from any other code you may have.

Other Excel Stuff

Kris Kringle

This is a small spreadsheet I built in 2004. It automates the process of people selecting a name out of a hat to create a Kris Kringle (aka Secret Santa). It is designed to ensure that no one (not even the person running the Kris Kringle) knows who is buying presents for who. It creates a text file that is then manually emailed to each person containing the name of the other person they have to buy for.

Download Kris Kringle

Sumproduct

Sumproduct is one of the most useful and versatile functions available in Excel. I have been using Excel (and Lotus 123 before it) since the '80s, and yet I didn't stumble on this function until 2006 when browsing various Excel forums.

For a really good read about sumproduct, have a look here