Not Application.Intersect () Is Nothing vs Select Case

[mdavid]

Hi, I have a WorkSheet with several UserForms that are opened by double clicking on a cell, using

If Not Application.Intersect(Target, Range("S2:S" & lRow)) Is Nothing Then
     Cancel = True
     UserForm7.Show
     Exit Sub
  End If
    If Not Application.Intersect(Target, Range("T2:T" & lRow)) Is Nothing Then
     Cancel = True
     UserForm8.Show
     Exit Sub
  End If

Please could you explain why the above code is better than:

Cancel = True
Select Case ActiveCell.Column
     Case "1"
          insertRowBelow
     Case "8"
          UserForm3.Show
     Case "9"
          UserForm1.Show
     Case "10"
          UserForm5.Show
     Case "13"
         UserForm4.Show vbModeless
     Case "16"
         UserForm6.Show
     Case "19"
         UserForm7.Show
     Case "20"
         UserForm8.Show
End Select

To me Select Case seems a lot less code and easier to understand than the double negative of "Not Application.Intersect" suggested in most examples.

Thanks for setting me straight
David

 

[Hui]

David

Why do you think the preceding code is better ?

What makes it worse or better?

In my opinion the best code or formula is the one that you understand

That isn't to say that alternatives maybe faster, more succinct, or have some other metric that people may read as "Better"

Often longer code may be faster?

Shorter code may have less error correction etc etc

In your example, I think the Select Case maybe more readable and is probably fine.
Each Application.Intersect() requires access back to Excel and that is generally also a slow point for operations

 

[Debaser]

For a DoubleClick event, there's not really a lot of difference (although Column returns a number so you really ought to test for numbers, not strings) other than the fact that your first code is restricting the row numbers looked at whereas the second is not.

For a different event, such as a Change event, the two codes would not really be the same thing at all.

 

[mdavid]

Hi,
Thanks for your replies. Whenever I check out how to check for an event in a cell - and I do it often 'cause my memory's shot - its always "Not Application.Intersect(Target,....) is Nothing" so I thought that this is the way it should be done 'cause it's more efficient, less error prone, more readable to anyone that needs to read the code in the future etc etc. But if using Select Case is not a problem, that's much easier for me.

Do you think there's a place for a cheatsheet that simplifies vba functions, like the example here - makes it a lot more accessible to people like me

Thanks for your help

 

[Debaser]

Select Case is not a problem for this event. For other events, the Intersect approach is generally better, which I think is why most people use it for everything.

 

[Hui]

The idea of using "If Application.Intersect(Target,....) is Nothing" is to quickly eliminate the negatives and hence exit the subroutine as soon as possible. This minimizes calculation lag and hence user disruption.

What you do past that really depends on the specific application each time you code a problem

 

[Debaser]

That's not the only difference though. Using Intersect allows you to test if any part of Target is within the range you are interested in. So, for example, with a Worksheet_Change code, if you're monitoring column B and the user changes cells in A and B at the same time (eg by pasting), the Intersect code will detect that cells in B have changed, whereas testing Target.Column on its own would not, since that will only return the column number of the top left cell of the first area in target (which would be column A in this case).