Range behaviour after delete

[mikerickson]

I ran across a puzzlement and thought that I'd share.
I set a range variable, deleted the range. What is the variable?
It has no address, types as Range, the .Row returns 0, but .Rows gets a "Object variable or With block variable not set" error.

There's no project depending on this, I'm just curious about this object. VB doesn't seem to recognize it as any kind of Null.

Hmm..


Sub tttest()

Dim xRay As Range
Dim outStr As String

Set xRay = ActiveSheet.Range("a1:b3")
xRay.Delete

outStr = CBool(xRay.Address = ""): Rem True
outStr = outStr & vbCrLf & CBool(xRay Is Nothing): Rem False
outStr = outStr & vbCrLf & IsEmpty(xRay): Rem False
outStr = outStr & vbCrLf & IsNull(xRay): Rem False
outStr = outStr & vbCrLf & ObjPtr(xRay): Rem a non-zero number (varies with run)
outStr = outStr & vbCrLf & TypeName(xRay): Rem Range
MsgBox outStr
MsgBox xRay.Row & xRay.Column: Rem returns 00
'MsgBox xRay.Rows: Rem this line causes run-time error 91
End Sub

[mvidas]

Interesting.. I get an "Object required" error in excel 2000 in the line outStr = CBool(xRay.Address = "")What version are you using?
As for xRay.Rows, what do you get for xRay.Rows.Count?

[mikerickson]

I'm using Excel2004 for Mac

The xRay.Rows.Count gives a r-t error 91, Object variable or With block variable not set.

[Bob Phillips]

I ran into this recently in one of my projects.

I had a situation where various workbooks would be opned, but only one at any time. I had a workbook variable and a worksheet variable. In my code, I was closing the workbook incorrectly at one point. Later in the code I tried to refer to the worksheet variable, and voila.

Worst of all, the Excel app was initiated from a VB executable, and all I got was an automation error.