As a former programmer I like clean, maintainable and documented code.
As a project manager I have to do complex excels from time to time and want to write "clean" formulas in the same way I wrote programs.
(How) Can I add "comments" into a (multiline) formula?
(How) Can I "name" a (cell-relative) formula and reuse it? (e.g. write it as a vb (or even f#) function with parameters)
Example1: Instead of
=IF(AND(L$11+(L$13-1)*7>=$C15;L$11+(L$13-1)*7<$D15);VLOOKUP($A15;RessourcePlan;12+$E15;WRONG);0)
I'd like to write:
// check if this columns (L) date is inbetween startdate (Col C) and enddate (Col D)
=IF (AND(L$11+(L$13-1)*7>=$C15;L$11+(L$13-1)*7<$D15);
// then select the the utilisation (12+E15) for the resp. team from the resource plan
VLOOKUP($A15;RessourcePlan;12+$E15;WRONG);
// else default to 0
0
) //ENDIF
And instead of example1 I might write a user defined function (UDF)
Function Utilization(thisDate As Date, task As String) As Double
... // clean VB or F# code
End Function
And then write
=Utilization(L11,A15)
Being a former functional programmer, I came up with the user defined functions given below for my problem.
Note the following points:
- I only write "pure" mathematical functions that map in-params to
results, no state-changes, input/output, for-loops or similar
involved. (At least I have this in mind, "Let" and VBA is obviously not "pure"..)
- cell referencing and auto-completion is to be done in the sheets (excel is strong here)
- constants are defined as named ranges in a special sheet called "Constants"
Given a task with an estimated effort and start and end-date, how many ppl do I need? => 100% means one person needs to work on this full-time (assuming she is working x daysPerWeek, as fixed in the constants)
Public Function util(ByVal startDate As Date, ByVal endDate As Date, ByVal estimation As Double) As Double
Dim duration As Integer
Let duration = DateDiff("d", startDate, endDate)
Dim weeks As Double
Let weeks = duration / 7
Dim pdays As Integer
Let pdays = weeks * [daysPerWeek]
util = estimation / pdays
End Function
Since I have many tasks and many teams working on them, I'd like to know how many ppl from a team I need for a task. The following function reuses the function from above. Note the readable names for the complex Vlookup-calls and the reference to "BaseLine1" which is my actual project plan. It will be very easy to extend this to get other scenarios etc.
Public Function currentUtil(currentDate As Date, id As String, team As Integer) As Double
Dim result As Double
Let startDate = Application.WorksheetFunction.VLookup(id, [BaseLine1], 11, 0)
Let endDate = Application.WorksheetFunction.VLookup(id, [BaseLine1], 12, 0)
Let estimation = Application.WorksheetFunction.VLookup(id, [BaseLine1], 5 + team, 0)
If (currentDate >= startDate And currentDate < endDate) Then
result = util(startDate, endDate, estimation)
Else
result = 0
End If
currentUtil = result
End Function
Finally I use it in the following way: I have a main table with all tasks including their dates and estimated efforts per team. In another sheet, I have the weeks on the horizontal and the tasks per team on the vertical. In the cells I use the function "currentUtil" and use autocompletion and conditional formatting to get a analytical view on the plan.
Finally I have the same result as before, but in a much more convenient and maintainable form.
