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.

Click here to download the COMPLETED 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.

Part 6 – Dynamic Positioning with MATCH

We managed to loop through the file and build up the name list in the previous video, a feat in itself. But, what we are doing is more sophisticated than that! We know a name may appear multiple times; each time the name appears, we have to count it in the correct column in our table. So, we have to establish: “how far down the list does an entry appear?” Only with this knowledge can we count in the correct row in the table. Got the logic? Good!

What formula tells us ‘how far along’ a row or ‘how far down’ a column a particular value appears? It’s a Tiger favourite. The MATCH formula helps us establish position in this way. Chris implements the formula in the video – make sure you work along and implement the formula yourself.

Chris also discusses a design issue – the balance between the scope of the formula and worksheet efficiency: it is possible for the formula to reference thousands of cells which allows for lots of data; but, referencing large ranges is inefficient and will eventually slow down workbook calculation. So, there is a balance to be struck.

With the row position established, we now have to think about how to use this information in the code. Chris assigns the row position to a variable to make it ‘portable’; we then deploy the variable in combination with the offset method to create a powerful and dynamic position control mechanism – cool! We step through the code to test it and correct a slight inaccuracy with the anchor point of the offset mechanism.

Next, we have to think about column position – how to ensure we mark the correct column? Remember each column represents a sheet in the workbook. We can solve this one elegantly and efficiently without additional formulae or excessive VBA code, using the existing mechanisms – can you figure out how?
Chris demonstrates in the video and, initially, chaos ensures! We regroup and intentionally ‘get it wrong’ first, as an intermediary step, then fix the code to create glorious dynamic functionality. Ahhhh….

But let’s not forget about testing. At the end of the video, Chris shows how to use the Find and Replace facility to double check the counting mechanism we created. But that is not enough – how else might we test the accuracy? We review the ‘Use Indirect’ sheet to show that a formula-based approach generates the same results. Achieving the same result in three (yes, three!) different ways gives us some peace of mind. Onwards!

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

Part 7 – Sum Values Using VBA

In part 7 of the series, we first look at ‘totalling up’ the entries, to tell us how many times they appear in the file. This is simple enough using an =SUM formula, but formulae are static and including them would detract from the dynamic quality of our solution – what if additional sheets were added to the file? The total column should ‘move across’ as sheets are added. Therefore, we opt for a VBA-based approach: this is fast, more dynamic and means no formulae ‘hanging around’ in this area of the worksheet.

After this initial discussion, we set about implementing the VBA-based approach. It involves an offset driven by the number of sheets in the file – this gives it ‘dynamic’ quality. Chris shows how to use ActiveWorkbook.Sheets.Count in combination with the offset method to get the job done. This setup should cater for additional sheets in the file. Cool!

With the position control tested, we consider how to ‘increment’ up the value in the total cell, so that it increases each time an entry is found in the file. The key concept here is ‘X = X + 1’. We apply this concept to the mechanism created previously to achieve the required effect.

Finally, we deal with the problem of ‘clearing out’ existing data from the file. We have now done this numerous times during the testing process, and it is getting onerous! Though a dynamic solution that would cater for very large datasets, would be ideal, Chris decides, for the sake of brevity, that a static solution is appropriate in this case.

We use the .clearcontents method (that retains cell formats), and create a new routine. It is sensible to ‘modulate’ code in this way (ie. create multiple, short routines) to allow us to access pieces of code from other macros. Chris compares the modulation to Ikea furniture!

An unwanted intervention from Siri completes the video 😊

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

Part 8 – The Rhythm of Excel VBA Development

Excel development has … a *rhythm*?

Yes that’s right – Excel VBA development has a rhythm and, by understanding its rhythm, we can improve our coding practice. At the beginning of the video, Chris argues that progress through any computer-related task is not ‘linear’; in other words, rather than by incremental units of work, an Excel VBA task is characterized by periods of conceptualization, planning or tidying up, interspersed with periods of heavy coding and rapid development. These different phases that demand different skills and mindsets make up the ‘rhythm’ of Excel VBA development.

