Defensive Programming in VBA

[obiron]

Hi guys, Does anyone have any tips to share on defensive programming in Excel.

Personally I always (nearly always) use an on error goto error_handler which includes things like

application.screenupdating = true
application.calculations = xlautomatic
application.displayalerts = true

What else would you put into your error handler.

Do you define public variables for application current state (e.g. application.visible, activecell, activesheet, activeworkbook, calculations etc) so that you can reset to prior values on completion of your procedure

how do you defend against unexpected values in worksheets; e.g. a rogue value beyond the end of the expected usedrange, numbers stored as text instead of numbers (screws up VLOOKUP for a start...), Column headings not being in the expected column numbers, Worksheets being left in a filtered state.

When defining variable sized ranged do you tend to go for .end(xlDown), .UsedRange or .specialcells(xlCelltypeLastCell) and why

what is your preferred range notation any why
I mainly use Range(Cells(x,y),Cells(x2,y2)) because I find it easier to substitue the row/column numbers with variable values or append modifiers such as .End() or .Offset()

Do you automtically defend against issues or only if the exceptional condition occurs?

What errors have you come across where you have ended up in a WTF! moment and how did you program around them.

 

[sous2817]

Not sure if it's proper, but I came up with two functions for the first bit:

Option Explicit
Public blnSavedScreenUpdating As Boolean
Public blnSavedEnableEvents As Boolean
Public blnSavedDisplayAlerts As Boolean
Public lngSavedCalculation As Long
Public blnSavedCancel As Boolean
Public Enum Calc
    manual = 0
    Automatic = 1
    Tables_Only = 2
End Enum

Function AppSettings(Optional blnSaveSettings As Boolean = True, _
                     Optional blnScreenUpdating As Boolean = False, _
                     Optional blnEnableEvents As Boolean = False, _
                     Optional blnDisplayAlerts As Boolean = False, _
                     Optional lngCalculation As Calc = manual, _
                    Optional blnCancel As Boolean = False)
    With Application
        If blnSaveSettings = True Then
            blnSavedScreenUpdating = .ScreenUpdating
            blnSavedEnableEvents = .EnableEvents
            blnSavedDisplayAlerts = .DisplayAlerts
            lngSavedCalculation = .Calculation
            blnSavedCancel = .EnableCancelKey
        End If
        .ScreenUpdating = blnScreenUpdating
        .EnableEvents = blnEnableEvents
        .DisplayAlerts = blnDisplayAlerts
        .Calculation = lngCalculation
        .EnableCancelKey = blnCancel
    End With
                     
End Function

Function RestoreAppSettings(Optional blnRestoreSettings As Boolean = False, _
                     Optional blnScreenUpdating As Boolean = True, _
                     Optional blnEnableEvents As Boolean = True, _
                     Optional blnDisplayAlerts As Boolean = True, _
                     Optional lngCalculation As Calc = Automatic, _
                     Optional blnCancel As Boolean = True)
    With Application
        If blnRestoreSettings = True Then
            .ScreenUpdating = blnSavedScreenUpdating
            .EnableEvents = blnSavedEnableEvents
            .DisplayAlerts = blnSavedDisplayAlerts
            .Calculation = lngSavedCalculation
            .EnableCancelKey = blnSavedCancel
        Else:
            .ScreenUpdating = blnScreenUpdating
            .EnableEvents = blnEnableEvents
            .DisplayAlerts = blnDisplayAlerts
            .Calculation = lngCalculation
            .EnableCancelKey = blnCancel
        End If
    End With

End Function

For defining variable ranges, I've grown to use Ron de Bruin's "Last" function:

