Exception when uisng Intersect method

[Ai]

H

It seems that if the range 1 and range2 in Intersect (Range range1, Range ranges2 , ...) are in different worksheets this method throws HRESULT: 0x800A03EC exception. I am using c#.

Looking for your advice

Ai

 

[Frank Kabel]

Hi
why do you want to test this kind of ranges with
Intersect. They could never Intersect if they're on
different sheets?

-----Original Message-----
Hi

It seems that if the range 1 and range2 in Intersect

(Range range1, Range ranges2 , ...) are in different
worksheets this method throws HRESULT: 0x800A03EC
exception. I am using c#.

 

[Frank Kabel]

Hi
try for example
If rng_1.parent.name<>rng_2.parent.name then
msgbox "different sheets"
end if

-----Original Message-----
I just wanted to have a more generic method to basically

check if a range is overlapping with any other range in
the same workbook. Is there a simple method to check if
two ranges are in different sheets?

 

[Tushar Mehta]

A test that should be safer than checking the name of the worksheet is
to use the Is comparison operator as in:

Option Explicit

Sub testIt()
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = Sheets(1).Range("a1")
Set Rng2 = Sheets(1).Range("a2")
MsgBox Rng1.Parent Is Rng2.Parent
Set Rng1 = Sheets(1).Range("a1")
Set Rng2 = Sheets(2).Range("a1")
MsgBox Rng1.Parent Is Rng2.Parent
Set Rng1 = Workbooks(1).Sheets(1).Range("a1")
Set Rng2 = Workbooks(2).Sheets(1).Range("a1")
MsgBox Rng1.Parent Is Rng2.Parent
Set Rng1 = Workbooks(1).Sheets(1).Range("a1")
Set Rng2 = Workbooks(1).Sheets(1).Range("a2")
MsgBox Rng1.Parent Is Rng2.Parent
End Sub

The above code should yield: True, False, False, True.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Ogilvy]

Is didn't work well in all cases in xl97. I don't question your approach,
but I don't see how it is safer than checking the name of the sheets by
comparing the results of the name property. Maybe more pleasing, maybe
even faster, but I don't see a case where using the name would fail and the
IS approach would be successful.

--
Regards,
Tom Ogilvy

A test that should be safer than checking the name of the worksheet is
to use the Is comparison operator as in:

Option Explicit

Sub testIt()
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = Sheets(1).Range("a1")
Set Rng2 = Sheets(1).Range("a2")
MsgBox Rng1.Parent Is Rng2.Parent
Set Rng1 = Sheets(1).Range("a1")
Set Rng2 = Sheets(2).Range("a1")
MsgBox Rng1.Parent Is Rng2.Parent
Set Rng1 = Workbooks(1).Sheets(1).Range("a1")
Set Rng2 = Workbooks(2).Sheets(1).Range("a1")
MsgBox Rng1.Parent Is Rng2.Parent
Set Rng1 = Workbooks(1).Sheets(1).Range("a1")
Set Rng2 = Workbooks(1).Sheets(1).Range("a2")
MsgBox Rng1.Parent Is Rng2.Parent
End Sub

The above code should yield: True, False, False, True.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

range is overlapping with any other range in the same workbook. Is there a
simple method to check if two ranges are in different sheets?

 

[Tushar Mehta]

I guess it wasn't obvious from my post that I was using the default
workbooks created by XL. Which meant different workbooks with
worksheets named the same. A safer name test would be
Rng1.Parent.Parent.Name <> Rng2.Parent.Parent.Name Or Rng1.Parent.Name
<> Rng2.Parent.Name

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Ogilvy]

Yes, I missed that you had expanded the problem to intersecting ranges in
different workbooks. My question was on the "safer" comment rather than
your code. So you are correct that checking on just sheet name if different
workbooks are involved would be problematic (overcome by using your most
recent suggestion. )

 

[Tushar Mehta]

Several years back I ran into similar code and it took a while to
figure out why it didn't work. That was the first time I used 'Is'.
I've never been completely satisfied with when Is will work, but, in
this discussion, it appeared to be the way to go.

The problems that you mentioned with 97 seem to extend to 2003 (and
VB6). The following code yields: False, False, True.

Sub testIt()
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = Range("a1")
Set Rng2 = Range("a1")
MsgBox Selection Is Selection
MsgBox Rng1 Is Rng2
MsgBox Rng1.Parent Is Rng2.Parent
End Sub

There's some information at
IS Operator Does Not Work with Microsoft Excel Objects
http://support.microsoft.com/default.aspx?scid=kb;en-us;111493

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Harlan Grove]

It seems that if the range 1 and range2 in Intersect (Range range1, Range
ranges2 , ...) are in different worksheets this method throws HRESULT:
0x800A03EC exception. I am using c#.

C# provides syntax for exception handling through try..catch..finally blocks. It
may prove more efficient to trap the exceptions than to compare objects'
parents' and grandparents' names. Note that C# also has an 'is' operator, but it
functions like VBA's 'TypeOf x Is y', not like VBA's plain 'Is' operator. It's
unclear to me (maybe just because I don't have an MSDN subscription or a lot of
VB[A] books) whether VBA's 'Is' operator is a reference or value equality
operator. In C terms, whether it's checking that pointers are pointing to the
same piece of memory or whether what they're pointing to is the same.

Going the opposite direction that Tushar and Tom went in discussing Is vs .Name,
as long as your range objects aren't NULL/Nothing (easily checked), you can
safely access their Address properties. You can compare Address(,,, 1) results
from all range objects up to the rightmost exclamation point in each(since
exclamation points may appear in workbook directory and filenames, but not in
range addresses). If they're the same, then you can use Intersect (and Union)
without throwing an exception.

With regard to string comparisons using Name properties, don't use Name
properties to check for the same worksheets. Use Index properties and integer
comparisons instead. For workbooks, since there's a possibility multiple Excel
application instances could be involved, don't use Name properties to check for
the same workbooks. Use FullName properties to make absolutely sure that you're
not working with ranges from workbooks with the same base filename but stored in
different directories.