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 ‘New Data’ file.

Click here to download the ‘Old Data’ file.

Click here to download the ‘Product Data’ file.

Excel VBA Example Picture

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!

Part 3 – A Sophisticated File Validation Mechanism

In video 3 of the series, we look at how to create a sophisticated validation mechanism to check the right files are open – and no others! We use VBA to check through the open files and ensure each (that is ‘supposed’ to be open) is open in order to complete the task. This involves some advanced coding: we have to implement an IF statement with multiple conditions, for example, and use another dynamic message box to inform the user of what is wrong should there be a problem. Remember – sophisticated user communication is a great way to make you stand out as a programmer.

The structure we set up is what Chris uses on all Excel VBA projects involving multiple files. These projects can be complicated; one way to manage the complexity is to ensure that the right files are open (and closed!) This should keep the stress levels down and allow us to focus on creating value for the client rather than ‘getting things to work’.

Part 4 – Time To Tidy Up!

In the fourth installment in the series, we deal with the non-glamorous but essential job of ‘tidying up’. Yes, alongside planning and a bundle of other skills, tidying up is one of the key competencies that nobody really talks about – but which determine your effectiveness as a coder. Don’t worry, you can learn all of these skills, either implicitly or explicitly, on the Tiger channel!

The key idea is that, by keeping stress levels down, coding sessions can be longer and more productive. Yes, we need to stay below the critical point where you want to throw the laptop out of the office window (we’ve all been there!) To do that, we need to be proactive. Let’s do ourselves a favour and take the chance to ‘tidy up’ right now.

In the video, Chris first shows how to use With … End With to economise on code; fewer characters to read means lower cognitive load and lower stress levels, so integrating With … End With represents a quick win for us. Just remember to add the full stops (.) where necessary.

Then we look at re-arranging the annotations (remember, the ones we created in the first video of the series?) so that each aligns with the correct line of code. Being able to understand what the code is doing in your native language is a real bonus for the coder – so let’s take the time now to get those annotations up-to-date.

A small test confirms everything is still working as required.

See you in the next video when we will tackle some more exciting coding ideas…

——————————————

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 Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

This entry was posted in Advanced Articles, Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.