Function Last(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
    Dim lrw As Long
    Dim lcol As Long

    Select Case choice

    Case 1:
        On Error Resume Next
        Last = rng.Find(what:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        Last = rng.Find(what:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.Find(what:="*", _
                       After:=rng.Cells(1), _
                       LookAt:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        On Error GoTo 0

        On Error Resume Next
        lcol = rng.Find(what:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

        On Error Resume Next
        Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
        If Err.Number > 0 Then
            Last = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0

    End Select
End Function

If I'm designing something where it's critical on how and where data should be entered, I use a user form and hide the actual data sheet. I don't trust the end user at all if I can avoid it.

More and more I use the Range(.cells(x,y),.cells(a,b)) method when dealing with ranges.

I really should be more proactive in my error checking, but I've found that using a user form really helps cut down on the "Seriously?!?!?" moments.

 

[RoryA]

FWIW, I would not use public variables for the application state stuff. A procedure should put things back as they were when it started, so local variables will handle that nicely and avoid other procedures changing the public variables.

 

[Jaafar Tribak]

If I wanted to make sure that the user's application settings are always restored, I wouldn't store them in variables in case the variables went out of scope. I would store them in cells,names,Tags,atoms ...etc so they can always be recovered upon closing.

 

[Greg Truby]

In addition to what has been mentioned I try to use named ranges + constants for the named range + scope the name to the worksheet where possible. I also attempt to reference worksheets by their codenames whenever possible instead of their Name or ordinal position (index). If the worksheet is one that I have created by code, I will use a hidden name scoped to that worksheet as a "codename" and I have functions that return a ws object based on this alternate "code name". No way I want my code failing for such a mundane reason as a user inserting, moving or renaming a worksheet; or inserting or deleting a row or column.

Public Const gc_strRngName_CustomerID As String = "ptrCustomerID", _
             gc_strRngName_ErrorCheck As String = "ptrErrorCheck"
Sub foo()
    Dim celCustID   As Excel.Range, _
        celErrCheck As Excel.Range, _
        wsSource    As Excel.Worksheet
 
 
    Set wsSource = fnFindWSByCodeName(g_wbSource, m_strSourceCodeName)
    With wsSource
        Set celCustID = .Range(gc_strRngName_CustomerID)
        Set celErrCheck = .Range(gc_strRngName_ErrorCheck)
    End With
 
End Sub

Also - anything that is going to be working on the selected range should make sure the SELECTION is actually a range.

 

[Greg Truby]

Also, you asked about hitting the last cell in a range. As far as getting the last cell in a worksheet, here is what I use:

' _____________________________________________________________________________
' fn LAST CELL
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip:  finds the bottom right corner of a rectangle drawn through all used
'           columns and all used rows.  UsedRange does not always "reset" when
'           it ought to and .SpecialCells(XlCellType.xlCellTypeLastCell)
'           most certainly does not reset when it ought to. This routine, while
'           more robust, is also not bullet proof.
'
' Args:     ws· · · · · · · the worksheet being reviewed
'
' Returns:  Range · · · · · the cell in the bottom right corner
'
' Date          Developer   Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 21 Aug 2008   G. Truby    • moved to the project - initial version very old
' 30 Mar 2011   G. Truby    • noticing that the function alters the selection
'                             on the active worksheet when called referencing
'                             a non-visible worksheet in a non-visible workbook
'                             (add-in), which may be a cause of some errors
'                             users are experiencing.  Attempting to code around
'                             this behaviour in the hopes of eliminating the error.
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function fnLastCell(ByRef ws As Excel.Worksheet) As Excel.Range
' _____________________________________________________________________________
    Dim booEvents   As Boolean, _
        intLastCol  As Integer, _
        lngLastRow  As Long, _
        rngSelected As Excel.Range, _
        wsParent    As Excel.Worksheet
    
    '// if the FIND function is altering what is the SELECTION, then
    '// it is likely to be raising a SELECTIONCHANGE event, which we
    '// normally would NOT want to happen or process when making a simple
    '// call to a function.
    With Application
        Let booEvents = .EnableEvents
        .EnableEvents = False
    End With
    
    Let lngLastRow = 1
    Let intLastCol = 1
    '//  Need the RESUME NEXT in case there
    '//  is no data in the worksheet.
    On Error Resume Next
    
    With ws
        '// the FIND function is altering the SELECTION on the active worksheet
        '// even though it SHOULDN'T be doing so.  This *MAY* be what is causing
        '// our catastrophic errors
        If fnVisibleWindowCount > 0 Then
            If TypeOf Selection Is Excel.Range Then
                Set rngSelected = Selection
                Set wsParent = rngSelected.Parent
            End If
        
            '// Find the real last row
            lngLastRow = .Cells.Find(What:="*", _
                                     After:=.Range("A1"), _
                                     SearchDirection:=xlPrevious, _
                                     SearchOrder:=xlByRows).Row
            '// Find the real last column
            intLastCol = .Cells.Find(What:="*", _
                                     After:=.Range("A1"), _
                                     SearchDirection:=xlPrevious, _
                                     SearchOrder:=xlByColumns).Column
                                  
            If Not rngSelected Is Nothing Then
                rngSelected.Select
            End If
        End If  '// visible window count > 0
            
        '// double-check to see if the results make sense.  If they
        '// don't hope that UsedRange gets it right.
        If Application.WorksheetFunction.CountA(.Cells) > 1 _
        And lngLastRow * intLastCol = 1 Then
            Let lngLastRow = .UsedRange.Range("A1").Row _
                           + .UsedRange.Rows.Count - 1
            Let intLastCol = .UsedRange.Range("A1").Column _
                        + .UsedRange.Columns.Count - 1
        End If
    End With
    
    Set fnLastCell = ws.Cells(lngLastRow, intLastCol)
    Application.EnableEvents = booEvents
    
End Function '// fn Last Cell

 

[Greg Truby]

Well, now that I posted that, I can see that perhaps you might want to see fnVisibleWindowCount, so here's that code

' _____________________________________________________________________________
' fn VISIBLE WINDOW COUNT
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Descrip:  Pretty simple - the # of visible windows
'
' Args:     wbToCount*· · · · · count visible windows for one workbook instead
'                               of for application as a whole.
' Args:     booCountHidden* · · toggle to return the # of hidden windows instead
'
' Returns:  Integer · · · · · · # of visible windows
'
' Date          Developer   Comments
' ¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
' 01 Nov 2009   G. Truby    • initial version very old, moved here on this date
' 15 Nov 2011   G. Truby    • added toggle
' _____________________________________________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  Public Function fnVisibleWindowCount( _
                    Optional ByRef wbToCount As Excel.Workbook, _
                    Optional ByVal booCountHidden As Boolean = False) _
                    As Integer
' _____________________________________________________________________________
    Dim o As Object, _
        w As Excel.Window, _
        v%
    
    If wbToCount Is Nothing Then
        Set o = Application
    Else
        Set o = wbToCount
    End If
    
    For Each w In o.Windows
        Let v = v - w.Visible           '// TRUE is (-1) in VBA
    Next w
    
    Let fnVisibleWindowCount = IIf(booCountHidden, _
                                   o.Windows.Count - v, _
                                   v)
End Function