mikerickson
06-19-2007, 05:42 PM
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
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