Clean Code in Excel?

Question

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.

1. (How) Can I add "comments" into a (multiline) formula?

2. (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)

Answer 1

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.