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!

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!
>

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
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!

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!

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!
>