Title: Use VBA to make a Gantt chart showing work schedules

This example shows how to use VBA (Visual Basic for Applications) in an Excel workbook to draw a Gantt chart showing when employees are scheduled to work. This isn't C# programming, but you may find it useful. (And I don't have a VBA blog right now where I can post it.)

You could do something similar in C# or another general-purpose programming language, but I decided to use Excel to take advantage off its spreadsheet features.

A Gantt chart uses colored bars to show the duration of tasks. In this example, it uses the bars to show when employees are working. That makes it easy to see when there are few, many, or no people working at the same time.

The example also demonstrates how to calculate elapsed time for employees. The following sections explain how to calculate and display elapsed time, and how to build the Gantt chart.

Calculating Elapsed Time

The entries in column D calculate the difference between columns C and B, but it uses a special rule. If the employee works for more than 5 hours, then we provide a half hour break.

To calculate elapsed time, you can simply subtract the start time from the finish time. Unfortunately the result is stored as a time. To convert it into a number, you multiply it by 24.

To add in the 30 minute break if the shift lasts more than five hours, you can use the Excel IF function.

The following statement shows the equation in cell D5.

=(C5-B5)*24-IF((C5-B5)*24>5,0.5,0)

The first part of this equation, (C5-B5)*24, subtracts the start time B5 from the end time C5 and multiples the result by 24 to convert the result into the number of hours.

Next, the equation uses the IF function. That function returns one of two values depending on whether a Boolean condition is true. In that sense it's similar to C#'s conditional operator (aka ternary operator) :?. In this example, the Boolean condition is (C5-B5)*24>5. This calculates the elapsed time again and checks whether it is greater than 5. If this sub-expression is true, then IF returns the value 0.5. If the sub-expression is false, the If function returns 0.

The expression then subtracts the result returned by IF from the elapsed time.

The final result of the equation is the elapsed time minus 0.5 hours if the elapsed time is greater than 5 hours.

Drawing the Gantt Chart

The example uses several VBA routines to work with Gantt charts. The following sections describe those methods.

Making Buttons

If you haven't created buttons in Excel before, here's how. On the Developer tab, open the Insert dropdown and select the Button item. Then click and drag to position the button. At that point, the Assign Macro dialog shown on the right appears.

