Welcome to the Excel VBA Real Example Series - season 2. If you are interested in using Excel VBA to speed up manual work, this series is for you. Download file link:
Series playlist link:
Real World VBA Task Season 1 playlist:
In the Excel VBA Real Example Series, we take a file from a viewer of the channel and demonstrate how to use Excel VBA to get an annoying manual task done 'at the click of a button'. Yes, with Excel VBA you can get those jobs (that you are spending minutes, hours or days completing) done at in just a few seconds, such is the power of Excel VBA. So, let's get into it!
Thank you to channel viewer Chahin for sending in this example. We've selected it because it's typical of so many examples of Excel-based work that we have seen over the years.
We have an old and a new data file, and the question is simple: which entries appear in the old file, which entries appear in the new, and which entries appear in both? Then, the entries should be checked against a product data file for any inaccuracies in the record. It's typical of manual work in Excel that can be frustrating. We're using a small dataset for learning purposes, but this kind of task would be a nightmare with a larger dataset, so let's learn how to use Excel VBA to get it done.
-----------------------------------------------------
In part 1, we discuss the task at hand, and do the all-important planning and conceptualisation. This may not sound very exciting and many people miss out this stage altogether in their programming; to do so often creates problems later. We argue that conceptual rather technical issues account for most of the problems people experience with VBA code.
Do you get the concept? Can you explain in plain English (or your own language) how the code should work? If you cannot, you are likely to run into problems later.
In the first video, therefore, we make sure the concepts are clear by using 'pseudo-code' (descriptive comments in the VBA editor) to work through the task 'step-by-step, making notes as we go. This lays an important foundation: when we begin coding (which we promise we will start in the next video!) these comments (or 'annotations') act as an important guide, saving mental bandwith for the technical component - in other words - the actual VBA coding.
Make sure you download the three files and work along with Chris - can you get a clear idea in your head how this routine is likely to work? What are the key features - loops, conditional statements etc.? Try applying this deliberate planning approach to your day-to-day VBA coding - we are confident you will see improvements, and feel lower stress levels!
Are you following season 2 of the Real Example VBA series? We would love to hear from you, and Chris answer all comments personally, so leave us your questions below in the comments.
---------------------------------------------
**FREE** Your Complete Guide to Excel Spreadsheet Projects
50-Minute (non-public) Video and PDF
When you sign up to the Tiger mailing list:
Daily video on Instagram
Sister Channel: Excel Spreadsheet Skunkworks (Livestream Only!)
0 Comments