Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exception when uisng Intersect method
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exception when uisng Intersect method
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#. Looking for your advice. Ai . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exception when uisng Intersect method
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? Thanks . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exception when uisng Intersect method
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 In article , says... 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? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exception when uisng Intersect method
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 "Tushar Mehta" wrote in message news:MPG.1b1bafe195095c289897ea@news-server... 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 In article , says... 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? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exception when uisng Intersect method
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 In article , says... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exception when uisng Intersect method
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. ) -- Regards, Tom Ogilvy "Tushar Mehta" wrote in message news:MPG.1b1bb814f81ae2209897ef@news-server... 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 In article , says... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exception when uisng Intersect method
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...b;en-us;111493 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... 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. ) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exception when uisng Intersect method
"?B?QWk=?=" wrote...
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. -- To top-post is human, to bottom-post and snip is sublime. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Uisng the Max and Index function to link to other workbooks | Excel Discussion (Misc queries) | |||
Uisng an option group query | New Users to Excel | |||
calculating a weighted average uisng formula | Excel Worksheet Functions | |||
Access a internet Web Site uisng a macro done with excel | Excel Programming | |||
Is there a "Non-Intersect" VBA method to remove a sub-range from a range? | Excel Programming |