How To Use Excel VBA Code Real Example S2 P2

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.


🎫Get Your Channel Membership HERE🎫


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 video 2 of the Excel VBA Real Example series (season 2), we look at how to interact with multiple files using Excel VBA. Things tend to get very complicated very quickly when working with multiple files. So, it's sensible to implement checks before the code runs to check that the right files (and the right number of files) are open. This *should* help us avoid complete chaos later!

We look at how to do this by harnessing the Workbooks collection. First, we ask the VBA editor how many files (or objects) are in the collection; in other words, how many files are open? Then, we loop through the collection, checking against the cells in the spreadsheet and the name of the file that contains the code, to ensure the correct files are open.


Only if these conditions are met do we proceed with the coding to do the analysis on the files. At all times, we make sure the user is informed by using (dynamic) message boxes to convey important information; which file is problematic, for example? We highlight this as one way to make yourself stand out as a programmer: effective communication with the user.

Did you manage to get the code working too? See you in the next video!

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.


---------------------------------------------


🎫Get Your Channel Membership HERE🎫


🎥**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!)

Example