Introducing VBA Coding

Introduction

In previous posts we have concentrated on spreadsheet functions and formulas, charts and pivot tables to calculate and present the data we have collected about our garden bird visitors. In this post we're going to begin some Visual Basic for Applications (VBA) coding to automate aspects of our Garden Bird Tracker. 

VBA and the VB Editor

VBA , or Visual Basic as it's widely known, is a programming language developed by Microsoft based on Basic language. It uses a combination of graphical and script elements, and is relatively user friendly. In many cases, it is possible to use the built in Macro recorder in Microsoft applications to build the VBA code for a task, and then save it for future use. Before we look at a recorded Macro, we need to add the Developer Tab to our Excel ribbon, and then look at the Visual Basic Editor (VBE).

In your open spreadsheet, click on file and then select Options from the drop-down list. From the sub-menu select Customise Ribbon and then tick the checkbox for Developer under the Main Tabs option:

Returning to the spreadsheet, we now see that there is a Developer Tab available:

We now have access to the VBE (first option on the left), and direct access to a list of any available Macros (second from left). Opening the Visual Basic option takes us into the VBE:

The right section of the window shows what modules of code (individual macros) are available; the large left are is where code can be written, edited or viewed. If you record a Macro, the underlying VBA code is generated for you; alternatively, code can be built from scratch in the VBE. For example here's a Macro recorded to select cell A1 of a spreadsheet and apply a red fill, step by step:

1 Start recording Macro

2 Name the Macro

3 Fill cell A1 in red, stop recording and then run macro again

4 View generated code

Opening the VBE and selecting the recorded Macro shows us the code that has been generated. Some of the code that has been generated is not necessary, so I've edited it in the coding window to the absolute minimum:

The Macro recorder can be very helpful if you have a complex formula that you need to convert into its coded row number and column number (R1C1) format (for instance our multiple look-up array formula); this what the spreadsheet function and coded forms look like:

As you can see, attempting to write this formula in R1C1 format would be complicated and prone to errors.

However, there are many instances where direct coding needs to be used, as in the example we are going to look at next.

Last Row

In our spreadsheet we are going to use some VBA code to go to the next available line with one click. The first thing to do is open the VBE, and then insert a new, blank module:

In the coding window, we can start to build our VBA by naming the module as Sub GO_TO_LAST_LINE()

 The name defines this a Subroutine that can apply to any spreadsheet.There can be no spaces, so the words are separated with underscores, and the name always ends with (). The code starts directly below the name and is broken down as follows:

DIM LAST AS INTEGER. DIM is an abbreviation of Dimension, and defines what type of information LAST is; we are stating that LAST is an INTEGER (whole number), because it is going to equal the number of the last cell in  column C of our spreadsheet that contain values. 

LAST = ActiveSheet.Range("C50").End(xlUp).Row This line assigns a value to LAST by stating that LAST equals the number of the last row in column C that contains data by starting at cell C50 and going up to where the data ends.

MSGBOX LAST This line will display a message box showing the last row number (the integer value of LAST).

END SUB is automatically generated and must be the last line of code.

Running the code by selecting the Macros tab in the ribbon, and then our module name and Run, we will see this:

Now that we know our last row, we can add some more code to select the last row plus 1 and add a value to that cell. The value we will use is the text "CURSOR JUMPS TO LAST LINE". Here's the code:

 And when the code is run, this is what we see:

Two last things to do - assign the macro to a trigger, which is the picture of the bird with a call-out saying "CLICK ME FOR LAST LINE". To do this, insert the picture and then right click inside it. From the drop-down list, select Assign Macro, and then select the named module we created and click OK. When you click the picture, the code will run.

And finally remove the lines of code in green below for the message box, and the value assigned to the row after the last:

The code will now select the last row without a message or anything being written into the cell. Note that in this example, the LAST range is set at C50; increase this to a value that you don't think your spreadsheet will reach, i.e. 5000 or 10000 so that the count doesn't stop in the middle of your existing data.

I hope you enjoyed this post - if you have any questions or need any help, let me know.

In the next post we're going to look at some VBA that will insert a picture into a report based on a LOOKUP function!