How To Build A Loop In Excel VBA (Real-World VBA Task S3 P2)

How To Build A Loop In Excel VBA (Real-World VBA Task S3 P2)



What’s the most powerful construct in Excel VBA?  It’s a great question.  For me, however, the answer is clear, and it’s a technique many Excel VBA beginners avoid early in their career.  Yes, it’s time to talk about loops!

The concept of a loop is simple but mind-blowingly powerful: a loop allows us to repeat an instruction any number of times. Power is nothing without control, however, and the truth is loops easily get out of control in Excel VBA.  If you’ve done even a moderate amount of VBA programming, you would have been struck in an interminable loop.  We’ve all been there!  So, how can we build up a loop in Excel VBA, step-by-step, minimizing stress and maximizing the impact of the technique?  Welcome to video 2 of the Excel VBA Real-World task series.


In the video, I show you how to build what is, in my view, the most useful looping technique in Excel VBA - a For Each loop.  It’s certainly a great starting point for beginners.  The concept is simple, we tell Excel to do something to each cell in a given range, which allows us to work through each of the weekend’s fixtures in order to do some kind of analysis.  A steady and systematic approach, testing at each step, is crucial here.  Make sure you download the file and work along with me – and don’t forget to do your own testing so you are 100% convinced the loop is working as it should.

So, did you get the loop working and are you enjoying season 3 of the Excel VBA Real-World Task series?  Let me know in the YouTube comments and see you in the third video in the series.

ABOUT OUR MEMBER COMMUNITIES

Our learning communities provide exclusive Excel learning content, direct access to Chris, support and inspiration from others just like you and much, much more.  They are the best way to develop your Excel skills long-term, and to make a few friends along the way!  Check out the options below, we would LOVE to welcome you ...

ABOUT REAL-WORLD VBA TASK SEASON 3

In the Real-World VBA Task series, we tackle a VBA challenge similar to the jobs that professionals you like are trying to automate daily using Excel VBA.

In Season 3 of the Excel VBA Real-World Task series, we explore a job I’ve been tasked with many times in my career: creating form analysis for football teams in a league table.  Form analysis the kind of thing you’ll see alongside football league tables in the newspaper or online, and it’s the perfect topic for this series for two reasons.  First, it’s a task that’s not easily done in Excel without using VBA (though it’s possible using a series of very long formulae).   Second, it requires powerful techniques such as loops, conditional statements, position control and more – so it’s broadly applicable to VBA tasks generally.  In other words, the series should help whatever VBA job you’re currently wrestling with.

In this 7-part season, I walk you through the VBA task step-by-step, from the critical planning and conceptualization phase, to application of loops, variables and more in VBA, through to combining techniques together to create a powerful ‘click-of-button’ solution.  Even I was surprised by how quickly this macro gets the job done …

It’s about more than impressing you with individual techniques such as loops in Excel VBA, however.  It’s about learning an overall approach to VBA development that you can apply in your work.  Look out for the Excel ‘Metaskills’ such as debugging that I showcase during the series – they really are the hidden gems in Excel learning.  I hope enjoy the series and do let me know in the YouTube comments how you get on.