How To Use VBA Arrays In Excel To Create A Simple Staff Roster
One of the most frustrating and time-consuming jobs in an office can be creating the staff roster. With constant requests for changes because of holidays, special occasions or staff absences most administrators are too busy creating rosters to think about developing a better system.
With a few lines of VBA code we’re going to create the basis for a staff roster system which will list each day of the week and the employees available for work on that day.
Matching Arrays To Create A Roster
All we need is a list of staff members detailing which days they are available for work; then we match these with each day of the week to generate a list of employees and which days they are working.
Joe Esposito, Mon Tue Wed
Maria Costello, Thu Fri Sat
The list of employees and days available we’ll keep in a worksheet called “Employees” and our code will simply match up each day of the week with the recorded availability for each staff member:
' create a roster collection to record each day of the week and who is available
Dim roster As New Collection
'define the sheet and select the employee available days
If you’re using a delimiter to create arrays a good choice is always the “|” or pipe character. A comma can create problems because the actual data might contain commas which can upset the array.
' create an array of the days of the week
days = Split("mon|tue|wed|thu|fri|sat|sun", "|")
' compare each day of the week to the days each employee is available
for x = 0 To UBound(days)
workers = ""
' if the day of the week matches the employees availability, add it to the list of workers for that day
For Each c In Selection
If InStr(trim(c.Value), days(x)) > 0 Then
workers = workers & c.Offset(0, -1).Value & ","
workers = days(x) & ": " & Mid(workers, 1, Len(workers) - 1)
We’ve now a got a list of the workers available for the current day and we’ll add them to the roster collection before moving on to the next day of the week.
The only thing left to do is print out the roster collection which is a list of workers available for each day of the week. The next step would be to present the list in a more user-friendly format for distribution.
mon: Joe Eposito,Peter Fargo,Lily Markham
tue: Joe Eposito,Lily Markham,Iain Malcolm
wed: Joe Eposito,Lily Markham,Iain Malcolm
thu: Maria Costello,Peter Fargo,Lily Markham,Iain Malcolm
fri: Maria Costello
sat: Maria Costello
sun: Peter Fargo,Lily Markham
VBA Code Enhancements
Developing a staff roster can be a complex task but it’s important to start somewhere. Once a basic system is in place it can then be added to without losing sight of the main objective. Some suggestions might include:
- Using random numbers to generate roster days when staff numbers are low
- Highlight days of the week which require attention
- Enable staff to rank backup days where they may be called upon to work
A staff roster will have a different structure for nearly every organization. But with some planning and knowledge of Excel and VBA it will nearly always be possible to produce a process which is an improvement on the current procedure.