We did a fair amount of coding in the previous video. So, now is a good time to step back, take stock and do some tidying up. We can think of the VBA editor as our ‘workspace’, like a potter’s wheel, carpenter’s workbench or seamstress’s sewing machine (yes, Chris thinks of computer programming as a ‘craft’, like the above). An untidy workspace means a muddled mind and more frustration, just as a tidy one can inspire creativity.

So, how can we tidy up the workspace, in our case, the VBA editor?

First, we call on a technique that helped us earlier in the series. With … end with allows us to simplify code by minimizing text; this helps our mental processing – and the computer’s! Yes, code tidied up in this way is not only easier to read, it also runs faster. Cool! We look at two opportunities to apply with … end with, integrate the syntax, and do the testing.

With the simplification done, what else can we do to tidy up? After the speedy development of the last video, we need to catch up on annotations. These in-code comments help us understand, in English, exactly what is going on. Chris goes through the code line-by-line and describes what each is doing in layman’s terms. The result: a ‘commentary’ of what the code is doing. This might help your colleague or your client understand what is going on; as Chris explains, it also helps us when we come back to the code after some time away from it. Consider these annotations essential for stress-free coding.

See you in the next video! Don’t forget to leave a comment for Chris on the YouTube channel – Chris will get back to you.

Part 9 – Sheet Naming Conventions in Excel

In part 9 of the real-world VBA series, we tackle the next part of the briefing from Eric, our ‘client’ for this project. We have to list the customers in four groups, on four different sheets, according to how many times the customer appears in the file. For example, all customers that appear on two sheets should be listed together.

This involves creating new sheets to store this information. Chris talks about the value of concise sheet names; they are helpful because shorter sheet names mean more tabs visible at the bottom of the Excel window, and more efficient navigation through the file. With this in mind, we shorten the existing sheet names, and create new sheets with concise names and a consistent naming convention – that will help us with the coding later.

Here’s a challenge: can you spot the problem the new sheet names create with the analysis we created using the INDIRECT formula, earlier in the series? Not covered in the video!

With the new sheets created, named and re-ordered, it’s time to think about how to get the right names on the right sheets! As usual, we take a step back and try to get the concept, before starting the coding.
Our idea is to loop through the names on the Analysis sheet once again, and then to move the name to the correct sheet according to the value in the ‘total’ column integrated previously in the series – we told you it would come in useful later!

With the concept clear, we set about creating the required code. We ‘concatenate’ the value in the ‘total’ column for the customer with the letter ‘L’ to give us the sheet name, and then store the name in a variable. Now, it is a small step to transfer the data to the correct sheet.

The video is completed with a small-scale test to prove that the code positions the names on the correct sheets. In the next video, we will look at how to ‘order’ or ‘stack up’ the names to create the lists specified Eric’s brief. We probably won’t use any new coding – so how about trying to do that step yourself, before Chris’s video demonstration is released? Let us know how you get on in the YouTube comments!

See you in part 10.

Part 10 – Tidying Up Again!

Welcome to part 10 of the Excel VBA real-world example series. The setting is new, but the task is the same, and our client Eric is waiting. So, let’s get into it.

We first discuss a common occurrence in Excel VBA development – the creation of a new macro interferes with the execution of existing ones, causing problems. A rigorous testing approach includes not only testing of new code but also existing functions; so, our first job in this video is to get into the VBA editor to identify the source of the problem, and to get the previous functions working again. Thank you to channel viewers BlvdMinSS and Paul for pointing out the issue in comments under the last video (why don’t you leave a comment too, Chris will get back to you!)

The first issue is simple enough: because we changed sheet names in the previous video, the INDIRECT formulae on the ‘Use Indirect’ sheet are no longer accurate. Remember, we use these formulae to confirm the accuracy of the VBA code, since these formulae to the same job as the VBA code, but in a different way. Simple amendments to the entries at the top of the sheet resolves this one, and our powerful counting formulae are back. Cool!

The next issue is to update the original routine to make it work with the new setup. In the previous video, we added new sheets to the file; so, lines of code such as ‘activeworkbook.sheets.count’ require our attention, since they reference the number of sheets in the file. Some clear, logical thinking and a quick tweak to this line of code allows us to move on.

Finally, and most complicated, we have to find a way to exclude the new sheets from the analysis. Remember, our code loops through sheets that contain original data form Eric; so, we need the VBA editor to ‘ignore’ sheets that do not contain original data. A number of approaches are possible; Chris quickly implements one in the video.