The dialog is a bit confusing. If you just click OK, it does not create a macro associated with the button. Instead you should either select a macro from the list and click OK, or enter a name for a new macro in the upper text box and click New. (Or you can record a macro, but I'm not going to talk about that.)

For this example, I entered names for new macros and clicked New.

At this point, Excel creates the button and the empty macro. It gives the button a default caption such as Button7. To change the caption, right-click the button (if you left-click it, it will fire) and select Edit Text. Type the new caption and click off of the button to finish.

The context menu that you get when you right-click the button contains some other useful commands. In particular you can use the Assign Macro command to change the macro associated with the button. You can also use the Format Control command to change the button's text color, font, and other format properties.

Removing Bars

When you click the worksheet's Remove Bars button, the following code executes.

Sub RemoveBars() Dim sheet As Worksheet Dim shp As Shape ' Remove existing rectangles. Set sheet = Application.ActiveSheet For Each shp In sheet.Shapes If shp.Type = msoShapeRectangle Then shp.Delete Next shp End Sub

This code gets the active worksheet. It then loops through that sheet's Shapes collection. If a shape is a rectangle, the code calls its Delete method.

If you're interested in how different languages work, there are several differences between how this VBA code works and how similar C# code would work.

• Variables must be declared before they are used. In C# you can declare and initialize variables in the same statement as in int number = 1337;. In VBA you must declare variables separately before you use them.
• You must use the Set keyword to assign reference variables. C# uses the same syntax for reference and value types.
• The syntax is different. Obviously the syntax is very different. One thing to note here is that the Next statement may include the name of the looping variable, in this case shp, if you like, but it is not required. You cannot include anything else there, however, just the name of the looping variable or nothing.

One other issue worth mentioning is that the code modifies the Shapes collection as it loops through it. In general modifying a collection while you are looping through it is a bad idea. It can be confusing and may cause problems. For example, C# does not allow you to add or remove items while you are looping through a collection.

In VBA you can actually add and delete objects while you loop through a collection, but the results can be strange. If you add or remove an item before the one that you are currently looking at, the items in the collection are renumbered so it's not clear what will happen. In short, don't do that.

However, this example loops through the Shapes collection and calls each item's Delete method. Somehow that removes the item from the worksheet and the collection without messing up the collection. I'm actually not sure exactly how it does that. It could be that it is using a snapshot of the collection for looping purposes.

Making One Bar

The following MakeBar subroutine creates a bar for a row in the worksheet. It's a bit intimidating because it's long, but it's really not that complicated. It also makes the other methods easier.

Sub MakeBar(ByRef sheet As Worksheet, ByVal row_num As Integer) Const first_hour As Single = 4 Const last_hour As Single = 18 Const left_col As Integer = 5 Const right_col As Integer = 11 Const start_col As Integer = 2 Const end_col As Integer = 3 Dim start_hour As Single Dim end_hour As Single Dim x1 As Integer Dim x2 As Integer Dim dx As Single Dim y1 As Integer Dim bar_x As Integer Dim bar_y As Integer Dim bar_width As Integer Dim bar_height As Integer Dim new_bar As Shape ' See if this person has hours. If sheet.Cells(row_num, start_col).Value = "" Then Exit Sub If sheet.Cells(row_num, end_col).Value = "" Then Exit Sub ' Get the hour range. start_hour = sheet.Cells(row_num, start_col) * 24 end_hour = sheet.Cells(row_num, end_col) * 24 ' See where the bar should begin and end. x1 = sheet.Cells(row_num, left_col).Left x2 = sheet.Cells(row_num, right_col).Left + sheet.Cells(row_num, right_col).Width dx = (x2 - x1) / (last_hour - first_hour) bar_x = x1 + dx * (start_hour - first_hour) bar_width = dx * (end_hour - start_hour) bar_y = sheet.Cells(row_num, left_col).Top bar_height = sheet.Cells(row_num, left_col).Height Set new_bar = sheet.Shapes.AddShape(msoShapeRectangle, bar_x, bar_y, bar_width, bar_height) new_bar.TextFrame2.TextRange = _ HourTo12HourTime(start_hour) & _ " - " & _ HourTo12HourTime(end_hour) new_bar.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter new_bar.TextFrame2.VerticalAnchor = msoAnchorMiddle new_bar.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0) new_bar.Fill.ForeColor.RGB = RGB(255, 200, 200) new_bar.Line.ForeColor.ObjectThemeColor = msoThemeColorText1 new_bar.Line.Weight = 1 End Sub

The code starts by defining some constants.

The values first_hour and last_hour are the first and last hours that the program will draw. In this example, those are 4 (4:00 am) and 18 (6:00 pm).

The left_col adn right_col values indicate the first and last worksheet columns where the bars will be drawn. In this case, those are columns 5 and 11. That means the time 4 (4:00 am) corresponds to the left edge of column 5, and the time 18 (6:00 pm) corresponds to the right edge of column 11.

The start_col and end_col values indicate the columns where the employees' start and end times are stored.

Next the code defines the variables that it will use later. It then checks the start and end hours for this row. If either of those values is blank, the subroutine returns without doing anything interesting.

The code then gets the row's start and end times. Notice that it retrieves the times and multiplies them by 24 to convert Excel's time format into hours after midnight.

Next the subroutine calculates x1 and x2, the X coordinates that give the bar's limits. The value x1 gives the left edge of the leftmost drawing column, and x2 gives the right edge of the rightmost drawing column.

The code then calculates a scale factor dx to map elapsed hours to the X coordinates. It uses that value to calculate the left edge of the bar bar_x, the bar's width bar_width, the Y coordinate of the bar's top bar_y, and the bar's height bar_height.

The program then passes those values into the Shapes collection's AddShape method to create the new rectangle. It sets the new shape's TextRange value to the text that we want to display inside the bar. To do that, the code calls the HourTo12HourTime method described shortly.

The code formats the bar's text so it is centered both vertically and horizontally, and it makes the text black. (The default is white text in the rectangle's lower left corner.) The subroutine finishes by setting the rectangle's background color, theme color, and line weight. You can experiment with those values if you want to use other colors.

