There are entire books — entire bookstores — dedicated to the truisms of business. One truism that doesn't get written about very often is this: before it gets big enough to have IT resources, every enterprise will run on a clutter of VBA-ridden Excel spreadsheets masquerading as line-of-business software. And, as surely as the sun rises in the East, such a company's first few developers will spend most of their time maintaining these. Sometimes those first few developers, with no one on the hiring squad able to vet their capabilities, will even create a few tabular terrors of their own. Dash found himself opening one of these, an Excel workbook created by a long-gone predecessor whose resume had claimed "extreme familiarity with Microsofts XL", to be faced with this:

``````
Private Sub Worksheet_Calculate()

'...snip...

If Not Intersect(wsTS.Range("Q11:Q35"), wsTS.Range("Q11:Q35")) Is Nothing Then
For r = 11 To 35
If wsTS.Cells(r, "N") <> "" Then
If wsTS.Cells(r, "Q") < 1 And wsTS.Cells(r, "E") <> "std" Then
wsTS.Cells(r, "Q").NumberFormat = "0.00"
ElseIf wsTS.Cells(r, "Q") < 10 And wsTS.Cells(r, "Q") >= 1 And wsTS.Cells(r, "E") <> "std" Then
wsTS.Cells(r, "Q").NumberFormat = "0.0"
ElseIf wsTS.Cells(r, "Q") >= 10 And wsTS.Cells(r, "E") <> "std" Then
wsTS.Cells(r, "Q").NumberFormat = "0"
ElseIf wsTS.Cells(r, "E") = "std" Then
wsTS.Cells(r, "Q").NumberFormat = "0.0"
End If
End If
Next r
End If

If wsTS.Range("D42") < 1 Then
wsTS.Range("D42").NumberFormat = "0.00"
ElseIf wsTS.Range("D42") < 10 And wsTS.Range("D42") >= 1 Then
wsTS.Range("D42").NumberFormat = "0.0"
ElseIf wsTS.Range("D42") >= 10 Then
wsTS.Range("D42").NumberFormat = "0"
End If

If wsTS.Range("G42") < 1 Then
wsTS.Range("G42").NumberFormat = "0.00"
ElseIf wsTS.Range("G42") < 10 And wsTS.Range("G42") >= 1 Then
wsTS.Range("G42").NumberFormat = "0.0"
ElseIf wsTS.Range("G42") >= 10 Then
wsTS.Range("G42").NumberFormat = "0"
End If

If wsTS.Range("J42") < 1 Then
wsTS.Range("J42").NumberFormat = "0.00"
ElseIf wsTS.Range("J42") < 10 And wsTS.Range("J42") >= 1 Then
wsTS.Range("J42").NumberFormat = "0.0"
ElseIf wsTS.Range("J42") >= 10 Then 2
wsTS.Range("J42").NumberFormat = "0"
End If

If wsTS.Range("O42") < 1 Then
wsTS.Range("O42").NumberFormat = "0.00"
ElseIf wsTS.Range("O42") < 10 And wsTS.Range("O42") >= 1 Then
wsTS.Range("O42").NumberFormat = "0.0"
ElseIf wsTS.Range("O42") >= 10 Then
wsTS.Range("O42").NumberFormat = "0"
End If

If wsTS.Range("Q42") < 1 Then
wsTS.Range("Q42").NumberFormat = "0.00"
ElseIf wsTS.Range("Q42") < 10 And wsTS.Range("Q42") >= 1 Then
wsTS.Range("Q42").NumberFormat = "0.0"
ElseIf wsTS.Range("Q42") >= 10 Then
wsTS.Range("Q42").NumberFormat = "0"
End If

If wsTS.Range("D44") < 1 Then
wsTS.Range("D44").NumberFormat = "0.00"
ElseIf wsTS.Range("D44") < 10 And wsTS.Range("D44") >= 1 Then
wsTS.Range("D42").NumberFormat = "0.0"
ElseIf wsTS.Range("D44") >= 10 Then
wsTS.Range("D44").NumberFormat = "0"
End If

