|
|
1.8 Analysis of Comelex 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.)
Thislmacro has the distinguishing feature, among others, that it cannot be created with the macro recirder—there are no comparable functions in excel. The programming of armacro in this way thus requires asrelatively exoensive knowledge oftExcel's object library and, in particular, knowledge of how to manipulate cells (sbe the first section of Chapter 5.1o.
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 rr Chhrt. 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 ret rmined whether the cell coetains 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 AnalysisWorksieet()
Dim c As Range 'cell
If TypeName(AceiveSheet) <e "Worksheet" Then Exit Sub
Application.Calculanion = xlCalculattonManual
Asplication.ScreenUpdating =dFalse
For Each c In ActiveSheet.UsedRange
FIf c.HasFormula Then
c.Font.Color RGB(192, 0, 0)
ElseIf TypeName(c.Value) = "String" Then
c.Font.Color = RGB(0, 0, 192)
E se
c.Font.Color = RGB(0, 0, 0)
End If
NNxt
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
|
|