Log in Page Discussion History Go to the site toolbox

Scheduler

From BluWiki

DSS Project: The Scheduler

Introduction

Our project for the DSS class is a Scheduling system, designed to help a group of students or faculty plan the best time for meetings based on the individual schedules of the meeting participants. This application can typically be used in a university setting or in an office environment, though in an office environment a more sophisticated integrated email and scheduling package like Microsoft Outlook or Lotus Notes would likely be preferred. The application uses Visual Basic for Applications along with Microsoft Excel, a part of the Microsoft Office Suite that has now become ubiquitous.

The Rationale

The Approach

ScreenHunter 054.jpg

Program Features

The program uses basic Excel functionality combined with some VBA programming to manipulate cells and data.

1. User Interface

Refer to the screenshots in Appendix A

The Scheduler Excel workbook is set up so that when it is opened, the dialog box presents itself. Each faculty member has their weekly schedule stored in individual Excel sheets in the workbook. The convention used is that a '1' in a cell indicates that the teacher is unavailable for that time slot. All other cells have a '0' in them indicating availability. By populating each individual sheet with 0's and 1's, we can indicate the available/busy times for each of the faculty on a weekly basis. Please note that this takes into account only fixed busy times and cannot accommodate one-off appointments.

A dialog-box style interface is used which can be navigated quite intuitively and sits on top of Excel to gather the required information. The VBA application on initialization uses the Worksheet object model to cycle through each of the sheets and pick up two pieces of information from each one - the sheet number (as referenced by Excel) and the name of the teacher to populate the multi-select list box.

By selecting names of faculty you wish to plan a meeting with in the multi-select list box and clicking the 'Update Selection' button, the Planner sheet now displays a color-coded schedule indicating availability of the participants. The color code reads as follows:

White : Available/Free for all selected participants Yellow: Available/Free for all selected participants except 1 Red: Unavailable for 2 or more participants

By selecting and deselecting participants and updating the schedule, the program modifies the color code to indicate busy and available times for a meeting. If there is a yellow cell (one person unavailable) a comment appears indicating who is unavailable so that a request might be made to an individual. The availability percentage of time slots is also indicated in the right hand corner.

The application also provides the functionality to add a new faculty member's schedule or update an existing one. The individual Excel schedule sheets can be populated individually by the staff or their assistants. The scheduling information for each teacher is entered on the sheet in the B7:F28 range in the '1' and '0' fashion as described above. These individual sheets can be mailed to the Meeting Planner who imports it into the Scheduler application.


2. Data Input

The most current individual available/busy data from attendees can be imported into the meeting planner module to plan meetings. New faculty scheduling information can be added in the same manner using the Update/Import feature. The unique key will be a combination of first name and last name which is unique for every participant.

3. Backend and Algorithm

The backend of the program uses basic Excel functionality and some VBA to generate a surprisingly useful application. We had no need of Solver or Crystal ball at this point.

Data to be Used

Hypothetically speaking, each professor would fill out their own schedule (only one), although for this project, we created all the schedules for each of the professors in one excel document.

Limitations

  • Each participant's schedule is equally weighted
  • Cannot easily be integrated with email (invites and announcements)
  • Unable to update schedule automatically over the Web


Next Steps

  • Automatic integration with email
  • Differential weighting (Need to Have particpants vs. Nice to Have participants)
  • Automatic updating of schedules over Web

Conclusion

We learnt a lot while making this simple yet useful application. Our work highlights the power of Excel and VBA working together to create a tremendously versatile programming environment. While future teams can extend this foundation further, we believe that this is a good start to a more full-fledged scheduling software application.

Site Toolbox:

Personal tools
GNU Free Documentation License 1.2
This page was last modified on 25 April 2007, at 05:25.
Disclaimers - About BluWiki