Simple programming with Excel

The big challenge with getting people excited, or even interested, in programming is coming up with an example that is both simple and useful. That pretty much rules out creating a program from scratch, but there are some useful things you can do with Microsoft Office. Office has its own programming language called Visual Basic for Applications or VBA. I once used this to create my own function in Excel. It took about half an hour and saved my wife hours of work.

The spreadsheet tracked the grades of children in my wife's class. Children were expected to move up one grade every term in each of the four core subjects. Automating this if the grades were numbers would have been laughably simple, =A1+1 would have done the trick.

Sadly it wasn't that simple because the grades were a combination of letters and numbers: 1c, 1b and 1a. It would have been possible to write a formula using the IF command but with a total of 13 possible grades it would have been too long.

The solution was to write a simple funtion that used a different way of working through different options called Switch. Here's how the function looks and then I'll explain how I got there.

Function RaiseOneGrade(Grade As String) ¹

RaiseOneGrade = Switch( _
Grade = "p5", "p6", Grade = "p6", "p7", Grade = "p7", "p8", Grade = "p8", "1c", _
Grade = "1c", "1b", Grade = "1b", "1a", Grade = "1a", "2c", Grade = "2c", "2b", _
Grade = "2b", "2a", Grade = "2a", "3c", Grade = "3c", "3b", Grade = "3b", "3a", _
Grade = "3a", "4") ²

End Function ³

It is used in the spreadsheet like this. =RaiseOneGrade(A1)

Line 1 creates the function, gives it the name RaiseOneGrade and then defines what information it will expect. In this case it is a single bit of text that I decied to call Grade.

Section 2 is where the action happens. The Switch command is followed by a series of possible options and what to do if they are true. So it first checks to see if Grade contains p5. If it does it makes the contents of the cell p6. The Function works through the conditions in the order that they are written down.

The underscore characters tell VBA that the command continues on the next line. I added them to make the code easier to read.

Line 3 marks the end of the function.

The Visual Basic Editor can be found in the Tools menu. Click on the Tools menu, then Macro and then Visual Basic Editor. It is important to create the function in the right place. Click on the entry VBAProject (spreadsheet.xls) where spreadsheet.xls is the name of your spreadsheet. Click on the Insert menu and then on Module. Enter the code for the function and click on the save button. Return to Excel to test the function. There is a button with an Excel icon in the toolbar for this.

I hope that gives a taster of some of the useful things you can do with even only a little coding knowledge. Let us know if you have any experiences, advice or questions using the comments box below.