With these three issues resolved, it’s time to test the code; then, we will be ready to move on!

Though this video does not seem particularly exciting in terms of content, it covers an important point in Excel VBA development, and coding more generally. Put simply, we are not always coding. Important phases in the development process involve planning, conceptualisation or, in this case, ‘tidying up’ after a spurt of coding. It’s all part of the rhythm of VBA development – try to embrace each phase and understand that these ‘metaskills’ will help improve your practice. See you in the next video!

Part 11 – Towards Completion?

We did not do a huge amount of ‘coding’ in part 10, so let’s get into some powerful VBA techniques this time. In part 11 of the VBA Real World Example series, we create a dynamic mechanism to make names ‘stack up’ on each sheet. This means that names that appear once in the file are listed on the 1L sheet, names that appear twice on the 2L sheet, and so on. We will use some of VBA’s most powerful constructs to get it done, so let’s get into it!

The key construct here is .end(xldown). This awesome VBA syntax takes us to the next empty cell; or, in other words, to the bottom of a column of data. If we can tell Excel to go to the correct list sheet and to add the name to the end of the column, we can get this task done for Erik. At least, that’s the concept. So, let’s try to implement it.

Chris works through the syntax in the video, combining the offset (remember, we are looking at position control here) method with .end(xldown) to get the job done. Multiple errors occur as we work through the code; no matter, this is completely normal and an important part of the development process, just be sure that your file is saved and backed up properly.

After some ‘manual’ testing to check the results against those generated on the Indirect sheet, we feel ready to move on. Remember, the best way to test something is to do it in two different ways and achieve the same result.

Some jobs remain. First, we have to clear data out from the ‘lists’ sheets. A dynamic approach – that would cater for a changing dataset – would be possible here; but we opt for simplicity and programme a sensible range (approximately 2000 rows) to be cleared using the .clearcontents method. Finally, we add a button to trigger the macro – since Erik would not want to have to enter the VBA editor to run the code!

The task is close to complete. In the next video in the series, we will perform some final (hopefully!) testing and discuss the strengths and weaknesses of our approach – see you then, and don’t forget to leave a comment below the YouTube video to let us know how you are getting on.

Part 12 – Hand over a file with good client communication

How to be a better Excel consultant? People ask me this question all the time! The obvious answer is ‘improve your Excel skills’, but this answer is not, in fact, the best – at least in my view. If you really want to boost the impact of your Excel consultancy, concentrate not on technical stuff but more on the soft skills that wrap around your coding and make it relevant to the real world. One of these soft skills is client communication. Client communication is particularly important at the end of the project to ensure a smooth handover and guarantee that the customer can get value out of the application.

We explore two ways in which you can communicate better with your client in this video. First, how to build client confidence in the code we have implemented in this project? Macros can be powerful but users, particularly inexperienced ones, can find them troublesome; when does the macro finish, for example? Let’s make this crystal (Christal?) clear for the user by flashing up a message to tell them the routine is finished. Let’s also use a dynamic mechanism to tell the user how much data has been processed. There, we have probably multiplied the real-world impact of this macro by communicating effectively and putting client needs front and centre – cool!

What else do we have to think about before handover to the client? It’s sensible to note the assumptions you have made during development and to be transparent about the weaknesses of the file. Customers can be amenable to some limitations if it makes the coding easier; most customers are impressed by the power of the coding and are prepared to make sacrifices elsewhere in return. So, ask yourself the question, do you need maximum flexibility? More and more coding may not create more and more value; it’s more likely to be a case of diminishing returns.

In the video, we look at how to interrogate code, identifying weaknesses and noting assumptions. This is where neat-and-tidy code that is well-annotated really helps (we told you it would!) I recommend actually listing the limitations and assumptions in the file to remind you to discuss them with the client. This should build confidence in the implementation (and in you as a person), and avoid annoying bugs and crashes later on.

This brings us to the end of the VBA real-world example series. I have done my best to communicate the approach that I use in my projects to create value for real people, in the real world! I would love to hear your impressions of the series. Also, let me know in the YouTube channel comments what else you would like to see on this channel.

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.