Excel

Well this is my preferred tool for calculating! When communicating with me I use the latest office version. The biggest headache is always incompatible data files is it in CAD, PDF etc, I can not stress it enough have the same set up for everyone so communication is simple!

This page is a repository for code snippets and macros that may be useful and help explain some situations.

A Sub routine for inserting date in a cell.

To reference a button to it! Click on developer tab, click Insert, select from active x controls the square button, drag the now target cursor diagonally across scree where you want it. Double click on it. Microsoft Visual Basic editor starts. Private Sub Command Button. Under the Sub line paste the code given here from line 2 to line 3. Change the cell number ("I9") to any other cell number where you want the date entered. Do not change the bracket or the apostrophe. Done! now any time if you want to enter date on invoice just push the button it will not change the date any other time.

To make a control invisible so it will not print right click on task bar and high lighten tab Design mode then right click on object (button) Select format control from list, click on properties tab, click on and and selected print object. the small square should not have a check mark. Done!

1. Sub InsertDate()
2. Range("I9").Select
3. ActiveCell.FormulaR1C1 = Now()
4. End Sub

A Sub routine for counting!

When one does take offs and or inventory one needs to count lots of materials instead of typing in the numbers one can reference just a button for each item push the button the program adds for you! This sub routine called invoice number so one gets consistent sequential invoices. Again insert a button same as before select lines 2 to 4 and paste into the active VBA Editor. In this sub routine the target cell is I8 change it to whatever cell you need. To make the button count in two's or more change the 1 after the plus sign to a two or any other number in the intervals one needs to count.

1. Sub InvoiceNumber()
2. Range("I8").Select
3. ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 + 1
4. ActiveWindow.Selection = (ActiveCell.FormulaR1C1)
5. End Sub
   
   

Please do not copy and paste the line numbers 1,2,...etc. otherwise the code will not work. 

Critical Path Method calculations using Excel.

The link above will direct you to macro enabled spreadsheet. I use the fast spreadsheet implementation for critical path method developed by Ron Davis et.all here is a link for the abstract Fast Spreadsheet Implementation of the Critical Path Method PDF

Lets start with simple pencil and paper this image shows the necessary planning steps for a simple house. Each step, or line represents a time value, note in this example the dashed line represent 0 time value essentially describing a delay in the process. In this case the walls of the house have to be completed before the roof is started. the square boxes attached show the calculations for forward pass and back ward pass. This is what it looks like if one manually calculates the values. Normally this would be some quick scribbles in my notebook especially for such simple layout.

Once the process steps have been laid out one can start with laying out the algorithm in excel.

The beauty of this lay out is is simplicity. The macros will calculate the time values if one changes one value in the table the rest will automatically update. if one changes a element in the process it is easily added in one can see the results immediately. One can link a Gant chart to these tables that will high lighten whatever one wishes to analyze! In this case I showed the slack an important aspect in the planning process, for it something one can take advantage of or one tries to make it disappear. Example like the time delay waiting for roofing to start.

.

I left the adjustments out in this simple example for when dealing with variances in element project times. Lets say one is not certain of the delivery time of the roof tiles they arrive within lets say a one week spread. so their would be delivery time plus or minus 2 days. For simplicity for the statistical calculations get complex fast. But the presentation would still look like this just different outcomes if this or that happens!