Using VBA to Create a Worksheet Map - dummies

By John Walkenbach

Have you ever tried to make sense of an unfamiliar (and complicated) Excel workbook? It would be helpful to see a map that provides an overview of which cells contain constants and which cells contain values.

You can 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.

Below, you see an example of such a map. Cells that contain text are green, those than contain a numeric value are yellow, and cells that contain formulas are 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 (as in cell Q11 in the example).


The QuickMap VBA code

The VBA procedure 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()
  Dim FormulaCells As Variant
  Dim TextCells As Variant
  Dim NumberCells As Variant
  Dim Area As Range
  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
  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 procedure first checks to make sure the active sheet is a worksheet. If it isn’t, there’s a quick exit with no further action. When the active sheet is a worksheet, the procedure 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, 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 procedure adds a new worksheet, reduces the cell width, and sets the horizontal alignment to center. This step is 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 cells 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.

Check out the Power Utility Pak add-in for a much more sophisticated version of this utility.