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

Sheets("employees").Activate

Range("a2").CurrentRegion.Columns(2).Select

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 & ","
End If

Next

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.

roster.Add workers

Next

Code Output

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

Summary

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.

Source by Andy L Gibson