Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: Excel Interop objects and checking for nulls?
hi
I've been having this trouble for a long time: Is there any way I can check if a reference to a range object no longer exists? Eg, After creating a reference to a range: Range refRange = Application.ActiveCell; I delete the cell (eg, via removing an entire row/column), yet the refRange object still points to the deleted range object. When I try to use it, I get an exception. This problem persists with any type of Excel Interop objects (Name, Names, Range, etc). Was wondering if anyone know if I can check if an Excel Interop object is usable? Eg, checking if it's null or not. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Interop objects and checking for nulls?
try
x = refRange.areas(1).address if that errors it has been entirely deleted, assuming 'Not refRange Is Nothing' Not sure if relevant for you but keep in mind also the range may have been moved (within the sheet) since it was created, and still be valid. Regards, Peter T "JsjsLim" wrote in message ... hi I've been having this trouble for a long time: Is there any way I can check if a reference to a range object no longer exists? Eg, After creating a reference to a range: Range refRange = Application.ActiveCell; I delete the cell (eg, via removing an entire row/column), yet the refRange object still points to the deleted range object. When I try to use it, I get an exception. This problem persists with any type of Excel Interop objects (Name, Names, Range, etc). Was wondering if anyone know if I can check if an Excel Interop object is usable? Eg, checking if it's null or not. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Interop objects and checking for nulls?
Hi Peter,
I'm aware that sometimes, a range might have been moved, and that's fine, because I can still go about my business (eg, range.text, or range.formula, etc). However, my problem occurs when a range is no longer valid (deleted). Any references that I have will still point to the invalid range. And when I try to access any of the attributes (once again like above, range.text, range.formula, etc), it throws an exception (0x800A01A8 I believe), which is what one would expect. What I would like to know, however, is if it's possible to check if a reference to an Excel range is still valid or not. Something like range == null? or perhaps is there a IsDisposed attribute within the range object that I can check? Currently, I'm handling this via try-catch, which imho is a really inefficient way of doing things. I'd rather have code that looks like this: if (!range.IsDisposed) { ...formula = range.Formula... } or if (range != null) { ...formula = range.Formula... } instead of try { ...formula = range.Formula... } catch{ // Range is in a faulted state and can't be used } Thanks "Peter T" wrote: try x = refRange.areas(1).address if that errors it has been entirely deleted, assuming 'Not refRange Is Nothing' Not sure if relevant for you but keep in mind also the range may have been moved (within the sheet) since it was created, and still be valid. Regards, Peter T "JsjsLim" wrote in message ... hi I've been having this trouble for a long time: Is there any way I can check if a reference to a range object no longer exists? Eg, After creating a reference to a range: Range refRange = Application.ActiveCell; I delete the cell (eg, via removing an entire row/column), yet the refRange object still points to the deleted range object. When I try to use it, I get an exception. This problem persists with any type of Excel Interop objects (Name, Names, Range, etc). Was wondering if anyone know if I can check if an Excel Interop object is usable? Eg, checking if it's null or not. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Interop objects and checking for nulls?
AFAIK there is no other way to directly test if a referenced range has been
"removed" from the sheet other than to attempt to refer to one of its properties. Normally your routine will have some sort of error handler. Typically you can proceed on the assumption the range still exists, but if the code fails the first thing to test would be the integrity of the actual range along the lines you posted. A way to test without invoking any error would be to "name" the range or refer to the range in some other (hidden) cell formula, eg (VBA) Dim r As Range Set r = Range("a1") r.Name = "nmCell" Rows(1).Delete If InStr(2, ActiveWorkbook.Names("nmcell").RefersTo, "#REF!") Then MsgBox "range removed" End If FWIW, I use hidden worksheet level names to be able to reconstruct same range ref after file save, close, re-open. Regards, Peter T "JsjsLim" wrote in message ... Hi Peter, I'm aware that sometimes, a range might have been moved, and that's fine, because I can still go about my business (eg, range.text, or range.formula, etc). However, my problem occurs when a range is no longer valid (deleted). Any references that I have will still point to the invalid range. And when I try to access any of the attributes (once again like above, range.text, range.formula, etc), it throws an exception (0x800A01A8 I believe), which is what one would expect. What I would like to know, however, is if it's possible to check if a reference to an Excel range is still valid or not. Something like range == null? or perhaps is there a IsDisposed attribute within the range object that I can check? Currently, I'm handling this via try-catch, which imho is a really inefficient way of doing things. I'd rather have code that looks like this: if (!range.IsDisposed) { ...formula = range.Formula... } or if (range != null) { ...formula = range.Formula... } instead of try { ...formula = range.Formula... } catch{ // Range is in a faulted state and can't be used } Thanks "Peter T" wrote: try x = refRange.areas(1).address if that errors it has been entirely deleted, assuming 'Not refRange Is Nothing' Not sure if relevant for you but keep in mind also the range may have been moved (within the sheet) since it was created, and still be valid. Regards, Peter T "JsjsLim" wrote in message ... hi I've been having this trouble for a long time: Is there any way I can check if a reference to a range object no longer exists? Eg, After creating a reference to a range: Range refRange = Application.ActiveCell; I delete the cell (eg, via removing an entire row/column), yet the refRange object still points to the deleted range object. When I try to use it, I get an exception. This problem persists with any type of Excel Interop objects (Name, Names, Range, etc). Was wondering if anyone know if I can check if an Excel Interop object is usable? Eg, checking if it's null or not. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Interop objects and checking for nulls?
Hi Peter,
Sorry for the late reply, and thanks for the help. Not exactly what I needed, but I must admit that it's a smart solution. Thanks again! "Peter T" wrote: AFAIK there is no other way to directly test if a referenced range has been "removed" from the sheet other than to attempt to refer to one of its properties. Normally your routine will have some sort of error handler. Typically you can proceed on the assumption the range still exists, but if the code fails the first thing to test would be the integrity of the actual range along the lines you posted. A way to test without invoking any error would be to "name" the range or refer to the range in some other (hidden) cell formula, eg (VBA) Dim r As Range Set r = Range("a1") r.Name = "nmCell" Rows(1).Delete If InStr(2, ActiveWorkbook.Names("nmcell").RefersTo, "#REF!") Then MsgBox "range removed" End If FWIW, I use hidden worksheet level names to be able to reconstruct same range ref after file save, close, re-open. Regards, Peter T "JsjsLim" wrote in message ... Hi Peter, I'm aware that sometimes, a range might have been moved, and that's fine, because I can still go about my business (eg, range.text, or range.formula, etc). However, my problem occurs when a range is no longer valid (deleted). Any references that I have will still point to the invalid range. And when I try to access any of the attributes (once again like above, range.text, range.formula, etc), it throws an exception (0x800A01A8 I believe), which is what one would expect. What I would like to know, however, is if it's possible to check if a reference to an Excel range is still valid or not. Something like range == null? or perhaps is there a IsDisposed attribute within the range object that I can check? Currently, I'm handling this via try-catch, which imho is a really inefficient way of doing things. I'd rather have code that looks like this: if (!range.IsDisposed) { ...formula = range.Formula... } or if (range != null) { ...formula = range.Formula... } instead of try { ...formula = range.Formula... } catch{ // Range is in a faulted state and can't be used } Thanks "Peter T" wrote: try x = refRange.areas(1).address if that errors it has been entirely deleted, assuming 'Not refRange Is Nothing' Not sure if relevant for you but keep in mind also the range may have been moved (within the sheet) since it was created, and still be valid. Regards, Peter T "JsjsLim" wrote in message ... hi I've been having this trouble for a long time: Is there any way I can check if a reference to a range object no longer exists? Eg, After creating a reference to a range: Range refRange = Application.ActiveCell; I delete the cell (eg, via removing an entire row/column), yet the refRange object still points to the deleted range object. When I try to use it, I get an exception. This problem persists with any type of Excel Interop objects (Name, Names, Range, etc). Was wondering if anyone know if I can check if an Excel Interop object is usable? Eg, checking if it's null or not. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interop Excel | Excel Programming | |||
Excel VBA Class Objects - Parent & Successor Objects | Excel Programming | |||
Using Excel, how do I replace cells containing blanks with nulls? | Excel Discussion (Misc queries) | |||
How do I sort nulls within date field in Excel | Excel Programming | |||
Unable to remove Sheet objects in the Microsoft Excel Objects | Excel Programming |