Creating A Worksheet Map

Category: General VBA | [Item URL]

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.

map example

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: My Power Utility Pak add-in includes a much more sophisticated version of this utility.


Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy