13 thoughts on “Test an Object and Its Property”

1. Oh, if only VBA had .Net’s AndAlso all would be good! That’s a cool little trick that I’ll never use as well :/

2. Isn’t it generally bad to rely on assumptions that case, if, etc. clauses are executed in order?

3. Just another way:

   bFailed = True
   If Not rControl Is Nothing Then _
   bFailed = Not IsEmpty(rControl.Offset(0, 1).Value)
   If bFailed Then
   MsgBox “Operation Failed”
   Else
   rControl.Offset(0, 1).Resize(1, 7).Value = clsTimeSheet.TimeArray
   End If

4. I hadn’t heard that Bob.

5. Ah, the lack of short-circuit Boolean evaluation.

   C-like/spawned languages have && [and] and || [or] operators which perform short-circuit Boolean evaluation. That is, if the left side of && (||) is false (true), then the result of the && (||) expression must be false (true), so no need to evaluate the right side. These are (in C semantics) strictly Boolean operations with both sides converted to 1/TRUE if nonzero or left as 0/FALSE before the comparisons are performed.

   OTOH, VBA’s And and Or (and Xor, etc) operators are BITWISE operators rather than Boolean operators. They’re performing operations on ALL bits from BOTH SIDES of the operators, so both sides must be evaluated. Royal PITA.

   You could always use boolean state variables to approximate short-circuit boolean evaluation.

   ‘condition1 AND condition2 AND . . . AND conditionN
   state = False

   If condition1 Then If condition2 Then . . . If conditionN Then state = True

   If state Then
     perform operation
   Else
     exception handling
   End If

   ‘condition1 OR condition2 OR . . . OR conditionN
   state = True

   If Not condition1 Then If Not condition2 Then . . . If Not conditionN Then state = False

   If state Then
     perform operation
   Else
     exception handling
   End If

6. @Bob – If the language in question specifies operation order, then it should be safe to assume that operation order. FTHOI, you also need to consider associativity and whether or not some operators evaluate both operands or not (e.g., 0 as left hand side for multiplication means the right hand side really doesn’t need to be evaluated).

7. Dick, I’d go with Bob Smith (“Isn’t it generally bad to rely on assumptions that case, if, etc. clauses are executed in order?”) but that’s just showing my age.
   In The Good Old Days(TM) when we were restricted to FORTRAN and COBOL, we wrote some FORTRAN code that worked just fine on machine CDC, but when we moved it to machine Honeywell it failed. The reason was often enough a different implementation by the writers of the compilers.
   I think that today when we are writing in, say, VBA which is specifically for the Microsoft family of products, we have less risk.
   I suspect that my MIX C compiler (1985) may have taken some liberties that would easily demonstrate Bob’s concerns.

