How To Use Excel VBA Code – Real Example S2
How To Use Excel VBA Code To Speed Up Complex Manual Work
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.
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!
Click here to download the Excel files for this series.
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.
Part 1 – Planning and Conceptualisation
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!
Part 2 – Interacting With Multiple Files With Excel VBA
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!