The VBA SpecialCells method combined with background colors can be a simple and effective method to perform high-level worksheet audits. The process works by using a macro to apply colors to individual cells flagged as per the SpecialCells criteria.

For example, in Excel 2003 when you go to Edit -> Go To... -> Special, the following dialog box appears:

Options are available that allow you to select cells with constants, formulas, blanks, etc.

The same can be done with VBA and the SpecialCells method. The advantage of using VBA is that you can apply background colors to make the cells quickly visible. Additionally, there are even more options available via VBA than what you see in the dialog box.

To explain, you're boss asks you to audit the accuracy of a simple financial statement. To do so, start by making sure that formulas appear where they logically should. A simple VBA procedure using the SpecialCells method as presented below can be used to color all of the cells with formulas in the desired range yellow (yellow is tied to the index value number "6" below - this may be different on your machine.)

Sub Flag_Formulas()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeFormulas).Interior.ColorIndex = 6
End Sub

After running the macro, the financial statement looks like this:

A quick look at the statement reveals that something is wrong in cell H19. Instead of seeing a cell with a formula it looks like somebody hard-coded a number.

This same process can be applied to other types of cells. The macro below can be used to flag hard-codes numbers:

Sub Flag_Hard_Coded_Numbers()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeConstants, 1).Interior.ColorIndex = 6
End Sub

This macro flags text entries:

Sub Flag_Text()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeConstants, 2).Interior.ColorIndex = 6
End Sub

For colors, if you want to specify an RGB number rather than an index number you can use a statement like:

Rng.SpecialCells(xlCellTypeConstants, 2).Interior.Color = RGB(255, 255, 204)

Finally, if you look up "SpecialCells" in Excel's VBA help there are even more options available. You can flag cells with comments, validation, same formatting, and even more.

In our accounting shop, we build our budgets in Excel but submit them in another software called Hyperion. Hyperion requires us to submit in whole numbers. To do so, we need to round every formula in the final Hyperion load so that the budget foots and crossfoots.

Before submission, I'll use the following macro to wrap the ROUND function around every formula:

Sub Add_Rounding()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Round")) = 0 Then
            Rng.Formula = "=round(" & cellFormula & ",0)"
        End If
    
    Next Rng

End Sub

The macro works by:

• Looping through each cell in the range.
• Uses the Special Cells method to identify only those cells with formulas.
• Sets up a string called "cellFormula" which is a picture of the formula.
• Uses the InStr function to look at the cellFormula string and identify if it already has the ROUND function included. If so, the macro goes on to the next cell. If not, it adds the ROUND function to the formula.

You can use the same type of logic to apply the ISERROR function to a range of formulas:

Sub Add_ErrorTrap()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Error")) = 0 Then
            Rng.Formula = "=if(Iserror(" & cellFormula & "),0," & cellFormula & ")"
        End If
    
    Next Rng

End Sub

I recently found the following procedure posted as part of a comment at the Excel-Tips Blog. The procedure makes all of the cells in the worksheet grid square. It might come in handy some time.

Sub SquareCells()
    Dim i As Integer
    For i = 1 To 4
        With ActiveSheet
            .Columns.ColumnWidth = _
            .Columns("A").ColumnWidth / .Columns("A").Width * _
            .Rows(1).Height
        End With
    Next
End Sub

Unfortunately Excel's goal seeking feature forces you to hand-enter a value into the "To value" field of the input box. You can't directly tie this input to a cell like you can with the "Set cell" and "By changing cell" inputs (see the screenshot below).

This can make things difficult especially if you're trying to seek very large values or values that that are carried out to many decimal points.

The following procedure allows you to get around this limitation. First, enter the cell addresses for the variables called Rng1, Rng2, and Rng3. Note that a formula must appear in Rng1 and a value must be entered into Rng3. Next, run the procedure.

Sub GoalSeek()

    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range

    On Error GoTo errHandler:

    'Set the value in this cell
    Set Rng1 = ActiveSheet.Range("C4")

    'Equal to the value in this cell
    'Note:  This cell must contain a formula
    Set Rng2 = ActiveSheet.Range("C7")

    'By changing the value in this cell
    Set Rng3 = ActiveSheet.Range("C2")

    If Rng1.HasFormula = True Then
        If IsNumeric(Rng3) = True Then
            Rng1.GoalSeek Goal:=Rng2, ChangingCell:=Rng3
        End If
    End If

    Exit Sub

errHandler:
    MsgBox "Procedure failed - please check inputs."

End Sub

