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.

 

 

 

CLICK HERE TO WATCH THE VIDEO

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!

 

ABOUT THE EXCEL VBA BUILDING BLOCKS COURSE

 

 

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.