HELP: Excel Interop objects and checking for nulls?

[JsjsLim]

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

 

[Peter T]

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]

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]

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]

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!