How To Use Excel VBA Code – Real Example

How To Use Excel VBA Code To Speed Up Manual Work – Real-World Example

Click here to download the STARTER Excel file for this series.

How can we use Excel VBA code to speed up manual work in Excel? In this series we explore an example Excel VBA project sent in by Eric, a viewer of the channel. We aim to build an Excel VBA application to move data around a file at the ‘click-of-a-button’, saving hours of frustrating manual work. The application should be flexible and scaleable enough to handle changes in business circumstances – a challenge indeed! Let’s get into it…

How To Use Excel VBA Thumb

Part 1 – Planning and Looping Through Worksheets

In part 1, we consider the task at hand and complete a simple task that moves us in the right direction. This may seem to lack ambition, but it is important to plan any VBA task and to move forward in easy steps; if you simply jump into the VBA editor, you are likely to get into trouble. We have all been in a coding tangle before!

We know we have to take data from four sheets in the file. So, a sensible first step would be to set up a mechanism to loop through sheets in the file. It’s simple enough, and it sets us off in the right direction, and allows us (and our client?) to build trust and confidence. In the video, Chris uses a particular sheet referencing technique (using sheet index numbers) that allows us to quickly exclude sheets from the loop – since some of the sheets in the file do not contain data. We also look at how to set up the loop to cater for additional sheets in the file – remember our solution has to be flexible and scaleable.

With this relatively simple task complete, we are on our way! See you in part 2. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 2 – Dynamic Range Referencing and With … End with

In part 2, we create a mechanism to loop through the data on each worksheet. Looping through ‘static’ data is simple enough – we have done that in other videos on the channel – but Eric’s briefing presents an additional challenge: how to loop through a variable amount of data?

What is meant by ‘variable’ in this case? As you can see in the download file, each sheet contains a different number of rows; further, Eric wishes to be able to add new sheets with any number of rows. What is required is a solution that can handle these different situations without the need for additional coding; what is needed is a *dynamic* solution.

The video explores how to create such a solution. We learn about the powerful .end(xldown) construct which selects to the end of a dataset and drives the dynamic mechanism. We use the ‘step into’ facility in the VBA editor to work through the code whilst looking at the Excel window to understand how it works, and to test the new mechanism.

Finally, we look at how to integrate With …. End With to simplify the code and to avoid having to select sheets. Selecting objects such as sheets and cells is a ‘direct referencing’ approach; routines built in this way can take some time to execute, and there is a more efficient approach.

We would much rather have a fast routine built on ‘remote’ referencing; ie. a routine that does not select sheets but uses sophisticated referencing to get the job done faster.

Part 3 – Loop within Loop and Countif

In the third video, we first implement a ‘loop within a loop’ – the powerful mechanism that allows us to get the first part of this job done. Conceptual thinking always comes first, so what exactly are we trying to do, and can we explain it clearly in normal language? Let’s try…

We already have one loop set up which loops through the worksheets in the file. The next loop, which operates ‘one level down’ and is embedded within the first, will loop through the name cells on each sheet. With the conceptual understanding clear, we can move onto the programming.

Chris uses a ‘for each object in the collection’ / ‘for – each’ loop to work through the name cells on each sheet. This type of loop could be applied to open workbooks, shapes on a sheet, sheets in a file, or another of Excel’s ‘collections’ of objects. It shows how, if we can navigate Excel’s object hierarchy (simply the ‘architecture’ of Excel), we can make coding easier.

In this case we loop through each cell in the range of cells that contains customer names, as defined by the dynamic range reference we created in the previous video. We test the mechanism using message boxes to extract information from the VBA editor during code execution. Job done – next!

The next step is to understand if a particular name appears in the list of names we are assembling. Remember, Eric has requested a list of ‘unique’ names, so names must not be duplicated. So, how can we check if a name already appears in the list? We set up a thing of beauty: worksheet formulae working together with Excel VBA to get a job done. In this case we create a ‘COUNTIF’ formula to establish if a value appears in a range; if the formula returns a value of zero, we know the name needs to be added to the list.

Onwards!

See you in part 4. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 4 – Importance of Frequent Testing and a Conditional Statement

In part 4, we discuss the importance of frequent testing of computer code, and test what we have done so far to build confidence in its accuracy and robustness. It might appear that Chris is obsessive about testing … and you might have a point! But, this mindset is the result of hours spent working with Excel VBA.

The truth is that frequent, small-scale testing eliminates the need for large-scale, stress-inducing testing later in the process. Testing should imbue the development process, rather than being bolted on at the end. We again use a message box and the ‘step into’ facility to work through the code and understand what is going on. Don’t forget to view the VBA editor and Excel worksheet side-by-side, if possible; an additional screen can really help if you have access to one.

With the testing done, it’s time to transfer data from the four worksheets into the mechanism on the Analysis sheet. This is fairly routine (only because we have already done the hard work!) but we wish to do more; we wish to tell Excel to do something if the name appears in the list we are compiling. We wish to tell Excel to do something if a condition is met.

With this in mind, we implement a simple conditional statement, followed by an If-Then-Else statement to direct the code in one of two directions. With this mechanism in place, we are almost ready to assembly our list of unique entries … all at the click of a button.

See you in part 5. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 5 – Combining A Worksheet Formula with VBA Code

Some more (yes, more!) testing is required at the beginning of part 5 to ensure the file is working as we expect. With a quick test completed, we set about creating a mechanism to ‘stack up’ entries in a list. This mechanism moves us towards our initial goal of a list of unique entries in the file.

Regular viewers of the channel will be familiar with the components: first, we use COUNTA to count the number of entries currently in the dataset. Then, we add the .offset method in the VBA editor; .offset allows us to move away from an ‘anchor point’ – the top-left corner of the dataset – by a certain number of rows and columns (anchor.offset(x,y).

By pointing Excel to the cell in the workbook that contains the COUNTA formula, we can make magic happen: a powerful interaction between .offset and COUNTA creates dynamic position control which allows us to create a list of unique entries at the click-of-a-button. A powerful mechanism indeed – check it out in the video!

As the thrill of this instant functionality subsides, we set about testing the accuracy of the new mechanism. Chris implements three (yes, three!) different tests to establish its accuracy, including some manual checking and an IF formula to check our dataset against a pre-prepared one. Only then are we ready to move onto the next step.

Hopefully you are understanding the importance of a methodical approach in Excel VBA coding, characterised by small steps and almost constant testing. This is the approach Chris applies in his actual Excel VBA development work – we hope it helps you shape your own practice.

See you in part 6. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

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, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.