8. Shaking head in disbelief! Hasn’t anyone heard of subroutines? {grin} Why go through this song-and-dance when the “multiple steps” could be black-boxed as a subroutine or a function? Add the appropriate arguments to this procedure to get custom error messages.

   As an aside, while the Select Case True approach is very appealing, I’ve learned the hard way (harking back to my FORTRAN and assembler days) to not rely on the sequence in which a particular compiler generates code. In VBA it seems like it has been sequential (top to bottom, left to right) for as long as I remember but who knows? If Microsoft ever provides a graceful transition from VBA to the .Net world, the compiler rules may change.

   In any case, here’s how I’ve handled these kinds of scenarios. Reverse the tests to get a much simpler structure that also allows one to provide more informative error messages. This technique to removing “bushy trees” is one of the few things I still remember from ‘The Elements of Programming Style’ by Kernighan and Plauger (http://www.amazon.com/Elements-Programming-Style-Brian-Kernighan/dp/0070342075/ref=sr_1_2?ie=UTF8&s=books&qid=1280125164&sr=8-2), which IMO is a must read for anyone who aspires to anything more than just using the macro recorder.

       Dim rControl As Range
       If rControl Is Nothing Then
           MsgBox “rControl is not defined”
       ElseIf Not IsEmpty(rControl.Offset(0, 1).Value) Then
           ‘should it be _
            application.WorksheetFunction.CountA(rcontrol.Offset(0,1).Resize(1,7)) <>0
           MsgBox “Target Cell ” & rControl.Offset(0, 1).Address & ” is not empty”
       Else
           rControl.Offset(0, 1).Resize(1, 7).Value = clsTimeSheet.TimeArray
           End If

   Alternatively, simulate Try…Catch…Finally though I don’t like it all that much because it also traps errors in the 1st MsgBox and the assignment.

   Sub Try_Catch_Finally()
       Dim rControl As Range
       On Error GoTo Catch1
       If Not IsEmpty(rControl.Offset(0, 1).Value) Then
           MsgBox “Target Cell ” & rControl.Offset(0, 1).Address & ” is not empty”
       Else
           rControl.Offset(0, 1).Resize(1, 7).Value = clsTimeSheet.TimeArray
           End If
       GoTo Finally1
   Catch1:
       MsgBox “rControl is not defined”
       Resume Finally1
   Finally1:
       End Sub

9. From what I understand, VBA is not a compiled language, rather it is interpreted line by line as it is processed, thus for VBA, I cannot foresee any instance where relying on the case statements to execute in order would be error prone.

10. ” … I cannot foresee any instance where relying on the case statements to execute in order would be error pron”
    and Tushar’s “Hasn’t anyone heard of subroutines?”.

    Interpretation and Compilation are two sides of the generic “translation”, and any fear of future methods of translation are valid fears (not to say that they will come true, just that they are arguable).

    I suspect the easiest way for VBAers to see this is to take Tushar’s approach and write a small procedure to handle these awkward tests. I do that often. When I take a chained AND condition and embed it into a VBA procedure, I become the translator, it is MY decision as to which condition is evaluated first, and which condition is evaluated conditionally on the first.

    Thus you and I could translate the same condition in two different ways.

    That said, is all the more reason for using Libraries of utility code rather than duplicating code.

11. “Shaking head in disbelief! Hasn’t anyone heard of subroutines?”
    I am about to take the first stray step off-topic, but yes, Tushar I’ve heard of them. Daniel D McCracken’s 1961 “Guide to FORTRAN programming p 55-57? for example (grin!)

    I hold that the VBA On Error statement should not appear in application code, since anything you can program for after the event you can program for before the event.
    I do allow On Error statements to appear in library routines written by the senior developer (me!) to allow the application developer (me again!) to avoid On Error statements in applications.
    Many examples abound:
    (1) a procedure to determine the number of dimensions of an array (APL: “rank”)
    (2) a procedure to determine if a file exists (originally the good old FileLen statement test)
    (3) borderline cases such as this one:
    Public Function blnProcedureExistsInModule(strProcedureName As String, strModuleName As String, doc As Document) As Boolean
    On Error Resume Next
    If blnModuleExistsInDocument(doc, strModuleName) = True Then
    blnProcedureExistsInModule = doc.VBProject.VBComponents(strModuleName) _
    .CodeModule.ProcStartLine(strProcedureName, vbext_pk_Proc) 0
    End If
    End Function

    Back on topic: That said, I suspect that sometimes it just makes more sense to have code in-line, that is, it can, at times, make the application code more comprehensible to future readers.

    We now return you to our regular programming …

12. Chris wrote: “I hold that the VBA On Error statement should not appear in application code, since anything you can program for after the event you can program for before the event.”

    For 95% of the code I write that is probably true — not to mention that during development it is often a must that no error handler be enabled except for specific *anticipated* errors.

    But, it sometimes makes for extremely convoluted implementation of a fairly straightforward algorithm. One instance where I use ‘on error resume next’ extensively is in the context of an Excel chart. At times detecting whether a certain property or method applies to the chart at hand is more work (requiring more cumbersome code that is harder to read, understand, and maintain) than simply making the change and falling through in the event of an error. Not only does this make the code more streamlined but I don’t have to worry about falling over when (if?) Microsoft introduces new chart types.

    And, there are a number of such instances where handling errors is easier than detecting the possibility of an error. Not only with Excel but with other applications and in building web based solutions using, say, asp and javascript.

13. A follow up to my original post. Four different ways to handle the problem without worrying about bushy trees or duplicated code. Do keep in mind that as Dick in his original post pointed out the value of these techniques is much more apparent in more realistic code than this small example. In more realistic code, there might be several tests for validity not just the 2 as here.

    1) Recognize that booleans can be assigned the result of a test and don’t require an If test with a corresponding assignment of a true/false value.

    Sub useBool()
        Dim rControl As Range
        Dim Oops As Boolean
        Oops = rControl Is Nothing
        If Not Oops Then _
            Oops = Not IsEmpty(rControl.Offset(1, 0).Value)
       
        If Not Oops Then
            ‘update code
        Else
            ‘error code
            End If
        End Sub

    2) Use the boolean approach but skip the If Not Oops tests

    Sub useBool2()
        Dim rControl As Range
        Dim Oops As Boolean
        Oops = rControl Is Nothing
        On Error Resume Next
        Oops = Oops Or Not IsEmpty(rControl.Offset(1, 0).Value)
        On Error GoTo 0
       
        If Not Oops Then
            ‘update code
        Else
            ‘error code
            End If
        End Sub

    3) Use a GoTo as an error handler. That’s a limited application of the development philosophy that any forward-moving GoTo is OK. [GoTos become a nightmare when people jump back-and-forth with them.]

    Sub useErrXIT()
        Dim rControl As Range
        If rControl Is Nothing Then GoTo ErrXIT
        If Not IsEmpty(rControl.Offset(1, 0).Value) Then _
            GoTo ErrXIT
        ‘update code
        Exit Sub
    ErrXIT:
        MsgBox “Oops”
        End Sub

    4) And, of course, one can enhance the above by using an Enum to better explain the error.

    Sub useErrXIT2()
        Enum ErrCode
            NoError = 0
            NoObj
            NotEmpty
            End Enum
        Dim rControl As Range
        Dim Oops As ErrCode
        If rControl Is Nothing Then Oops = NoObj: GoTo ErrXIT
        If Not IsEmpty(rControl.Offset(1, 0).Value) Then _
            Oops = NotEmpty: GoTo ErrXIT
        ‘update code
        Exit Sub
    ErrXIT:
        ‘customized error message
        End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.