Calculate statutory and mandatory training compliance with Excel VBA

If you work for a government organisation, you will invariably be required to undertake periodic training. There is likely to be a training officer who will track training of you personally and of your department.

There are two types of training that are tracked and analysed, being:-

• Statutory
• Mandatory

Statutory training is training that UK employers are legally required to provide. It ensures that employees receive information, instruction, and training needed to work safely and comply with legal duties.

Mandatory training is compulsory training that an organisation requires staff to complete to ensure safe, effective, and efficient service delivery.

My day job requires me to print off and analyse a training report in my work area every week. These tasks require much manipulation of two particular spreadsheets, where I have to combine columns of data from two spreadsheets into one. I then have to take the data from the two spreadsheets to calculate the average completion compliance.

I had previously been calculating completion compliance manually, and this process was somewhat time consuming. I decided that, rather than obtain these figures by hand, I would write a program in Excel VBA to calculate these completion values automatically.

This blog post contains the two Excel VBA programs that I have written to automate a process that I had previously been obtaining by hand:-

• A VBA program to calculate the percentage complete of each column of data
• A VBA program to calculate mandatory and statutory training completion of each row of data

Below is a screen shot of the simulated data and the results of the two VBA programs once they have been run:-

The first Excel VBA program that I have written is how to calculate the percentage training complete by column of data. Each column of data is a specific training course.

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 psuedocode for this program is:-

1. Define the variables used in the program.
2. Set the worksheet the program being applied to is ate active worksheet.
3. Determine the last row and last column and display this information on the screen.
4. Add a heading two rows down from the last row that the data is on.
5. Create a for loop to cycle through each column of data and initialise the completed and required variables to 0.
6. Within the for loop, create a second for loop that cycles through each row of each column of data.
7. Within the second for loop, set up a case statement to select different options, such as NOT REQUIRED, NOT ATTAINED, ATTAINED, and a date. Calculate required and completion counts based upon what the value of each cell is.
8. Once each row of a column has been looped through, calculate the percentage of training that has been completed.
9. Once each column of data has been looped through, the program will display a message saying the training percentages are complete.

The second Excel VBA program that I have written is to calculate the statutory and mandatory training percentages for each row of data. Each row of data correspondends to a specific employee’s training records.

The psuedocode for this program is:-

1. Declare the variables used in the program.
2. Create the statutory and mandatory dictionaries that contains the names of the training courses.
3. Count the number of rows and columns.
4. Create a for loop that will iterate through each active row of the spreadsheet.
5. Within the for loop, create a second for loop that will iterate through each column in the spreadsheet. Each column corresponds to a course. The course will be contained in either the mandatory or statutory dictionary. The value of each cell will either be NOT REQUIRED, NOT ATTAINED, ATTAINED, or a date.
6. The percentage complete for both the mandatory and statutory courses will be calculated and printed on the last three columns of the spreadsheet in a percentage format.
7. The headings will then be printed on the final three columns of the spreadsheet.
8. Once each row of data has been looped through, the program will display a message saying the training percentages are complete.

The two above programs will help me to expedite my work, freeing me up to undertake more meaningful tasks at my day job.

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