Calculate number of contiguous hours

manpreet Best Answer 2 years ago

I have a spreadsheet which contains a sheet called 'Classes' this contains the start (L) and end time (K) of each lesson as well as the duration (J), class code (A) and student name (B) who attends this lesson.

I have to timetable extra lessons around this timetable but students can only have a maximum of 5 hours without a break. Whilst I timetable someone else is also changing the timetable I'm working around so each day I need to be able to run a check to see if any lessons have been moved to give a student too many hours in a row.

The only way I can think to do this is some kind of loop which concats the student name and start time then compares it to a concat of student and end time. When it finds a match it adds the durations together. I need it to return the student and the day where they have too many hours. I have no idea how this might be achieved in VBA bar my suggestion. Any ideas?

manpreet 2 years ago

Sorry there are no images in this answer. The new system for including images introduced since I last posted an answer crashes or crashes my browser. I will try again tomorrow. I have tried again with no success. I have reported the problem but doubt I will hear anything before Monday. Perhaps the answer is usable without the images. Alternatively, my profile includes an email address allowing you to request a copy of the images if you wish.

If you are going to Google for anything please make it “Excel VBA Tutorial”. If you search for code to achieve some objective without knowing the basics of VBA, you will not recognise the code when you find. We get questions here where the posted code is almost exactly what the questioner wants but they lack the VBA to make even a trivial change.

If you search for “Excel VBA Tutorial”, you will find many to choose from. Try a few and then complete one that matches your learning style. I prefer books. I visited a good library and borrowed the most promising Excel VBA Primers to try at home. I then bought the one I liked best as a permanent reference. Twelve years later, I still check it now and again. The time you spend learning the basics will quickly repay itself.

Once you know your basics, you are ready to start designing your macro. I have created some demonstration data which matches your description:

Original data

I have added a Day column because you say the worksheet contains all classes which take place in one week. I have made most of the classes one hour and have allowed five minutes travel time to get from one class to the next. I do not care if this data is not very realistic; I just want something vaguely appropriate to as test data and as a discussion aid.

To find continuous blocks of lessons for a student, you need the data to be in student-day-start sequence. I have entered “day” as Mon, Tue and so on which is convenient for a human user but means that when sorted “Fri” will come before “Mon”. The macro treats each day independently so does not care about the sequence of days. It may not that matter if an anomaly with Friday’s lessons is reported before one with Monday’s. I will revisit this issue later.

It can be better to finish the design before any coding. However, if the early stages are obvious, it is usually easier to code them immediately so you can see the data as it will be when designing the later stages.

The easiest way of getting the code for a sort is to use the macro recorder. The macro recorder is no use for loops or if-then-else-endif but is convenient when you don’t know the syntax for a statement.

I activated worksheet “Classes” then switched the macro recorder on. (Tools -> Macro -> Record New Macro). I selected all cells then “Data -> Sort” then specified the columns I wanted. After sorting, I closed the macro recorder. The code saved was:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/09/2015 by Tony Dallimore
'
    Cells.Select
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("G2") _
        , Order2:=xlAscending, Key3:=Range("L2"), Order3:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
End Sub

This is syntactically correct VBA but it is not good VBA. The macro recorder does not know your objective; it simply records each statement in isolation. However, tidying this up is not difficult:

  With Worksheets("Classes")

    .Cells.Sort Key1:=.Range("B2"), Order1:=xlAscending, Key2:=.Range("G2") _
        , Order2:=xlAscending, Key3:=.Range("L2"), Order3:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal

  End With

The above is a minimal tidy. I have added a With statement to identify the target worksheet; I have remove the Select and have placed a period in front of Cells and Range to indicate they are subject to the With statement. Don’t worry if you don’t know what I am talking about; once you have finished your tutorial, you will.

I would normally tidy up parameters but I will leave that as an exercise for you. What I would also do is replace the column letters with names. If you bury column letters or numbers in your code, you have a real problem when a new column is added or existing columns are re-sequenced. I would replace the above with one of the following:

  Const ColClsStud As String = "B"
  Const ColClsDay As String = "G"
  Const ColClsStart As String = "L"

  With Worksheets("Classes")

    .Cells.Sort Key1:=.Range(ColClsStud & "2"), Order1:=xlAscending, _
                Key2:=.Range(ColClsDay & "2"), Order2
                                                    

                                                                                                                REPLY
                                                        
                                                    
                                                    



                                                        

                                                            0
                                                                views   0
                                                                    likes    
                                                                0 shares
                                                                                                                                


    

         Facebook
        
         Twitter
        
        
         Linked In 
        
          WhatsApp