If you’re interested in learning Excel VBA, you might be happy to know the basics of the language are relatively straightforward. At Tiger, we talk about five building blocks for automation with Excel VBA. In today’s session, we’ll demonstrate each of these building blocks through a simple automation exercise based on my experience of what professionals are trying to do in the real world. Welcome back to 30 Day Excel Analyst!

DOWNLOAD THE RESOURCES HERE

Watch The Video HERE

So, what are the five building blocks of Excel VBA? The first is interaction with cells in the spreadsheet. We need to be able to write to the spreadsheet, and read information from it. You might be familiar with syntax such as Range(“A1”) = “Tiger”. This is an example of interacting with cells in Excel with VBA. I also recommend the ‘dot cells’ method: Cells(1,1) = “Tiger”. This is our first building block.

The second is using variables. A variable is an abstract idea and, like many things in Excel and computer programming more generally, is not helpfully named! I like to think of a variable as a place to store information. To that extent, it can be compared to a cell in the spreadsheet – which also stores information. It’s just that variables only exist in Excel’s memory and are less easily to visualise.

The third is to be able to control position. This involves techniques such as the Offset method in Excel VBA (a real Tiger favourite!) and even ‘dot cells’. Position control refers to how well we can navigate the grids, worksheets and files that Excel gives us. Dynamic position control is the next level and concerns working with datasets dynamically – since, as we know, data is constantly changing.

The fourth is loops. Loops allow us to repeat an instruction any number of times and, as such, are the key to powerful automation in Excel. Want to do something to each sheet in a file, each cell in a range, or each chart on a sheet? You’ll need some kind of loop. The For-Each loop that does something to each ‘object’ in a ‘collection’ is the best starting point.

The fifth is conditionality. A conditional statement in Excel VBA sends the code one of two or more ways depending on a ‘condition’ – whether something is happening in the spreadsheet or not. Syntax ranges from a simple ‘one-line’ If statement to more complex logic using If – Then – Else and Select Case. Conditionality adds another layer of sophistication and power to your VBA coding.

In today’s session, we’ll at least touch on all five of the building blocks. Remember, computer coding is a skill and you have to practise to improve … and reach those sweet ‘punch-the-air’ moments. So, make sure you download the Excel files and work along with me. Then, let me know how you got on in the comments - I would love to hear what you think about the 30 Day Excel Analyst programme.

READ MORE ABOUT OUR PROFESSIONAL EXCEL DEVELOPMENT COMMUNITY: MEMBERS MONDAY