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? 

 

DOWNLOAD COURSE HANDBOOK / EXCEL FILES 

 

 
CLICK HERE TO WATCH THE VIDEO

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. 

 

Excel VBA Beginner Fundamentals Playlist 

 

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. 

 

ABOUT THE EXCEL VBA BUILDING BLOCKS COURSE 

 

DOWNLOAD COURSE HANDBOOK / EXCEL FILES 

 

Learn the fundamentals of Excel VBA in around an hour with Tiger's FREE Excel VBA Building Blocks course.  The course is based on Chris' 10 years of Excel training, consultancy and content creation, and teaches the basics of Excel VBA around 18 simple ideas or 'building blocks'.  In addition to the 6 basic videos on Excel VBA, on the website you can find 3 'metaskill' videos (to be uploaded through Summer 2021) about next level concepts that could unlock your potential with Excel VBA.  

 

A course handbook is provided for free that lists each of the Excel VBA beginner building blocks and includes a learning template for each session - make sure you download and use it to support your learning. 

 

Use the link below to download the course handbook and access the learning template for this session.