1.8 Analysis of Complex Tables

As an Excel user you are frequently confronted with complex worksheets that you yourself did not create, or if you did, it was so long ago that you do not remember how you constructed them. It is generally difficult to orient yourself in such worksheets. It is unclear which cells represent the results of which inputs, which cells are the results of formulas, and so on. The tools provided by the "Auditing" toolbar are, of course, helpful, but they are not quite suitable for our initial orientation. The macro that we will present here takes over this task: It analyzes all cells in the active worksheet. Character strings will be turned blue, formulas, red. (Of course, we could also investigate other aspects of the contents of the worksheet or produce other formatting, but we have enough on our plate as it is.)

This macro has the distinguishing feature, among others, that it cannot be created with the macro recorder ”there are no comparable functions in Excel. The programming of a macro in this way thus requires a relatively extensive knowledge of Excel's object library and, in particular, knowledge of how to manipulate cells (see the first section of Chapter 5.1).

The program code begins with a test of whether the active page is a worksheet (it could be a chart). TypeName returns the name of the object type, for example, Worksheet or Chart . If a worksheet is present, then for the sake of speed, automatic recalculation and refreshing the screen are temporarily turned off. Finally, all used cells are analyzed in turn , as follows .

With HasFormula it can be simply determined whether the cell contains a formula. With TypeName(c.Value)="String" character strings are recognized. (With similar tests you can determine the presence of dates or currency values, e.g., $2.50.) For formatting purposes the Color property of the Font object of the cell being examined is altered .

 ' analyse.xls Sub AnalysisWorksheet()   Dim c As Range   'cell   If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub   Application.Calculation = xlCalculationManual   Application.ScreenUpdating = False   For Each c In ActiveSheet.UsedRange     If c.HasFormula Then       c.Font.Color = RGB(192, 0, 0)     ElseIf TypeName(c.Value) = "String" Then       c.Font.Color = RGB(0, 0, 192)     Else       c.Font.Color = RGB(0, 0, 0)     End If   Next   Application.Calculation = xlCalculationAutomatic   Application.ScreenUpdating = True End Sub