Welcome to the fourth part of our series and our first assignment where we will be looking at how to integrate everything that we have covered so far to create a periodic performance report for management.
This will be the first screen that you will see upon opening the Working File. Don’t forget to like our Facebook page if you haven’t!
Using the Workbook
Click the various assignment sheets to read the problem sets and identify what is to be done to resolve the issue. Answers have also been provided within the Visual Basic windows, although you are strongly advised to attempt the problems yourselves first to test your capabilities in stringing together pieces of VBA code to solve problems such as this.
Alternatively, you may also refer below for a visual guide on what is covered inside the Training Workbook as well as how to conduct first time setup of Excel to enable VBA Macros (Part 1 of the Visual Guide), but you are highly advised to also try it out for yourself. Note that this is only 1 possible way to solve the 2 assignment questions within, and there exist many other ways, some more efficient and some less efficient.
1. Assignment 1
For our first assignment problem, we will be tackling a very common issue that plagues many VBA programmers, broken codes. These can arise from a variety of issues, ranging from an initially buggy code that was not generalised (i.e. not made to adapt to situations outside of its test data) to changing report/data formats that resulted in erroneous reports.
Whatever the cause, it is imperative that users know how to find and debug the codes stored in the Visual Basic library. In some cases, the bug will manifest itself clearly with an error window such as this:
In this case, not only is it apparent that there is an error in the code, but it is also obvious what that error is, which in this case is the lack of a “Loop” line to close off a “Do While” sequence. But other times, as we can easily see from our workbook, there is no outright statement, instead the error manifests itself in erroneous data entries, and it is up to the user to identify them as we can see below:
An important principle when it comes to data analytics and automation is to always check your outputs before submitting them for whatever purpose is needed. Another important principle is to always save before running your VBA code. Unlike with regular changes to the workbook, there is no undo option for changes enacted by VBA macros, so if you accidentally delete or override data onto a worksheet, there is no way to reverse those changes, and this could potentially set back your work substantially if you have to debug and rerun the entire macro again.
With these principles in mind, lets dive into the code to see what is wrong with it:
An easy way to identify which lines of code are causing errors is to use the F8 key to run your VBA macros line by line, allowing you to see exactly which lines are causing bugs or creating erroneous entries. Observe:
As we can see, the yellow highlight shows us where we are in our VBA macro, with everything on top of it being code that has already been executed. This is where we can see our first error, a wrong sheet name. Looking at the workbook, it is clear that there is no Assignment 1d worksheet. There is therefore a misspelling, and a quick rectification is all that is needed to fix the code.
Our next error exists here:
This is the wrong copy range, the correct range is actually E2:E4 because we are only interested in the numerical values for Nidus, not the accompanying data labels. Moving on:
Similar to our previous part, the range of our data is wrong, and should actually be B9:B11 as that corresponds to the profit data for Alatrax, which is what we are looking for, not the data from B1:B9. Finally, the most confusing part of our first VBA macro:
At first glance, nothing seems to be wrong. There is actually a sheet called Assignment 1, yet the VBA code gives an error stating that no such sheet exists:
The reason for this is a little known detail about the characters for “small L” and “1” in the Visual Basic Window, and that is that they display themselves in exactly the same manner. Here, the error rests in the fact that what is displayed is not a “1”, but a “small L”. If you ever wonder why your code isn’t working, a good habit is to check if your 1s and small L’s are really what they are. Changing this out completes the code:
Moving on to the second part of our debugging:
Unlike with the first part of our debugging exercise, this part has errors that don’t manifest in notifications. Rather, they manifest in erroneous outputs, not macro breaking bugs in the VBA code.
Despite that, the first error should be really obvious, and that is with the formula to sum up all of the profits generated. As you can see, the initial code inputs a formula that sums up all values in columns G to J, whereas we only want the values in G4:J4 because those contain our profit values. Another problem that arises is that it creates a circular reference, which could really screw your results as the formula will simply keep recalculating itself because the cell (6,7) is G6.
Our next error is with our nested if statement. The condition for specifying whether profit targets are met is wrong, because cells(7,6) is referring to F7, a cell that contains a string of text. In reality, what we want to reference is cell G7, which has the coordinates (7,7).
Our next 2 errors also suffer a similar ailment with regards to their cell references. In addition, there are also errors in the output values. As we can see, in the current macro, we will output a value of “No” if our profits exceed our target as we are outputting the string variable b which contains that value. Obvious this is not what we want and we should reverse the order of their placement. This is an example of misplaced string references, and in extended VBA macros, this can potentially arise in programming when multiple variables are being defined and redefined multiple times in the same code. Working all of these changes into our code, we now have this:
With all our fixes done, we can now output the data that we need to assess our periodic performance:
2. Assignment 2
In this assignment, we will be tackling the issue of creating a brand new VBA macro to automate an existing process. This requires us to first visualize how we would accomplish the different steps in the process manually, before programming them into VBA for automation. Let’s take a look at our problem set:
From the instructions on the left, what we want to do is to extract information relating to salary and performance from our source sheet (Source 2), and based on the results, decide whether they are deserving of pay increments, bonuses, and in the case of Meesa, whether they are to be terminated from the company.
Firstly, let us tackle the first and easiest part of the assignment, the extraction of data from the source sheet to the assignment sheet. If you’ve been through all of our previous tutorial series, then this should be easy for you as it simply involves the copying and pasting of data from 1 sheet to another.
In our next step, we need to determine whether their performance entitles them to a salary increment, and if so how much. In this simple example, as long as your performance is not unsatisfactory, you qualify for an increment that varies according to your rank. Therefore, we insert a VLOOKUP formula into the various cells, using integer and lastrow variables to input our formulas into the cells we want, and then looping through the Performance column to replace the increments of any unsatisfactory performer with the string “No”.
Next, we need to calculate the expected future salary based on any increments that our various employees may receive, and this is done using the autofill function to insert a simply formula to sum up the current salary with any increments.
Moving on, we will now tackle the issue of whether Meesa is to be terminated. According to our assignment information, Meesa is to be terminated if their performance for this assessment is still unsatisfactory. While this can be done using loops and nested if statements, we will be demonstrated how to use the find function.
As can be seen above, we selection the range that contains all our names, then specified that we are looking for the word “Meesa”, upon which we will then select and see if their performance is rated as “Unsatisfactory”. If so, the value in the increments column, which was previously “No”, would then be changed into “To be terminated”, and their expected future salary changed to $0 from the previous amount of $3,000.
Having resolved the issue with Meesa, we then move on to determine if some individuals deserve a bonus, and if some should have a performance penalty. In our case, we use nested if statements to determine the appropriate value to input. First, we check if the value in the performance column is “Exemplary”, and if so we input the bonus value of $6,000. If not, we check if it is “Unsatisfactory” and if so we input the penalty value of ($1,000). Finally, if the performance is neither of those 2 values, then we simply reflect that the individual is neither going to be rewarded or penalized.
Finally, we format the cells that will contain our salary values to be in $ terms with 2 decimal places. All of this then allows us to output the following report:
Congratulations, you now have a greater understanding of how to use simply VBA macros to automate and conduct basic analysis of reporting data. While simplistic, these 2 assignments reflect real world scenarios that I myself have encountered, and I hope they provide you with a good stepping stone towards applying VBA macros in the problems that you face. Always remember, if you ever find yourself stuck, always think about the problem logically. Visualize the steps you would take manually, and then decide how you would translate those steps into VBA code. In the next part of our series, we will be covering more advanced data analysis tools, including how to automate toolpak activations and call analysis toolpaks using VBA such as correlations and regression analysis.
This tutorial was put together by Goh Jun Hui.