Re: Vba Coursework
Quote from Istupidheclever
Hi,
I'm not going to pretend, hold my head up high or bow out gracefully. Instead I am going to beg, admit defeat and pray for help. Yes I am blatantly asking someone to do my coursework for me and will take the insults by others for my actions.
Well, at least you are honest.
Quote from Istupidheclever
...Is this easy for you guys?
Sitting at your PC and typing in some code is really the final step in the process. Writing down what your solution should be (in this case, the instructor has done that for you), perhaps making a diagram of your logic flow to help you understand how your solution should work, and then writing your code is easier.
As with most things worth learning, doing is an integral part of making it easy.
Quote from Istupidheclever
I was introduced to the world of VBA a couple weeks ago and have been given this to do in the next two days, making up 100% of a module.
I'm prepared to take general jibes or mockery for this potentially being extremely easy and me being dense, but any help would be appreciated.
Obviously with the due date fast approaching, getting the completed assignment in is your most desirable outcome. Taking the grade you have earned (an F, I would presume, given your apparent panic) and repeating the course for a better grade (not all schools allow this) or asking for an incomplete and an extension of time to complete the assignment are more realistic outcomes. Of course, the latter would require that your instructor is familiar with you (ie., you didn't cut class frequently) and has seen an effort to learn on your part.
Having done my time at Uni, I understand that not all instructors are human. Even with the most compelling of reasons to grant an extension, sometimes the power of the professorship overcomes them and they force a D or an F upon you. Take your lumps if that is the case.
Quote from Istupidheclever
"An operating theatre manager regularly needs to schedule operations for a list of patients; all operations are equally urgent. Operating theatres are an expensive resource in the hospital so the manager wants to use them as efficiently as possible, by scheduling the operations to take place over as few days as possible.
So far, so good.
Quote from Istupidheclever
There are two surgeons who carry out the operations in separate operating theatres. The surgeons expect to work six hour days on average and will be very unhappy if they regularly work over seven and a half hours a day.
Some meat and potatoes here. We know that we can schedule the doctors for up to 7.5 hours a day if necessary, but six is better.
Quote from Istupidheclever
For each patient on the list the manager knows which of two surgeons will carry out the operation...
We are told who will perform each patient's operation.
Quote from Istupidheclever
... and based on hospital records has calculated the expected (mean) operation duration and the standard deviation for similar operations.
We are given how long the operation SHOULD take (mean), and how long it MIGHT take (mean + standard deviation). A tabular layout may be useful. We can see how long the doctors SHOULD and MIGHT work when we schedule operations in a theater for them.
Quote from Istupidheclever
A simple way of finding a feasible (but quite possibly sub-optimal) schedule for this problem is to use a greedy heuristic. We are going to use the following greedy heuristic in the coursework:
This is most helpful as in the real world, a client or boss will likely not be this specific when a task is assigned. Not just a humble opinion, but some practical advice from someone who does this as a living.
Quote from Istupidheclever
1. Select a surgeon, S, whose schedule has not been planned.
Not too hard, we only have two surgeons, so pick one.
Quote from Istupidheclever
2. Create an ordered list, L, of the patients who require treatment by that surgeon, in descending order of expected operation duration.
An array of the patients, with a specific sort order is being requested. The provided code in the example workbook provides you with functions that you can use to determine the expected operation duration.
Quote from Istupidheclever
3. Consider the patient, P, at the top of L, awaiting operation O. Add P to the earliest acceptable daily schedule for the surgeon S. A daily schedule is acceptable for patient P if adding operation O will not:
Before getting to the conditions, let's put this in plainer English. With our ordered list of patients, find the day on the surgeon's schedule that fits the operation the best.
Quote from Istupidheclever
(a) take the total expected duration of the operations for that day over 6 hours, or
(b) take the total expected duration of the operations on that day plus the standard deviation for the total duration of operations on that day over 7.5 hours.
Condition (a) limits us to days where an additional procedure won't take the hours beyond 6
Condition (b) limits us to days where an additional procedure plus the standard deviation will not take the hours beyond 7.5
Quote from Istupidheclever
If no such acceptable daily schedule exists then create a new daily schedule at the end of the current schedule for surgeon S to accommodate patient P (and implicitly operation O).
We must start a new day if we can't schedule the operation on an existing day.
Quote from Istupidheclever
4. Remove patient P from L
Move on to the next patient.
Quote from Istupidheclever
5. If L is not empty, then return to step 3.
If the list is not empty, then schedule the next patient.
Quote from Istupidheclever
6. If there are surgeons whose schedules have not been planned then return to step 1.
Schedule the other surgeon's procedures.
Quote from Istupidheclever
In order to make the heuristic deterministic (i.e to guarantee the same result each time it is run on the same problem instance), we must specify how ties are broken in step 2. In this coursework, ties should be broken by selecting the patient with the highest standard deviation of operation duration first. If two patients have the same expected operation duration and standard deviation of operation duration, then the patient with the smallest index (first on the original list of patients) should be chosen first.
Additional conditions on the list of patients. This would have been nice to know back in step 2. This, of course, is a prime example of why a pencil and paper approach is better than starting to code with your instructions at your side.
Quote from Istupidheclever
You must implement the greedy heuristic for the theatre scheduling problem in a VBA project in Excel. Your program must have a single user interface so that the user can:
Single user interface. A userform would by my choice, but buttons on a spreadsheet would also do.
Quote from Istupidheclever
- read data from a text file
- recognize any problems with the data
- run the greedy heuristic
- output the results.
- Search OzGrid for reading data from a text file. Many examples exist. Run this from a button on a userform or from a button on your worksheet.
- Error checking the data, your professor gave you some functions that may be useful here. Assign these to a button on a userform or from a button on your worksheet.
- Run the greedy heuristic. Once you have it written, run it from a button on a userform or from a button on your worksheet.
- Output the results, either to the screen, a printer, or give the user a choice. In any case, run this from a button (or buttons) on a userform or from a button (or buttons) on your worksheet.
Quote from Istupidheclever
Now I realize that this is not a completed VBA project for you. What it is, hopefully, is a point in the right direction. Going step by step, you should be able to ask direct, specific questions that will allow you to absorb some of what is being done along the way.
VBA frequently presents more than one way to achieve a result. For example, nested IF-THEN-ELSE constructs and the SELECT CASE structure can both be used to determine which action will be taken given a set of constraints that are tested. Which one is best is debatable depending on the skill of both the coder and the end user.
Asking questions here for a specific portion of the project MAY result in getting multiple solutions. When you have code for a portion of the project and it isn't working, post the code (using the code tags) so we can help you debug it.
If you open the VBE and look carefully, your instructor has given you the name of two functions that you must complete. They are:
Public Function TestFile(ByVal strFile As String) As FileError
' TO DO
End Function
Public Function GetAlgorithmResults(ByVal strFile As String) As clsResults
' TO DO
End Function
Take a close look at this subroutine:
Private Sub ResultsDemo()
'Demo code is in here.
End Sub
See if this sparks any thoughts or questions.
Kindest regards,
