MS Excel - Please explain the Target property in the Intersect Function

I found the below formula:

If Intersect(Target,Range(strRange)) IsNothing then Exit Sub

How does the function know to pick up the cell address for Target if the default is the value ?
Can someone explain the mechanics of the Intersect Function.  I have googled it by I am struggling on why you would use this function...
Who is Participating?
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:

The default property for a Range object is the hidden _Default(RowIndex, ColumnIndex) property (not Value)!  RowIndex & ColumnIndex are both optional here so I am not surprised you may have found the use of Target confusing.

Think of _Default() like the Item method, or Cells property (although this may make an explanation more confusing).  The Item method requires at least one parameter (RowIndex) of the two available (RowIndex, ColumnIndex).  Cells, again has two parameters (RowIndex, ColumnIndex), but both are optional.  With me so far? :)

With the default property, _Default(), use of the Range object without any further qualification is equivalent to Range.Item(1) or Range.Cells(1) & this is the first cell in the range; the default property of that is, as you said, Value.

So, if Target is referring to a single cell (not more than one cell), & you queried the value of Target in the "Immediate" window, you would see the value of this cell:

Debug.Print Target

You would use the code statement you mentioned (actually separating IsNothing, see below) in a worksheet function associated with an event, such as one of these two event code functions within a worksheet code module:

a) Private Sub Worksheet_Change(ByVal Target As Range)
b) Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Alternatively, in a workbook code module:

a) Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
b) Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

For example, the following code would be entered within the code module of a worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim strRange                                          As String
  strRange = "C3:G7"
  If (Intersect(Target, Range(strRange)) Is Nothing) Then
     Exit Sub
  End If
  MsgBox "The cell just changed is within the range [" & strRange & "].", _
         vbInformation Or vbOKOnly, _
End Sub

Open in new window

Here, if a cell in the worksheet is changed, the Intersect() method (of the Application object, that is omitted as this is default object in this case) determines if the cell is within an explicit range ([C3:G7]).

If it is, then a Message box is shown.

The Application.Intersect() method returns a Range object & the code is simply checking if the return is a valid Range, or not (Is Nothing).

Simplying the code will, I hope, demonstrate the return object better:

Private Sub Worksheet_Change(ByVal Target As Range)

  MsgBox Intersect(Target, [C3:G7]).Address
End Sub

Open in new window

If the cell changed is within the range [G3:G7], then the address of the cell is displayed in a Message box.  However, if the cell changed is outside the range, a run-time error (91) is encountered as the return object from Intersect is Nothing.

Application.Intersect() is used to determine if the address of two (or more) ranges intersect each other.  That is, if all ranges share (at least) one common cell.

One of these ranges could be Target in the above examples, but it need not be.  Intersect() is just commonly used within the routines I mentioned because of the usefulness of determining if a cell has been changed, or selected, during normal operations upon a worksheet.

Another example (not using Target):

  Dim objRange                                          As Range

  Set objRange = Intersect([A:G], [3:7], [D4])
  If Not (objRange Is Nothing) Then
     ' [D4] intersects columns [A:G] & rows 3 to 7.
  End If

Open in new window


upobDaPlayaAuthor Commented:
fp,   this is making sense...I am reviewing your reply and then doing further reading at msdn.  Let me have some more time prior to closing...thx for all the detail and well layed out response !
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

I did consider going further & writing the code example(s) without using the Intersect() statement to show what was happening, but thought that may be overwhelming (initially).

If, after your further research, you are still unclear, & you would like to see that though, please ask.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

upobDaPlayaAuthor Commented:
What you have explained does make sense, but then why does MSDN have this on there site....

.... if an object has a Value property, then this property is the default value for the object. If an object does not have the Value property, then that object does not have a default value.

This was found at the MS Office site

Since the Item property is the default property of the Range object, it is not necessary to explicitly reference it.
[ fanpages ]IT Services ConsultantCommented:
If I understand the mismatch you were trying to highlight, I believe this sentence is incorrect:

If an object does not have the Value property, then that object does not have a default value.
upobDaPlayaAuthor Commented:
I felt like I had my own private tutor....Great tutorial on Target
[ fanpages ]IT Services ConsultantCommented:
:) You're very welcome.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.