Download the course handbook in Excel or PDF format here.

The handbook includes a learning template for each session.  Use it to record what you learned, what you found difficult (return to it later?) and your punch-the-air moments.

In this session, we learn how to set up your system for Excel VBA, how to write a macro and how to tweak it for better results.

Download the course handbook to access the learning template for this session.

What is Excel?  There's a tricky question!  Well, an Excel file, at the highest level of granularity, consists of cells.  Our ability to manipulate those cells - and the relationships between them - is a key determinant of our Excel (VBA) capability.  So, in part 2 of the Excel VBA Beginner Fundamentals series, let's learn how to work with cells using Excel VBA.

First, we learn how to 'read' a cell in the spreadsheet from the VBA Editor.  With our old friend the message box (aka 'msgbox'), we show how VBA can recognise the value in a cell with a really simple line of code.  Next, we work the other way by 'writing' a value to the spreadsheet from the VBA Editor - yes, it's a two-way street!  Finally, we explore how to write a value from one cell to another using instructions in the VBA Editor.  Make sure you do your own experimentation to consolidate these key ideas ...

If you’re a beginner in Excel VBA, you may be wondering: What’s An Excel VBA Variable?

Excel, like computer programming more generally, is full of terms that are difficult to understand.  I’ve often said, for example, that the OFFSET formula should be renamed ‘MOVE AWAY FROM’.  So, what about a ‘variable’?  Does it ‘vary’ in some way?  Well, kind of.  But, as you might expect, its function cannot be easily understood from its name.  It’s a shame because variables in Excel VBA are one of the building blocks of powerful Excel applications.

I’m offering you a simple definition today: a variable is a place to store information.  In this way, it can be compared to a cell in the spreadsheet – we are very used to inputting data there!  So, why do you need variables?  Well, unlike cells, variables live in VBA and are only ‘seen’ if we specifically ask to externlise their value for some reason (for example, by using a message box, as we do in the video.)  This means they can be more efficient and easier to control - variables drive all the ‘slick’ Excel VBA applications I have developed over the years.

But, you’ve got plenty of time to get to the more powerful applications later.  In this video, I take you through variables from an absolute beginner perspective.  We explore the concept of a variable (look out for the ‘mic pack’!), create three variables in Excel VBA, then pass data to them and use them to perform a simple operation.  In the final step, we see how variables can ‘read’ and ‘write’ data to the spreadsheet.  In this sense, they facilitate a thing of beauty and a feature of many powerful Excel applications: interaction between the spreadsheet and the VBA editor.

Loops in Excel VBA are, quite simply, the best way of taking your coding to the next level.  They allow us to do something extraordinarily powerful: repeat a command any number of times.  They’re not easy to programme, however, and you’re likely to get stuck in an interminable loop at least once - it happens!  So, how should a beginner learn loops in Excel VBA?

Forget about Excel for a second and let’s deal with the concept first.  Take this completely implausible, fictional situation: I have a biscuit tin on my desk that I use to store used batteries (content creation involves many, many AA batteries!)  I intend, one day, to recycle these batteries which is why I keep them in the biscuit tin.

Suppose I need to mark these batteries as ‘used’ (I often confuse used with non-used batteries.)  I have a permanent marker for the purpose and instruct YOU to do the job (suppose you are the Tiger Intern for a day – a terrifying prospect, I know!)  I request via email that you undertake the task.  The email reads as follows:

Hi Tiger Intern,

Here’s a job for you

Write ‘USED’ on battery 1

Write ‘USED’ on battery 2

Write ‘USED’ on battery 3

…

Write ‘USED’ on battery 25

Thanks,

Chris

What’s wrong with this email?  Well, it’s completely unnatural.  I’ve communicated the task in a verbose (and quite frankly ridiculous) manner.

But, here’s a thought: are currently going about your coding in this inefficient way?

How would you actually ask somebody to do the above?  Rather than referring to each battery individually, you would simply ask:

Write ‘USED’ on each battery in the biscuit tin.

Let’s quickly analyse the architecture of this sentence, because it unlocks understanding of loops in Excel VBA.

There’s a containing whole, what Excel would term a ‘collection’ of objects that are essentially the same (ie. the batteries).  So, we have the concept of a collection of objects.  And, we wish to do something to each object in the collection.  There it is!  This is the language we use to structure loops for beginners n Excel VBA:

‘For Each Object In A Collection’

So, by harnessing Excel’s language and understanding, at least at a basic level, how Excel organizes things into collections of objects, we can begin to access the power of loops.

In the video, I show how to use the above construct to do a simple task in Excel: display the names of all worksheets in the file.   Hopefully, you’ll punch the air when it works!  We’ll also look at other easy examples of how to use this basic idea to get you using loops in Excel VBA.

In this website-exclusive video, we explore another powerful looping technique.  By combining this technique with the Offset method to achieve position control, we create a list of the worksheet names in the file.  This session can act as a bridge between session 4 (loops introduction) and session 5 (position control with Offset).

Yes!  I’ve been looking forward to this one – a beginner Excel VBA tutorial on my number one Excel VBA technique.  Welcome back to the Excel VBA Beginner Building Blocks series, you can download the series resources and access secret videos using the link in the video description.

