How can I detect "nothing"?
 

This takes a little explaining, please bear with me:
As part of a data mining project I have Excel (2003) VBA code that builds a list
out of a user defined variable type:

Type OpRec
.........
........
........[List of variable eliminated to save space]
End Type

Type SORec
strSOSfx As String
strPItem As String
OpRecord() As OpRec
End Type
Dim sO() As SORec

Because a few of the lines in the file this reads result from erroneous or
canceled shop orders, when the process reaches those lines, I try to skip over
them:

If (UBound(sO(i).OpRecord) < 0) Then
This works OK for many but a few end up with "Subscript out of range". At such
times watches tell me that sO(i).OpRecord = Nothing.

I tried
If (UBound(sO(i).OpRecord) < 0) And (sO(i).OpRecord < Nothing) Then
And got "Invalid use of Object"

I tried
Dim varNothing
Set varNothing = Nothing
If (UBound(sO(i).OpRecord) < 0) And (sO(i).OpRecord < varNothing) Then
And got "Type Mismatch"

I tried
If (UBound(sO(i).OpRecord) < 0) And Not IsNull(sO(i).OpRecord) Then
but got the message "Only user defined types defined in public object modules
can be coerced to or from a variant passed to late-bound functions"

This last one goes over my head. How do I set up a public object module (what
sources of info can I you recommend) and even if I do that, will it work? Or is
there a simpler way?
(There are many thousands of lines in the input file, so manual editing is not
an option!)
Thank You,
-plh


--
I keep hitting "Esc" -- but I'm still here!

How can I detect "nothing"?
 

I have not read your entire post but to detect nothing you need the word
is... Try this as an example...

sub Test
dim wbk as workbook
msgbox wbk is nothing
set wbk = thisworkbook
msgbox wbk is nothing
end sub

--
HTH...

Jim Thomlinson


"plh" wrote:

This takes a little explaining, please bear with me:
As part of a data mining project I have Excel (2003) VBA code that builds a list
out of a user defined variable type:

Type OpRec
.........
........
........
[List of variable eliminated to save space]
End Type

Type SORec
strSOSfx As String
strPItem As String
OpRecord() As OpRec
End Type
Dim sO() As SORec

Because a few of the lines in the file this reads result from erroneous or
canceled shop orders, when the process reaches those lines, I try to skip over
them:

If (UBound(sO(i).OpRecord) < 0) Then
This works OK for many but a few end up with "Subscript out of range". At such
times watches tell me that sO(i).OpRecord = Nothing.

I tried
If (UBound(sO(i).OpRecord) < 0) And (sO(i).OpRecord < Nothing) Then
And got "Invalid use of Object"

I tried
Dim varNothing
Set varNothing = Nothing
If (UBound(sO(i).OpRecord) < 0) And (sO(i).OpRecord < varNothing) Then
And got "Type Mismatch"

I tried
If (UBound(sO(i).OpRecord) < 0) And Not IsNull(sO(i).OpRecord) Then
but got the message "Only user defined types defined in public object modules
can be coerced to or from a variant passed to late-bound functions"

This last one goes over my head. How do I set up a public object module (what
sources of info can I you recommend) and even if I do that, will it work? Or is
there a simpler way?
(There are many thousands of lines in the input file, so manual editing is not
an option!)
Thank You,
-plh


--
I keep hitting "Esc" -- but I'm still here!

How can I detect "nothing"?
 

I am uncertain about what your code does, but maybe...
If Not sO(i).OpRecord is Nothing Then
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"plh" wrote in message ...
This takes a little explaining, please bear with me:
As part of a data mining project I have Excel (2003) VBA code that builds a list
out of a user defined variable type:
Type OpRec
.........
........
........[List of variable eliminated to save space]
End Type
Type SORec
strSOSfx As String
strPItem As String
OpRecord() As OpRec
End Type
Dim sO() As SORec

