Why does this work????

[Mr Walker]

I had trouble getting my hlookup to work, and whilst trying a few things, I wrote this. I'm glad it works, but I always like to know why, and I just can't think this through in my head.

It's the If Error line. Can someone explain what it's doing? I know it's probably simple, but I'm still fairly new to the whole VB thing. Great fun though!!!

BTW, ReceiptsCol and LookupRange are dimmed early on in my sub.

On Error Resume Next
If Error = Sheet21.Cells(r, 1) = WorksheetFunction.HLookup(Sheet21.Cells(r, 1), LookupRange, 1, False) Then
On Error GoTo 0

Sheet3.Columns(ReceiptsCol).EntireColumn.Insert
End If

 

[goblin]

Hmmm. Actually, your final conclusion is not correct :wink:

Think of your statement like this:

if False = WorksheetFunction.HLookup(Sheet21.Cells(r, 1), LookupRange, 1, False) then...

HLookup will then return contents from the LookupRange. If the contents returned are either 'False' or 0, the comparison expression becomes:

if False = False then ...

or

if False = 0 then ...

both of which evaluate to true and your column is inserted. If the HLookup returns "", the comparison is ' False = "" ' which evaluates to False.

Goblin

 

[Andrew Poulsom]

This intrigued me so I have had a closer look.

Actually the reason you don't have to explicitly declare Error is that it is a VBA function with one optional argument (errornumber). If errornumber is omitted the message corresponding to the most recent run-time error is returned or a zero-length string ("") if there isn't one.

With this sample code (Lookup range B1:C1, lookup value in A1):

Sub Test()
    On Error Resume Next
    If Error = Range("A1") = WorksheetFunction.HLookup(Range("A1"), Range("B1:D1"), 1, False) Then
        MsgBox "Error = " & Error
    Else
        MsgBox Error = (Range("A1") = WorksheetFunction.HLookup(Range("A1"), Range("B1:D1"), 1, False))
    End If
End Sub

The HLookup property fails if the lookup value is not found, as evidenced by the message box saying:

Error = Unable to get the HLookup property of the WorksheetFunction class.

The code continues at the next line not because the test is True, but because of OnError Resume Next.

If the lookup value is found you get a message box saying False, because null does not equal True.

So the result of the test will never be True. The code only works because of
OnError Resume Next.

 

[tusharm]

If someone had looked up 'error' (w/o the quotes) in XL VBE help, they would have found the Error *function,* which, when used without the optional error-number parameter, returns the text description of the last error, if any.

Further, as Goblin pointed out, boolean operators are evaluated L-to-R.

Finally, when the HLookup faults, it returns an empty result.

So, the IF statement as evaluated by XL is

IF(Error=Cells())={hlookup stuff} THEN...

Now, if the HLookup works, then Error returns nothing. So, nothing=Cells() is False and False={successful hlookup} will be False.

If Hlookup fails, then Error contains "Unable to get the VLookup property of the WorksheetFunction class". Compared with Cells(), the result is still FALSE. Compare FALSE with the empty result from {Hlookup stuff}, the result is *TRUE* -- another of XL's quirky implicit conversions!

{Interestingly, Goblin's speculative explanation, while not totally correct, comes closest to explaining the intermediate results}

To fully understand the multiple subtleties of what is going on, try (you may have to look up some of the functions below in XL VBE help)

Option Explicit

Sub testIt()
    Dim x
    On Error Resume Next
    x = Application.WorksheetFunction.VLookup(Cells(1, 1), Range("b1:b24"), 1, False)
    MsgBox TypeName(x) & "," & IsEmpty(x) & "," & IsNull(x)
    MsgBox (x = True) & "," & (x = False)
    If Error = Cells(1, 1) = Application.WorksheetFunction.VLookup(Cells(1, 1), Range("b1:b24"), 1, False) Then
        MsgBox True
    Else
        MsgBox False
        End If
    MsgBox Error
    Debug.Print Error
    End Sub

Finally, as Goblin pointed out, you can demonstrate a bug in the code by having FALSE (or the number zero) in Cells() and the same value somewhere in the lookup-range.

 

[goblin]

Teaches you to exercise extreme caution when using 'On Error Resume Next'!

I never even thought of checking for that possibility. I just went straight for the logical syntax and found under what circumstances it would possibly evaluate true. The simplifications I made on the expression were drastic enough to ensure an Error would not occur, and thus removed the actual cause I was looking for.

Guess this is what happens when you try to debug something off-site with no access to the actual data. Too many simplification!

Excellent catch, Andrew! :D

Goblin