HourTo12HourTime

The HourTo12HourTime helper subroutine converts a numeric time value into a string with the format 4:30p. (I wrote this to produce a more concise time format so times would fit better inside the bars.)

The following code shows the HourTo12HourTime subroutine.

Function HourTo12HourTime(ByVal the_hour As Single) As String Dim the_date As Date Dim am_pm As String If the_hour = 12 Then am_pm = "n" ElseIf (the_hour = 0) Or (the_hour = 24) Then am_pm = "m" ElseIf the_hour < 12 Then am_pm = "a" Else am_pm = "p" End If If the_hour = 0 Then the_hour = 12 ElseIf the_hour >= 13 Then the_hour = the_hour - 12 End If the_date = CDate(the_hour / 24) HourTo12HourTime = Format$(the_date, "h:mm") & am_pm End Function

The code first checks the time and sets the variable am_pm to n (for noon), m (for midnight), a (for AM), or p (for PM) accordingly.

Next the code checks for special hours. If the hours is 0, then it represents midnight. The code changes the hour to 12 so the result will be 12m instead of 0m.

The value midnight could be confusing in many programs because it may not be clear whether you're talking about midnight at the beginning of the day or at the end of the day. In this example, the context should be obvious from the column that contains the value. The elapsed time calculation still depends on you using the correct value, however. You should use 0:00 for start times and 24:00 for end times.

If the time greater than or equal to 13, then it represents a time that is 1:00 PM or later. In that case, the code subtracts 12 so it can display the result using a 12-hour clock. The code does not do this if the time is between 12:00 and 1:00. For example, if the time is 12:45, then we want to leave it at that rather than subtracting 12 to get 0:45.

Making Many Bars

The following MakeBars subroutine calls MakeBar to create all of the Gantt chart bars.

Sub MakeBars() Const first_row As Integer = 2 Const last_row As Integer = 75 Dim i As Integer Dim sheet As Worksheet Dim shp As Shape ' Remove existing rectangles. Set sheet = Application.ActiveSheet For Each shp In sheet.Shapes If shp.Type = msoShapeRectangle Then shp.Delete Next shp ' Make the rows. For i = first_row To last_row MakeBar sheet, i Next i End Sub

This subroutine first defines constants to hold the indexes of the first and last rows that could contain employee hours. The code then simply loops through those rows and calls the MakeBar subroutine for each.

Other Calculations

The workbook performs a couple of other simpler calculations. If you look again at the picture at the top of this post, you can see that the workbook adds up the hours for each week. That makes it easier to keep tabs on the total amount of time spent by all employees.

The bottom of the worksheet also adds up the total hours for each employee for the week, as shown in the following picture.

Those sums are straightforward. For example, the formula for adding up Adam's hours in cell B78 is the following.

=SUM(D4,D14,D24,D34,D49,D59,D69)

The total at the bottom simply adds all of the employees' weekly totals.

Conclusion

As I said earlier, you could do something similar in C# or some other language. Feel free to do so. That will give more control over the layout. For example, you can probably arrange things differently to make the chart fit on a single page. The example workbook is set up to use two pages, mostly to keep the font large enough to read easily.

One very unusual feature of this post is that the download includes an executable file. Because this example uses VBA macros, the example is a macro-enabled workbook. In general, you should not open macro-enabled workbooks unless they come from a trusted source. If you must use one, including this one, you should open it with the macros disabled first and look at the macro code to see if there's anything suspicious in there before you run the macros.

That being said, download the example, verify that the macros are safe, and the experiment.

Download the example to experiment with it and to see additional details.