Recently I needed to create a stop and resume process for an application at work. This is probably pretty basic stuff for most developers but took me some time to figure out. To do so, I first added a "Show Form" button to a worksheet. When the Show Form button is hit, it calls a userform that contains four buttons that control a simple looping procedure. The first button starts the procedure, the second stops it, the third button resumes it, and the fourth exits the procedure altogether. As the procedure loops through its values, those values are recorded in cell A1.

The procedure works by using "DoEvents" to read the value of the iStop variable. When the Go button is hit, the iStop variable is set to False. When the Stop button is hit, iStop is set to True and the procedure is forced to exit the loop.

The userform is below:

Option Explicit
Public iLoop As Integer
Public iStop As Boolean
Public Rng As Range

Sub DoLoop()
    Do Until iLoop = 15000
        Rng.Value = iLoop
        iLoop = iLoop + 1
        DoEvents
        If iStop = True Then Exit Sub
    Loop
    Rng.Value = 0
    Unload Me
End Sub

The code tied to the Go button is:

Private Sub btnGo_Click()
    iStop = False
    Set Rng = ActiveSheet.Range("A1")
    iLoop = 1
    Call DoLoop
End Sub

The code tied to the Stop button is:

Private Sub btnStop_Click()
    iStop = True
End Sub

The code tied to the Resume button is:

Private Sub btnResume_Click()
    iStop = False
    Call DoLoop
End Sub

Aned finally the code tied to the Quit button is:

Private Sub btnQuit_Click()
    iStop = True
    Rng.Value = 0
    Unload Me
End Sub

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.