Even Beginners Can Learn Loops In Excel VBA ... (MM LITE #4)
Even Beginners Can Learn Loops In Excel VBA ... (MM LITE #4)
Free Excel (VBA) Tutorials
Become An Excel Analyst In 30 Days (COMPLETE COURSE!)
Become An Excel Analyst In 30 Days (COMPLETE COURSE!)
Articles topics
Articles topics
Even Beginners Can Learn Loops In Excel VBA ... (MM LITE #4)
It’s difficult to convey the power of loops in Excel VBA – I’ve tried it many times over the years … with mixed success!
The term ‘order-of-magnitude’ applies since loops are an ‘order-of-magnitude’ more powerful than any other technique in Excel VBA. But, that sounds a bit academic.
FREE NON-PUBLIC ONE-HOUR EXCEL VBA TUTORIAL
00:00 ▶ INTRODUCTION
00:44 ▶ Use Excel VBA To Loop Through Sheets In A File
04:53 ▶ Use Excel VBA To Loop Through Cells In A Range
08:10 ▶ Use Excel VBA To Loop Through Open Files
So, think of it this way: suppose you go about your daily business on a bike – cycle to work, to the shops, to see friends. Well, if you add loops to your Excel VBA vocabulary, it’s like upgrading to a car. Actually, it’s like upgrading to a spaceship. That’s because loops in Excel VBA allow you to get so much done, they open up a new world of possibilities for your coding.
The best thing: even beginners can get started with loops in Excel VBA TODAY. So, grab the keys for your spaceship and let’s get to it!
The best way for beginners to understand loops in Excel VBA is with a For-Each loop. Now, Excel has three main loop types but let’s not worry about the others because we don’t need those for now. Since it uses Excel architecture that you’re already familiar with, a For-Each loop is the best place to start.
A brief (I promise!) lesson on that architecture.
“Excel consists of objects organised into collections”
Objects are the components of a spreadsheet application – cells, sheets, buttons, charts … even whole workbooks are treated as ‘objects’. Excel organizes objects into collections: open workbooks, for example, are a ‘collection’. It’s like a basket of things that are essentially the same but with different ‘properties’ or attributes. My fruit bowl contains apples of different size, colour and age (one looks particularly old), for example.
It’s important to understand this because, using the power of loops, we can say to Excel ‘do something to everything in this collection’. It’s a bit like saying, ‘Throw away all the apples’; or even ‘throw away any apples that are older than 1 month’. This is the concept.
In the video, I use three collections in Excel to demonstrate the power of For-Each loops: sheets in a workbook, cells in a range and workbooks that are currently open. If you’ve not worked with loops before, the syntax can be tricky, I find the need to declare a variable to manage a For-Each loop particularly counter-intuitive. The good news, however, is the same formulation applies whichever collection you’re working with.
“For Each Object In A Collection”
So, download the download file and work along with me to create some For-Each loops using Excel VBA. Once you’ve got the idea, make sure you experiment by trying some other collections, or by adapting these examples to manipulate object properties (eg. Worksheet names) in different ways.
Yes – it’s critical you do your own practice after watching the video. Remember – Excel is play!
Have you been able to add For-Each loops to your Excel VBA arsenal? I would love to hear in the YouTube comments how you got on.
FREE NON-PUBLIC ONE-HOUR EXCEL VBA TUTORIAL