Giving Excel the Run-a-Round

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!