Create weekly and monthly training report using Excel VBA

I have been working on a project of trying to automate as much of my work in Excel as possible, with the possibility of freeing me up for added responsibilities in my job.

In the last year or so, I was given the task of taking a spreadsheet that was provided weekly and picking out the staff who had training due within the next 60 days. I had to print the data on the sheet in a vertical fashion, which is what my manager wanted. I accomplished this by writing a program in Excel VBA to complete the task because I never would have been able to complete the work in my allotted 14 hours a week. In addition, it would have been painful for my wrist and hands to undertake that much typing.

The program counted the number of rows and columns, with the desired data being printed out five rows beneath the last column. I was happy with this setup, only having to tweak the code every now and then. I decided that instead of having the report on the same sheet, I would like the report to be printed out on a new sheet. Therefore, program that I had originally written needed to be updated.

Get Crystal X’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The screenshot below is an image of the training data that I used to write the amended program:-

The weekly report, that has been written on a new sheet of the workbook can be seen below:-

The psuedocode for the program that I have written can be seen below:-

1. Define the variables used in the program.
2. Initialise the active sheet and the sheet that the report that is going to be added, with the report written on.
3. Define the name of the sheet, being Weekly Report and the date of the report.
4. Initialise the current and training dates.
5. Use a do while loop to count the number of staff.
6. Determine the total number of courses by multiplying the number of columns of training courses by the number of staff.
7. Prepare the worksheet with headers used in the report.
8. Initialise the variables for the report and red, green, amber values.
9. Create a for loop that will iterate through each row in the sheet.
10. Initialise the variable, printedName, to False. This will ensure that a staff member’s name, team, and appraisal date appear only once in the report.
11. Create a second for loop through the first loop, iterating through each column.
12. If a cell value is “Not Attained” or is less than the current date: print the name, team, appraisal date, course title, and “ASAP”. Increment row training and total red variables,
13. If a cell is greater than the current date but less than the course date: print the name, team, appraisal date, course title, and course date. Increment row training and total amber variables.
14. Once both for loops have been iterated through all of the rows and columns, the statistics of red, green, amber will be printed.
15. The program is completed.

If an individual wants to create a monthly report, that can be accomplished too. The screenshot below reveals a monthly report that can be created for the training data used in this blog post:-

The psuedocode for the monthly report can be seen below:-

1. Define the variables used in the program.
2. Set the sheet used as being the active worksheet.
3. Prompt the user to enter the month of the report and use this to name the sheet.
4. Determine the number of rows and columns in the training data.
5. Create a dictionary of teams in the training data by using a for loop to iterate through each row of the training data.
6. Set up a for loop to iterate through each key in the dictionary. Initialise staffcount, outstanding, possible variables.
7. Set up a second for loop to iterate through each row in the training data. Increment the number of staff in each key of the dictionary.
8. Within the third for loop, use a case statement for the cell values: “Not Required”, “Not Attained”, “”, else. This case statement will iterate through each cell value: Not attained will increment outstanding and possible; “” and else will increment possible.
9. Calculate the compliance achieved for each team.
10. The program is completed.

The two above programs are used every week in my day job and help to automate my work immensely.

I have created a code review to accompany this blog post and it can be viewed here:- https://youtu.be/XAOUjTebhMo