So, what about this ‘number one’ VBA technique?  It’s my number one technique firstly because, on its own, it’s pretty cool.  But there’s more than that: it binds together other powerful VBA techniques (variables, loops etc.) to create beauty in Excel.  This is why, for me, this technique is responsible for more ‘punch-the-air’ moments than any other, over the years.  Let’s create one for you today!

To understand the power of the technique, we first have to appreciate the concept of ‘position’ and its importance in Excel.  Like in chess, command of a grid in Excel gives us ‘control’.  VBA’s Offset method makes this all happen!  By allowing us to reference a starting point, and ‘move away’ or ‘offset’ from there by (1) a number of rows and (2) a number of columns, we can get exactly where we need to go on the spreadsheet.  Cool!

So, how does it work?  In the video, I introduce you to the Offset method with a simple example.  Remember to stop the video here and accept my challenge: can you see what’s going to happen when we run the code?  If you’ve got the basic idea, take the time to ‘play’ with the basic construct and build your understanding and confidence.  Yes, at Tiger, we know that code is play!

Now, a steep rise in the difficulty level, are you ready?  I mentioned at the beginning that Offset brings together powerful techniques in Excel VBA.  We integrate them fairly quickly in the video – so make sure you have watched the previous videos in the Excel VBA Beginner Building Blocks series on variables and loops in Excel VBA.  Oh, you haven’t seen the For – Next Loop video?  It’s a secret one on the website – follow the link in the video description to access it.

Building the construct step-by-step allows us to create a list of numbers with just a few lines of code.  As I demonstrate in the video, it’s easy to ‘scale up’ this example to any length of list – such is the power of this mechanism.  I love the combination of the three techniques and how integrating the variable into the code makes the magic happen.  If you get it working, make sure you punch the air!  Then take the time to develop this example and master the VBA Offset method yourself.

See you in the final video of the Excel VBA Beginner Building Blocks series!

Conditional statements allow us to ‘send’ Excel in one of two (or more) directions when running VBA code.  They work through a ‘condition’ or a logical statement – is something happening in the workbook?  For example, is the value in cell A1 more than 10?  If the ‘condition’ or logical statement is met, then Excel jumps to one place in the code; if not, it jumps to another.  In this way, Excel diverts the code in one of two directions.  Cool!

Conditional statements feature in most powerful computer programmes.  I love learning (and teaching) conditional statements because you only need 3 structures to do everything you would need to do involving conditionality in Excel VBA.  They build nicely from a very simple to sophisticated.  So, let’s look at the first one.

1.       ONE-LINE CONDITIONAL STATEMENT USING IF

The first building block is a one-line conditional statement using IF.  It’s simple but, applied correctly, is often enough to get the job done.  In literal terms, it tells Excel to do something if something is happening.  There are a few syntax rules to observe here – the most important of which is the whole statement must be on a single line.  That means both the condition - and what to do if the condition is met.

Follow along with the video and build and test the one-line conditional statement with me.

2.       IF – ELSE – END IF

The second building block for conditional statements in Excel VBA is more powerful but significantly more difficult to put together.  IF – ELSE – END IF gives us two options: what to do if the condition is met, and what to do if it isn’t.  Once again – syntax is important.  The main difference compared to the one-line conditional statement is the instructions are now placed on a different line to the condition.  This can confuse and has cost me hours of time over the years!  Once you’ve practiced the basic IF – ELSE – END IF structure, however, you’ll be harnessing its power for years to come.  Follow along with the video as I build, test and debug it. 

A sidenote here: you can build on this structure using ELSE IF to introduced more than one mutually exclusive option.  I find this syntax too complicated, however, which is why I favour our final building block …

3.       SELECT CASE

So, we’ve looked at options to divert Excel in one direction if a condition is met, and in one of two directions.  But, what if you needed to programme 3 options, or more?

This is where SELECT CASE comes in.  Yes, the language is different which certainly takes some getting used to.  But, it’s so worth it in the end because SELECT CASE gives us the flexibility to programme any kind of conditionality logic using Excel VBA.  It’s one of my all-time favourites!

First, we use SELECT CASE (object) to show Excel what to look at.  Then, we list options for the (object); Excel automatically jumps to the option that applies.  We can even introduce a CASE ELSE catch-all option to ensure all bases are covered.

Once again, follow along with the video as I create a SELECT CASE statement with Excel VBA.  If the syntax is too much for the moment, that’s fine: simply watch the video and try to appreciate the power of SELECT CASE – you’ll be using it in no time!

So there we have it, the three structures I use to programme conditionality with Excel VBA.  If you’re steady and systematic and build from one idea to the next, I’m absolutely sure you’ll be able to integrate these powerful mechanisms into your Excel VBA vocabulary, adding flexibility and power to your VBA applications.

This video (duration 2 hours 15 minutes) features the building of a 'real-world' VBA tool from scratch.  Will you be able to understand all the coding?  Probably not, but that's not the point.  The real point is to try to pick up on the 'metaskills' that allow Chris to create powerful VBA applications in the just a couple of hours.  What are these 'metaskills'?  Chris explains more in the video.  Enjoy!  Oh, and don't forget to download the files and work along with Chris ...