Scheduling program in VBA

[dipdog]

I have a general question about macros. I've been working with Excel for a little while, and I'm interested in learning more about VBA. There is a project that I've been trying to work on for a while. I've been looking around the internet for a program that would help our work group to schedule their monthly on-call hours. There are only a few formulas that we use. We put 2 of 4 people into 2 different call positions (1st call and 2nd call) every night. Ideally the call positions change every night, except for Fri-Sun during which time they stay the same (ie. same two people in same two call positions Fri-Sun). One of the 4 people only takes call Fri-Sun (no call Mon-Thur). And we try to make the amount of call (weekday call and weekend call) that everyone takes even. I've tried to find a scheduling program that could give us a monthly calendar, allow people to reserve dates that they would not be in the call rotation (ie. vacation), and then evenly assign people to the days that are left. I've been surprised to find that there is very little available to meet these conditions. So I've been considering just trying to write something myself.
Anyhow, my questions is: does this sound like something that would be best suited for an Excel program with VBA macros? Does it sound relatively feasible, or very difficult. And if it does sound feasible, do anyone have any resources that they would suggest for reading up on how to do this.

Appreciate any help you can offer!

- Scott

[Bob Phillips]

Scott,

In essence, this sounds ideal for Excel. You can use formulae to get a basic setup, and tweak it with VBA. With Solver and Goal Seek as available functions you should be able to get close.

As to how easy it might be, that would depend upon how clever and how automated you want it. I did something similar recently for a care schedule, only using formulae. Rather than try and get it to an absolute, I created a 'best-guess' situation which I linked the actual schedule to. They could then overtype the best-guess if they wanted to tweak it. Sometimes that last 5% is not worth the 200% effort.

[dipdog]

Thanx for your reply! That encourages me to move forward. Do you have any suggestions for a place to start if I were to look for instructions and examples for this sort of thing (ie. any particular website, book, or links on this site)

thanx again
- Scott

[Bob Phillips]

Not really mate, apart from a forum like this one.

The best thing is to sit down with a nice stiff single-malt, and think it all through, work out how you want it to work. Then start building it slowly, posting specific questions here as you get stuck.