How To Use VBA Arrays In Excel To Create A Simple Staff Roster

How To Use VBA Arrays In Excel To Create A Simple Staff Roster

One of the vital irritating and time-consuming jobs in an workplace may be creating the workers roster. With fixed requests for adjustments due to holidays, particular events or workers absences most directors are too busy creating rosters to consider growing a greater system.

With a number of traces of VBA code we will create the premise for a workers roster system which is able to listing every day of the week and the staff obtainable for work on that day.

Matching Arrays To Create A Roster

All we want is an inventory of workers members detailing which days they’re obtainable for work; then we match these with every day of the week to generate an inventory of staff and which days they’re working.

Joe Esposito, Mon Tue Wed
Maria Costello, Thu Fri Sat

The listing of staff and days obtainable we’ll preserve in a worksheet known as “Workers” and our code will merely match up every day of the week with the recorded availability for every workers member:

' create a roster assortment to file every day of the week and who is obtainable

Dim roster As New Assortment

'outline the sheet and choose the worker obtainable days

In case you’re utilizing a delimiter to create arrays a good selection is all the time the “|” or pipe character. A comma can create issues as a result of the precise knowledge may comprise commas which might upset the array.

' create an array of the times of the week

days = Break up("mon|tue|wed|thu|fri|sat|solar", "|")

' evaluate every day of the week to the times every worker is obtainable
for x = zero To UBound(days)
employees = ""

' if the day of the week matches the staff availability, add it to the listing of employees for that day
For Every c In Choice

If InStr(trim(c.Worth), days(x)) > zero Then

employees = employees & c.Offset(zero, -1).Worth & ","

Finish If
employees = days(x) & ": " & Mid(employees, 1, Len(employees) - 1)

We have now a acquired an inventory of the employees obtainable for the present day and we’ll add them to the roster assortment earlier than shifting on to the following day of the week.

roster.Add employees

Code Output

The one factor left to do is print out the roster assortment which is an inventory of employees obtainable for every day of the week. The following step can be to current the listing in a extra 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
solar: Peter Fargo,Lily Markham

VBA Code Enhancements

Growing a workers roster is usually a complicated job but it surely’s vital to begin someplace. As soon as a fundamental system is in place it could actually then be added to with out dropping sight of the principle goal. Some recommendations may embody:

  • Utilizing random numbers to generate roster days when workers numbers are low
  • Spotlight days of the week which require consideration
  • Allow workers to rank backup days the place they could be known as upon to work


A workers roster could have a unique construction for practically each group. However with some planning and information of Excel and VBA it’ll practically all the time be doable to supply a course of which is an enhancement on the present process.

No Comments

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.