Creating a Worksheet Map
In this document I describe how to create a VBA utility that generates a map of the active worksheet. The map is generated on a new worksheet, and it consists of color-coded cells that let you quickly identify values, text, and formulas.
The figure below shows an example of a such a map. Cells that contain text are green, those than contain a numeric value are colored yellow, and cells that contain formulas are colored red. Such a map can help you spot potential errors. For example, if one formula in a block of formulas has been overwritten by a value, that cell will stand out in the map view.
The QuickMap Subroutine
The subroutine that generates the worksheet map is listed below. If you'd like to use this utility, just copy the code and paste it to a VBA module. Then, activate a worksheet and execute the QuickMap subroutine.
Sub QuickMap()
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
' Create object variables for cell subsets
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells _
(xlFormulas, xlNumbers + xlTextValues + xlLogical)
Set TextCells = Range("A1").SpecialCells(xlConstants, xlTextValues)
Set NumberCells = Range("A1").SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
' Add a new sheet and format it
Sheets.Add
With Cells
.ColumnWidth = 2
.Font.Size = 8
.HorizontalAlignment = xlCenter
End With
Application.ScreenUpdating = False
' Do the formula cells
If Not IsEmpty(FormulaCells) Then
For Each Area In FormulaCells.Areas
With ActiveSheet.Range(Area.Address)
.Value = "F"
.Interior.ColorIndex = 3
End With
Next Area
End If
' Do the text cells
If Not IsEmpty(TextCells) Then
For Each Area In TextCells.Areas
With ActiveSheet.Range(Area.Address)
.Value = "T"
.Interior.ColorIndex = 4
End With
Next Area
End If
' Do the numeric cells
If Not IsEmpty(NumberCells) Then
For Each Area In NumberCells.Areas
With ActiveSheet.Range(Area.Address)
.Value = "N"
.Interior.ColorIndex = 6
End With
Next Area
End If
End Sub
How it Works
The subroutine first checks to make sure the active sheet is a worksheet. If not, there's a quick exit with no further action. Next, it creates three object variables by using the SpecialCells method to identify the various cell types. The SpecialCells method is very useful. If you're not acquainted with it, I urge you to check it out in Excel's online help file. Notice the use of On Error Resume Next. This is to avoid the error that occurs if no cells qualify -- for example, if the worksheet has no formulas.
Next, the subroutine adds a new worksheet, reduces the cell width, and sets the horizontal alignment to center. This step is just cosmetic. The sub then turns off screen updating to speed things up a bit.
The next three blocks of code process the cells. If no cell qualify, the object variable is Empty, so the sub tests for this. Then, the routine loops through each Area in the Range object and formats the cell. You can easily customize this part of the subroutine to apply different formatting.
NOTE: The Power Utility Pak includes a much more sophisticated version of this utility, along with several other auditing tools.