How to use arrays in Excel VBA to create a simple Staff Roster

How to use arrays in Excel VBA to create a simple Staff Roster -

One of the most frustrating and time-consuming work in an office can be the creation of staff rosters. With constant requests for changes because of the holidays, special occasions or staff absences Most administrators are too busy creating shifts to think about the development of a better system.

With a few lines of VBA code that we will create the basis for a personal roster system that lists every day of the week and employees available to work on that day.

correspondence Array to create a Roster

All we need is a list of staff members in detail on what days are available for work; Then we combine 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 to keep it in a worksheet called "Employees" and our code will simply match each day of the week with the availability recorded for each staff member:

 ' create a roster collection to record every day of the week and is available 
 
 roster Dim as New collection 
 
 'Define the sheet and select employees available days 
 Sheets ( "employees") . Active 
 Range ( "A2"). CurrentRegion.Columns (2) .Select 

If you use a delimiter to create arrays a good choice is always the "|" or the pipe character. A comma can create problems because the actual data may contain commas that may disturb the array.

 'Create an array of days of the week 
 days = Split ( "mon | mar | Wed | Thu | fri | sat | sun", "|") 
 
 'compare each day of the week for the days each employee is available 
 for x = 0 to UBound (days) 
 employees = "" 
 
' If the day of the week matches the employee availability, add in the workers list for that day 
 for every c in 
 
 If Instr (trim (c.Value), days (x))> 0 
 [1945001PoiSelezione] = workers and workers c.Offset (0, -1) .Value & "," 
 
 End If 
 next 
 working days = (x) & ":" & Mid (workers, 1, Len (workers) - 1 ) 

we now have got a list of available workers for the current day and we'll add them to the collection roster before moving on to the next day of the week.

 roster.Add workers 
 Next 

Exit code
The only thing left to do is print the roster collection which is a list of available workers 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 
 Mar: Joe Eposito, Lily Markham, Iain Malcolm 
 Mer: 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 

improvements VBA code
the development of a personnel roster can be a complex task but it is important to start somewhere. Once the basic system is in place it can then be added without losing sight of the main objective. Some suggestions may include:

• using random numbers to generate roster days when staff numbers are low
• highlight days of the week, that require attention
• Enable the backup days-ranking staff in which they may be called to work

summary
a staff roster will have a different structure for almost every organization . But with some planning and knowledge of Excel and VBA it will almost always be possible to produce a process that is an improvement over the current procedure.

Tweet