Create school timetable by vba

[abuzainab73]

Is it possible for the vba codes in excel organized lessons in the weekly schedule according to the available data in the worksheet1 (Number of hours for each teacher assigned to teach class) ?
by dumpng in worksheet2 , taking care to balance the number of lessons in each day
If possible, I hope to get this code by putting it in the macro button (Dump) in worksheet2 .

Regards

[Simon Lloyd]

yes it's possible but your workbook needs much more explaining, you need to spend a little more time on its structure.

[abuzainab73]

can you help me please .?

[Simon Lloyd]

Yes but first you must put some effort in yourself, as i said you need to restructure your workbook/data in a fashoin that will be easier to for you to maintain and manipulate data, then when you have done that you need to explain thoroughly what you would like to acheive, how and why!

[abuzainab73]

Hi Simon Lloyd
What do I need Actually in this code is the distribution of teaching hours for teachers in the days of the week, Take for example teacher 1, which assigned a teaching class C4 (t two hours a week) , and class C5 (two hours a week) - and this we know by the numbers in the teachers rows 1 parallel to the class C4 and class C5 ( worksheet1) . so that the figure refers to the number of hours assigned to the teacher to teach the class -.


• In (worksheet2) there is 5 days in each day there is 7 hours starting from 7:00 to 13:00 pm ,referred to figures 1-7.

• need to consider the following when dump the table in worksheet2:


1 - hours must be distributed on the day of the week the balance, if a teacher has 4 hours to teach one class it will be as follows:
· 1 hour on Monday.
· 1 hour on Tuesday.
· 1 hour on Wednesday.
· 1 hour on Thursday or Friday.


2 - pasture the number of hours per day for each teacher, if the total hours of teaching for one teacher is 20 hours per week, it will be 4 hours per day.


3 - one teacher only in the classroom every hour.

Is this enough to explain ?

Thanks for your interes

[Simon Lloyd]

Your explanation clears up nothing, infact you only confirm the ambiguity of your structure e.g

· 1 hour on Thursday or Friday.

Which is it......or don't you care?
You're expecting excel to take on guess work as to how to split a teachers hours and over which days, by your reckoning if a teacher has a class for 3 hours it can be spread over any day, also if a teacher has a class for 2, 3, 4 , 5....etc hours why is it not possible they have more than one class a day?

I'm afraid i can't help you with this!

[austenr]

[abuzainab73]

Simon Lloyd,
anyway thank you v.much brother.

anybody can help me ?

[Aussiebear]

Are the figures presented in your workbook representing those for one week or can it be extended over a greater period of teaching days?

Example Teacher 1 has 4 hours, You have alluded to 1 hour per day, and with the option of the 4th hour being undertaken either on Thursday or Friday.
Teacher 2 has 14 hours. If the rule is one hour per day then naturally this means 14 days to complete the teaching, or can there be more than one hour per day?

[abuzainab73]

Hi Aussiebear

No this is not the rule ( that it is one hour for the teacher a day),
but the rule is:

Distribute hours on weekdays from Monday - Friday in order to achieve a balance in the schedule .


According to an example that I reported for teacher1 who has only 4 hours per week,so his teaching hour will be as I explained .


Teacher2 has 14 hours per week This means that he will teach 3 hours for (4 days) and 2 hour for (one day), so the total of teaching hours (14).

Teacher19 has 15 hours per week and this means that he will teach 3 hours each day.
(3 * 5 = 15)

Teacher29 has 20 hours per week and this means that he will teac 4 hours every day.
(4 * 5 = 20)


In this way, the teaching hours of all teachers will be balanced .

[Aussiebear]

A couple more questions based on your workbook

1. Sheet 1, shows 97 possible teachers over 30 courses in your table. That's 2910 cell values in your range if my maths is correct. What limits are there in this table? Are you limited in the maximum number of hours per course per week?
2. Sheet 2, shows 100 teacher positions by 35 hour values giving 3500 values in the range.... Are you sure you are going to fill all these?

[abuzainab73]

in sheet1 there is :
30 class
each class shuled studty 35 hours a week
so total of teaching hours for all class 1050 hours a week


( in sheet 1 ) Some teachers are not assigned to them teaching hours .

required to greate a schedule for the teachers who assigned to them.

[abuzainab73]

Hi Aussiebear

is this clear ?

[Aussiebear]

Please be patient. I'm just trying to get everything clear so that others can understand the nature of your request

[Aussiebear]

I can see some real issues with this.
1. From real life experience - Whilst formal schooling for me finished nearly 30 years ago- I think the process of allocating teacher hours is quite complex. It was more effective for teachers to be teaching in multiple units ( from both an employers perspective and from the teachers perspective), rather than single units. However I digress somewhat.

2. Allocating teachers on the basis of dividing the total hours by 5 and adding the balance - one unit per day - to each day, may work here but.... this doesn't tell us when in the day to actually allocate the hour. If we were to start at 7am, the school will be flatout in the mornings and potentially be non functioning by afternoon. Is this really an effective method? I would doubt it.

3. You need to set up some more rules regarding allocation.

For example: Which subjects are better being taught at which part of the day? Teaching Geology straight after lunch was a recipe for sending classmates into slumberland - from my recollection. Hardly a day went by where one or more of the class ended up having to touch your toes to recieve 6 of the best with the cane.

Can students move around the school effectively to maximise the education time alloted for a class or do students stay put in the classroom and teachers move around? If teachers move around, do they have time to gather their teaching material and prepare for the next class subject matter?

Should teachers be given a break between classes to prepare for the forthcoming class/es?

How many classrooms exist? Maybe it might be better to allocate teachers or students to a class room and then work out how to distribute the lessons?

I'm afraid that this is a very complex issue regarding the allocation

[abuzainab73]

Is there a solution?

[Simon Lloyd]

Try and follow Aussibears last post, make the structural changes and answer the questions he posed!

[Aussiebear]

abuzainab73 why have you posted an image of a child in post #16? This has no relevance to this forum, and it adds nothing constructive to the debate. Please do not do this.

[rbrhodes]

Hi,

Jumping in again.

[rbrhodes]

Hi,

No interest in this I guess. I just spent 3 days working on it! Oh well.

For Sale: 1 custom program.