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... Download File Link:
----------------------------------------------------------------
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.
Series Playlist Link:
**FREE** Your Complete Guide to Excel Spreadsheet Projects
50-Minute (non-public) Video and PDF
When you sign up to the Tiger mailing list:
Also on Insta :-)
0 Comments