Using VBA to Create a Worksheet Map
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 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 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.