To add images of embedded charts to this weblog, I use the VBA procedure below. To run the procedure, first make sure the specifications are correct. You can make changes to the chart height, width, file name, and file path. When all of the inputs are correct, click on (activate) the embedded chart and run the macro.

Sub ExportChart()

    Dim Cht As ChartObject
    Dim Path As String
    Dim FileName As String
    
    Set Cht = ActiveChart.Parent

    Cht.Height = 210
    Cht.Width = 336
    FileName = "2008032301.gif"
    Path = "C:\Program Files\"
    
    ActiveChart.Export Path & FileName

End Sub

Please note that the height and width are not really needed. I've added this code only because I like to make sure all of the charts are exactly the same size.

Many companies produce reports at a top-level, department, division, and/or cost center level. When Excel is used to build these reports, the normal practice seems to be to create a new sheet for each department, division, and/or cost center. Each sheet contains the data and calculations for that area. A single top-level reporting sheet is then created to which each department, division, and/or cost center sheet links to. When it's all over and done the workbook can become very large, prone to error, and difficult to audit.

An easier and more accurate approach is to use the combination of lookup formulas and a VBA loop to build the report. In this example, only four sheets in the Excel file are illustrated (it could be only one if designed that way). The first sheet, called "dBase", contains the raw data for each department, division, and/or cost center. The second sheet, called "Calcs", contains a single set of calculations needed to build the final report. The third sheet, called "Load", contains a summary of the data from the Calcs sheet needed for the final report. The final sheet, called "Report", is the final report.

The example begins with a screenshot of the sheet called "dBase" (for database). The "dBase" sheet contains ten departments. The Commerical / Contract and Government volumes for each department is provided for the years 2007 and 2008. The final report requires that you show the components of payer mix variance for each department.

The next screenshot shows the sheet called "Calcs". The blue cell (C2) contains the name of the department. The yellow cells contain lookup formulas that reference the "dbase" sheet.

For example, the lookup formula in cell E8 is:

=VLOOKUP($C$2,dBase!$A$3:$E$12,2,FALSE)

The lookup formula in cell F8 is:

=VLOOKUP($C$2,dBase!$A$3:$E$12,4,FALSE)

The next screenshot shows the sheet called "Load". The purpose of this sheet is to reference all of the values that need to appear in the final report into one row of data.

The data that appears in the "Load" sheet is referenced to the green cells in the "Calcs" sheet.

Finally, a VBA procedure is run to create the report. A loop is created that, when combined with the lookup formulas, does the following:

• The VBA procedure loads the name of the department into cell C2 of the "calcs" sheet.
• The lookup formulas in the calcs sheet reference the data from the "dBase" sheet for that particular department.
• The payer mix calculations are performed.
• The payer mix data is referenced from the "calcs" sheet into row of data in the "Load" sheet.
• The data from the "Load" sheet is copied as values into the final report.

In this example, the procedure described above is performed ten times - once for each department as referenced in the range B8:B17 of the "Report" sheet. The VBA code that performs the work is below:

Sub BuildReport()
    Application.ScreenUpdating = False
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    Dim Rng4 As Range
    Set Rng2 = Sheets("Calc").Range("C2")
    Set Rng3 = Sheets("Load").Range("B3:G3")
    Set Rng4 = Sheets("Rpt").Range("C8:H8")
    For Each Rng1 In Sheets("Rpt").Range("B8:B17")
        Rng2.Value = Rng1.Value
        Rng3.Copy
        Rng4.PasteSpecial xlPasteValues
        Set Rng4 = Rng4.Offset(1, 0)
    Next Rng1
    Sheets("Rpt").Range("A1").Select
End Sub

After the macro has been run, the final report looks like this:

The advantages of using lookup formulas and VBA loops to build the report are these:

• Only one set of calculations (the "Calcs" sheet) needs to be audited for accuracy. This is especially advantageous as the number of departments, division, etc. needed on the final report grows.
• The audit risk concerning the workbook is reduced due to less sheets and data ranges needing to be proofed.
• The physical size of the workbook is reduced which make it easier to maintain and transfer to others via email, web, etc.
• The VBA procedure runs fast and the report is produced almost instantaneously.

Potential disadvantages of using lookup formulas and VBA loops to build the report are these:

• If you need to pass the workbook on to someone that has no experience with VBA, the issue of who can maintain the workbook may come up.
• If your manager is clueless when it comes to lookups or VBA, he or she may prefer the "many sheets and calculations" approach so they can understand it (runs hand-in-hand with the first bullet).