Because a few of the lines in the file this reads result from erroneous or
canceled shop orders, when the process reaches those lines, I try to skip over
them:
If (UBound(sO(i).OpRecord) < 0) Then
This works OK for many but a few end up with "Subscript out of range". At such
times watches tell me that sO(i).OpRecord = Nothing.
I tried
If (UBound(sO(i).OpRecord) < 0) And (sO(i).OpRecord < Nothing) Then
And got "Invalid use of Object"
I tried
Dim varNothing
Set varNothing = Nothing
If (UBound(sO(i).OpRecord) < 0) And (sO(i).OpRecord < varNothing) Then
And got "Type Mismatch"
I tried
If (UBound(sO(i).OpRecord) < 0) And Not IsNull(sO(i).OpRecord) Then
but got the message "Only user defined types defined in public object modules
can be coerced to or from a variant passed to late-bound functions"
This last one goes over my head. How do I set up a public object module (what
sources of info can I you recommend) and even if I do that, will it work? Or is
there a simpler way?
(There are many thousands of lines in the input file, so manual editing is not
an option!)
Thank You,
-plh
--
I keep hitting "Esc" -- but I'm still here!

How can I detect "nothing"?
 

To test an object for Nothing, use the Is comparison operator. E.g.,

If Obj Is Nothing Then

or

If Not Obj Is Nothing Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)


"plh" wrote in message
...
This takes a little explaining, please bear with me:
As part of a data mining project I have Excel (2003) VBA code that builds
a list
out of a user defined variable type:

Type OpRec
........
.......
.......
[List of variable eliminated to save space]
End Type

Type SORec
strSOSfx As String
strPItem As String
OpRecord() As OpRec
End Type
Dim sO() As SORec

Because a few of the lines in the file this reads result from erroneous or
canceled shop orders, when the process reaches those lines, I try to skip
over
them:

If (UBound(sO(i).OpRecord) < 0) Then
This works OK for many but a few end up with "Subscript out of range". At
such
times watches tell me that sO(i).OpRecord = Nothing.

I tried
If (UBound(sO(i).OpRecord) < 0) And (sO(i).OpRecord < Nothing) Then
And got "Invalid use of Object"

I tried
Dim varNothing
Set varNothing = Nothing
If (UBound(sO(i).OpRecord) < 0) And (sO(i).OpRecord < varNothing) Then
And got "Type Mismatch"

I tried
If (UBound(sO(i).OpRecord) < 0) And Not IsNull(sO(i).OpRecord) Then
but got the message "Only user defined types defined in public object
modules
can be coerced to or from a variant passed to late-bound functions"

This last one goes over my head. How do I set up a public object module
(what
sources of info can I you recommend) and even if I do that, will it work?
Or is
there a simpler way?
(There are many thousands of lines in the input file, so manual editing is
not
an option!)
Thank You,
-plh


--
I keep hitting "Esc" -- but I'm still here!

How can I detect "nothing"?
 

Seems to be much ado about Nothing! lol

"plh" wrote:

This takes a little explaining, please bear with me:
As part of a data mining project I have Excel (2003) VBA code that builds a list
out of a user defined variable type:

Type OpRec
.........
........
........
[List of variable eliminated to save space]
End Type

Type SORec
strSOSfx As String
strPItem As String
OpRecord() As OpRec
End Type
Dim sO() As SORec

Because a few of the lines in the file this reads result from erroneous or
canceled shop orders, when the process reaches those lines, I try to skip over
them:

If (UBound(sO(i).OpRecord) < 0) Then
This works OK for many but a few end up with "Subscript out of range". At such
times watches tell me that sO(i).OpRecord = Nothing.

I tried
If (UBound(sO(i).OpRecord) < 0) And (sO(i).OpRecord < Nothing) Then
And got "Invalid use of Object"

I tried
Dim varNothing
Set varNothing = Nothing
If (UBound(sO(i).OpRecord) < 0) And (sO(i).OpRecord < varNothing) Then
And got "Type Mismatch"

I tried
If (UBound(sO(i).OpRecord) < 0) And Not IsNull(sO(i).OpRecord) Then
but got the message "Only user defined types defined in public object modules
can be coerced to or from a variant passed to late-bound functions"

This last one goes over my head. How do I set up a public object module (what
sources of info can I you recommend) and even if I do that, will it work? Or is
there a simpler way?
(There are many thousands of lines in the input file, so manual editing is not
an option!)
Thank You,
-plh


--
I keep hitting "Esc" -- but I'm still here!