If wsTS.Range("G44") < 1 Then
wsTS.Range("G44").NumberFormat = "0.00"
ElseIf wsTS.Range("G44") < 10 And wsTS.Range("G44") >= 1 Then
wsTS.Range("G42").NumberFormat = "0.0"
ElseIf wsTS.Range("G44") >= 10 Then
wsTS.Range("G44").NumberFormat = "0"
End If

If wsTS.Range("J44") < 1 Then
wsTS.Range("J44").NumberFormat = "0.00"
ElseIf wsTS.Range("J44") < 10 And wsTS.Range("J44") >= 1 Then
wsTS.Range("J44").NumberFormat = "0.0"
ElseIf wsTS.Range("J44") >= 10 Then
wsTS.Range("J44").NumberFormat = "0"
End If

If wsTS.Range("O44") < 1 Then
wsTS.Range("O44").NumberFormat = "0.00"
ElseIf wsTS.Range("O44") < 10 And wsTS.Range("O44") >= 1 Then
wsTS.Range("O44").NumberFormat = "0.0"
ElseIf wsTS.Range("O44") >= 10 Then
wsTS.Range("O44").NumberFormat = "0"
End If

If wsTS.Range("Q44") < 1 Then 3
wsTS.Range("Q44").NumberFormat = "0.00"
ElseIf wsTS.Range("Q44") < 10 And wsTS.Range("Q44") >= 1 Then
wsTS.Range("Q44").NumberFormat = "0.0"
ElseIf wsTS.Range("Q44") >= 10 Then
wsTS.Range("Q44").NumberFormat = "0"
End If

If wsTS.Range("D46") < 1 Then
wsTS.Range("D46").NumberFormat = "0.00"
ElseIf wsTS.Range("D46") < 10 And wsTS.Range("D46") >= 1 Then
wsTS.Range("D46").NumberFormat = "0.0"
ElseIf wsTS.Range("D46") >= 10 Then
wsTS.Range("D46").NumberFormat = "0"
End If

If wsTS.Range("G46") < 1 Then
wsTS.Range("G46").NumberFormat = "0.00"
ElseIf wsTS.Range("G46") < 10 And wsTS.Range("G46") >= 1 Then
wsTS.Range("G46").NumberFormat = "0.0"
ElseIf wsTS.Range("G46") >= 10 Then
wsTS.Range("G46").NumberFormat = "0"
End If

If wsTS.Range("J46") < 1 Then
wsTS.Range("J46").NumberFormat = "0.00"
ElseIf wsTS.Range("J46") < 10 And wsTS.Range("J46") >= 1 Then
wsTS.Range("J46").NumberFormat = "0.0"
ElseIf wsTS.Range("J46") >= 10 Then
wsTS.Range("J46").NumberFormat = "0"
End If

If wsTS.Range("O46") < 1 Then
wsTS.Range("O46").NumberFormat = "0.00"
ElseIf wsTS.Range("O46") < 10 And wsTS.Range("O46") >= 1 Then
wsTS.Range("O46").NumberFormat = "0.0"
ElseIf wsTS.Range("O46") >= 10 Then
wsTS.Range("O46").NumberFormat = "0"
End If

If wsTS.Range("Q46") < 1 Then
wsTS.Range("Q46").NumberFormat = "0.00"
ElseIf wsTS.Range("Q46") < 10 And wsTS.Range("Q46") >= 1 Then
wsTS.Range("Q46").NumberFormat = "0.0"
ElseIf wsTS.Range("Q46") >= 10 Then
wsTS.Range("Q46").NumberFormat = "0"
End If
``````

The workbook had worked just fine for years, Dash was told, so he wasn't to "mess it up" — just add the one little feature that the business desired. Instead he decided to introduce the old workbook to an exciting new feature built right into Excel since at least the Nineties: conditional formatting. No longer running that script every single time every single cell containing a number was changed, the workbook's performance improved enormously.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!