Beginners Tutorial: Visual Basic for Applications
Visual Basic for Applications, or VBA for short, is the programming language of Microsoft Office – and it’s something I’ve been using extensively over the past year or so for my day job. If you had asked me several years back to write anything in VBA, I’d have probably laughed at you and said “why on Earth would I do something like that?” After all, before my job started to get me to do some more interesting development based work, I had little to no reason to make anything more than a function beautiful Excel spreadsheet. Whilst it functioned wonderfully, it didn’t really satisfy me.
From creating full blown login menus, to locking down a whole spreadsheet, VBA has the potential to change your Excel spreadsheet to be much more than just a spreadsheet. Do you want to create a spreadsheet that calculates based upon what button you press? Perhaps you want to create an amazingly interactive sheet which will allow you to click on a picture and go to another, hidden sheet, which is fully locked down. Whilst VBA does indeed have its flaws (and indeed security risks along with it, ) if you’re creating something that needs to be greater than your typical Microsoft Office document, then you may want to consider dabbling in VBA a little bit.
VBA is an implementation of Visual Basic which is embedded in all of the Microsoft Office suite. It’s able to do some incredibly complex things, but there are security issues when you give such power to anyone. Since Microsoft Office can be installed on any computer, coupled with the fact you can easily share an Office document with anyone in the world, you can only imagine how many malicious applications there are. With this said however, it’s not the end of the world. Microsoft are smart enough to make it so people are able to not allow macros. However, once enabled, that’s when the real power of VBA comes out.
In this tutorial, we’ll look at how you can use VBA within Microsoft Excel and how functions work within it. To access the VBA environment for your Excel sheet, simply press Alt + F11.
Depending on what you’re trying to achieve, you create subs and private subs. A sub is something that can be assigned to anything, such as a button, or a specific cell. Meanwhile, a private sub can’t be called by a button, but it can be called within other subs. This is simply done like the example to the right. If you wanted to create a button that returned the result of two cells, you would write the following code (where A1 and B1 and the two cells you are trying to add together and C1 is the cell you are updating).
Worksheet(“Sheet1”).Cells(1, “C”).Value = Worksheet(“Sheet1”).Cells(1, “A”).Value + Worksheet(“Sheet1”).Cells(1, “B”).Value
Once you know how to declare a sub, you are then able to make just about anything happen. You might have noticed the above code seems rather long winded, especially how in Microsoft Excel you are simply able to click on cell C1, type =A1+B1 to do the exact same thing as the above. Sometimes however, you don’t want things to be done automatically by functions. You want things to be calculated when someone clicks on an object, such as a button. This is why you declare subs – and subs are then able to call private subs, allowing more functionality, based upon what you put in to the application.
One of the many useful things you can do with VBA is hiding rows and columns. The next example to the right is a simple case of hiding a row when the user clicks on a button that has the hideRow() function applied to it.
.EntireRow.Hidden = True
The point I’m trying to make is that there’s a lot more to spreadsheets than initially meets the eye. The next time you use an Excel spreadsheet, why not consider any ways you can make it better with code. Now it’s over to you – What do you think about VBA? Do you think it’s still got a lot of life left in it? Do you think it’s a useful supplement, or just a hindrance? As always, leave us a comment below, or over on Facebook, Twitter or Reddit