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!

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…

Part 5 – A Modular Approach

In recent years, I have experimented with a more ‘modular’ approach in my Excel VBA programming, getting good results. A more modular approach can mean easier-to-understand code and a lower risk of crashes – but how to actually realise it in practice? Let’s check it out in this video…

It seems we’ve reached the end of the first ‘phase’ of this task – checking the open files are present, open, and correctly named. We could call this phase ‘validation’ and make it ‘distinct’ or discrete by separating it from the main programming. We could then trigger this first phase from a separate button (as we will later in the series) to create a sense of a ‘pre-departure check’. Yes, this phase does not do much in terms of analysis; but it does set things up for us, and a phase completed successfully builds user confidence.

This approach requires a ‘module-level’ variable. These variables, declared at the top of the module, are not cleared after a routine executes; they continue to store their values and can be used to pass information between routines within the same module – cool! In the video, we create a module level variable, test it, and then use it to check a condition before triggering another routine – the modular approach in action.

As always, we include lots of testing as we go along. Our classic ‘flash up a message box’ approach comes in handy more than once; at the same time, we take the opportunity to improve a message box by changing its title to something that directs the user more effectively. Remember – user communication is everything!

So, are you a fan of the modular approach? Perhaps you can’t feel the benefits fully yet, but Chris does mention some in the video. As we work through the task, the approach will take shape and hopefully you’ll be able to feel its full power.

See you in the next video in the Excel VBA Real World Example series – season 2! Don’t forget to leave Chris a comment below (he answers all comments personally.)

Part 6 – Step-By-Step

In the previous video we explored how to apply a modular approach in this implementation. In video 6 in the series, we first add buttons to the interface, to allow us to exploit the modularity of the code, and to allow the user to ‘work through’ the process step-by-step. It takes a little more time to implement a modular approach, but we will soon begin to feel the benefits: modulated code is easier to debug (it’s bound to go wrong at some point!)

Moreover, it allows your client to gradually build confidence in the application, and in you as a programmer. No more clicking the button then closing your eyes and hoping everything works!

Let’s not forget more practical matters. In the video, Chris shows how to create the buttons, align them with the spreadsheet gridlines, assign macros to them and change the text that displays on the button. Don’t forget to use the Alt key to make sure those buttons are aligned! (Chris’s bugbear.)
With our button in place, let’s take our communication with the user to the next level by including text on the interface to let them know when the file was last validated. We use some cool VBA syntax to get this done – NOW to give the current date and time, and FORMAT to display the text exactly as we require.

The step-by-step approach we apply in this series may seem unnecessarily time-consuming (Chris calls it ‘ponderous’ in the video.) Hopefully you are feeling some of the benefits already, however, and more will come apparent as we work through the series. Years of working on real-world projects has taught Chris this really is the best way!

See you in the next video and don’t forget to leave a comment on YouTube, Chris will get back to you.

Part 7 – A Remote Reference To Another File

We begin by testing the validation mechanism created in the previous video and refreshing our minds about how the module level variable works. Ultimately, we want the customer to be able to click through a few buttons to get this task done. Yes – it’s more ‘click-of-a-few-buttons’ than ‘click-of-a-button’ but we are arguing that the ‘modular’ approach delivers benefits for the user (by building confidence in the process) and the programmer by making the code easier to debug.

This takes us to the next phase of the task. So, what to do? Here, the time we took to talk through the task at the beginning (see video 1 in the series) and create pseudo code (the green annotations or comments) pays dividends. We can simply read through the annotations to find what to do next. No shifting between conceptual thinking and coding. Cool!

One thing we will have to do is remotely reference the other open files. ‘Remotely’ (as opposed to ‘directly) means referencing the files without activating them. It involves more sophisticated coding but results in far more efficient execution.

In the video, Chris talks about the value of the ‘ThisWorkbook’ construct – meaning ‘the workbook that contains this code’. It comes in useful when working with multiple open workbooks. As with multiple sheets, we have to make sure that Excel understands the object we are talking about when working with multiple workbooks. So, let’s try the syntax and test it using Msgbox, as usual.

In the video, Chris also shows how to integrate the With … End With construct to economise on code and improve efficiency. Are you using it in your coding practice?

We end the video by remotely referencing a cell in another open file to flash up the contents of a cell in a message box. It’s proof that our remote and modular coding approach works, at least for this simple example. In the next video, we’ll look at scaling up this approach to begin to create some insights for our client.

See you in the next video and don’t forget to leave a comment on YouTube, Chris will get back to you.

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

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.