How do you tell if an object array element is set? For example, I have a
class I've made which is dimensioned as an array in my program:
dim roll() as cRoll

I've tried using statements like
If roll(i) is nothing then ...
but that gives a subscript out of range error if I haven't already done
something like
set roll(i) = new cRoll

I've also attempted using things like
if roll is null then ...
if roll is empty then ...
but nothing works for me.

When I have this problem with finding whether a worksheet exists I use
something like the following, which, by the way, I find rather cumbersome.
It would make more sense to me to have something simple like:
if not worksheet("sheetName") is nothing then....
but instead I need to use the 8 or so lines of code shown below

Can someone straighten me out?

dim wk as worksheet, alreadyThere as boolean
for each wk is worksheets
if wk.name="sheetName" then
alreadyThere = true
exit for
end if
next
If not already there then
worksheets.add ... etc....

Instead of an array why not use a collection to hold your objects. That way
you can use code something like this

Sub test()
Dim col As Collection
Dim wks As Worksheet
Set col = New Collection

col.Add Sheet1, Sheet1.Name
col.Add Sheet2, Sheet2.Name

On Error Resume Next
Set wks = col("Sheet1")
On Error GoTo 0
If Not wks Is Nothing Then
MsgBox wks.Name
Else
MsgBox "Sheet1 does not exist"
End If
Set wks = Nothing

On Error Resume Next
Set wks = col("Sheet3")
On Error GoTo 0
If Not wks Is Nothing Then
MsgBox wks.Name
Else
MsgBox "Sheet3 does not exist"
End If
Set wks = Nothing
End Sub

and you can check if a sheet exists similar to above or you can use...

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

--
HTH...

Jim Thomlinson


"T_o_n_y" wrote:

How do you tell if an object array element is set? For example, I have a
class I've made which is dimensioned as an array in my program:
dim roll() as cRoll

I've tried using statements like
If roll(i) is nothing then ...
but that gives a subscript out of range error if I haven't already done
something like
set roll(i) = new cRoll

I've also attempted using things like
if roll is null then ...
if roll is empty then ...
but nothing works for me.

When I have this problem with finding whether a worksheet exists I use
something like the following, which, by the way, I find rather cumbersome.
It would make more sense to me to have something simple like:
if not worksheet("sheetName") is nothing then....
but instead I need to use the 8 or so lines of code shown below

Can someone straighten me out?

dim wk as worksheet, alreadyThere as boolean
for each wk is worksheets
if wk.name="sheetName" then
alreadyThere = true
exit for
end if
next
If not already there then
worksheets.add ... etc....

You could use a function (from Chip Pearson) to test the existence of a sheet.

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function

'and you can use it like:
....
if worksheetexists("myname",activeworkbook) then

========
Lots of times, I'll just check with this kind of code:

Dim wks as worksheet
....
set wks = nothing
on error resume next
set wks = worksheets("somesheetname")
on error goto 0
if wks is nothing then
'not there
else
'it is there
end if

===========
Chip also posted this function:

Function IsArrayAllocated(Arr As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = IsArray(Arr) And _
Not IsError(LBound(Arr)) And _
LBound(Arr) <= UBound(Arr)
End Function



T_o_n_y wrote:

How do you tell if an object array element is set? For example, I have a
class I've made which is dimensioned as an array in my program:
dim roll() as cRoll

I've tried using statements like
If roll(i) is nothing then ...
but that gives a subscript out of range error if I haven't already done
something like
set roll(i) = new cRoll

I've also attempted using things like
if roll is null then ...
if roll is empty then ...
but nothing works for me.

When I have this problem with finding whether a worksheet exists I use
something like the following, which, by the way, I find rather cumbersome.
It would make more sense to me to have something simple like:
if not worksheet("sheetName") is nothing then....
but instead I need to use the 8 or so lines of code shown below

Can someone straighten me out?

dim wk as worksheet, alreadyThere as boolean
for each wk is worksheets
if wk.name="sheetName" then
alreadyThere = true
exit for
end if
next
If not already there then
worksheets.add ... etc....

--

Dave Peterson

You can test whether an array has been allocated with the following
function:

Function IsArrayAllocated(Arr As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = IsArray(Arr) And _
Not IsError(LBound(Arr)) And _
UBound(Arr) = LBound(Arr)
End Function

It returns True if Arr is an array that has been allocated. It returns False
for an unallocated array or any non-array variable type.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"T_o_n_y" wrote in message
...
How do you tell if an object array element is set? For example, I have a
class I've made which is dimensioned as an array in my program:
dim roll() as cRoll

I've tried using statements like
If roll(i) is nothing then ...
but that gives a subscript out of range error if I haven't already done
something like
set roll(i) = new cRoll

I've also attempted using things like
if roll is null then ...
if roll is empty then ...
but nothing works for me.

When I have this problem with finding whether a worksheet exists I use
something like the following, which, by the way, I find rather cumbersome.
It would make more sense to me to have something simple like:
if not worksheet("sheetName") is nothing then....
but instead I need to use the 8 or so lines of code shown below

Can someone straighten me out?

dim wk as worksheet, alreadyThere as boolean
for each wk is worksheets
if wk.name="sheetName" then
alreadyThere = true
exit for
end if
next
If not already there then
worksheets.add ... etc....