Can't test for Empty objects in an array
 

I'm writing Excel macros using VBA under XP and I'm having a problem testing
for Empty objects in an array.

The debugger shows an element is Empty, but if I separately test for either
'Is Empty' or 'Is Nothing' I
get 'Object required'

Thanks for your help,
Peter.

Can't test for Empty objects in an array
 

Hi Peter,

I created a (0 to 4) array; I loaded worksheets into array positions 1, 2
and three.

In consequence, array positions 0 and 4 were empty. I tested each position,
using an IsEmpty condition and, as expected, the test returned false for
postions 1-3 and True for positions 0 and 4:


Sub Tester()

Dim Arr(0 To 4)
Dim i As Long
Dim j As Long

For i = 1 To 3
Set Arr(i) = ActiveWorkbook.Sheets(i)
Next i

For j = LBound(Arr) To UBound(Arr)
MsgBox IsEmpty(Arr(j))
Next j

End Sub


---
Regards,
Norman



"Peter Chatterton" wrote in message
.. .
I'm writing Excel macros using VBA under XP and I'm having a problem
testing for Empty objects in an array.

The debugger shows an element is Empty, but if I separately test for
either 'Is Empty' or 'Is Nothing' I
get 'Object required'

Thanks for your help,
Peter.

Can't test for Empty objects in an array
 

Thanks a lot Norman.

What I ended up doing was:
If aAllArrays(ID_WS, i) Is Empty Then Exit For

Peter.


"Norman Jones" wrote in message
...
Hi Peter,

I created a (0 to 4) array; I loaded worksheets into array positions 1, 2
and three.

In consequence, array positions 0 and 4 were empty. I tested each
position, using an IsEmpty condition and, as expected, the test returned
false for postions 1-3 and True for positions 0 and 4:


Sub Tester()

Dim Arr(0 To 4)
Dim i As Long
Dim j As Long

For i = 1 To 3
Set Arr(i) = ActiveWorkbook.Sheets(i)
Next i

For j = LBound(Arr) To UBound(Arr)
MsgBox IsEmpty(Arr(j))
Next j

End Sub


---
Regards,
Norman



"Peter Chatterton" wrote in message
.. .
I'm writing Excel macros using VBA under XP and I'm having a problem
testing for Empty objects in an array.

The debugger shows an element is Empty, but if I separately test for
either 'Is Empty' or 'Is Nothing' I
get 'Object required'

Thanks for your help,
Peter.

Can't test for Empty objects in an array
 

Hi Peter,

What I ended up doing was:
If aAllArrays(ID_WS, i) Is Empty Then Exit For

Are you sure that syntax works for you?

---
Regards,
Norman



"Peter Chatterton" wrote in message
.. .
Thanks a lot Norman.

What I ended up doing was:
If aAllArrays(ID_WS, i) Is Empty Then Exit For

Peter.


"Norman Jones" wrote in message
...
Hi Peter,

I created a (0 to 4) array; I loaded worksheets into array positions 1, 2
and three.

In consequence, array positions 0 and 4 were empty. I tested each
position, using an IsEmpty condition and, as expected, the test returned
false for postions 1-3 and True for positions 0 and 4:


Sub Tester()

Dim Arr(0 To 4)
Dim i As Long
Dim j As Long

For i = 1 To 3
Set Arr(i) = ActiveWorkbook.Sheets(i)
Next i

For j = LBound(Arr) To UBound(Arr)
MsgBox IsEmpty(Arr(j))
Next j

End Sub


---
Regards,
Norman



"Peter Chatterton" wrote in message
.. .
I'm writing Excel macros using VBA under XP and I'm having a problem
testing for Empty objects in an array.

The debugger shows an element is Empty, but if I separately test for
either 'Is Empty' or 'Is Nothing' I
get 'Object required'

Thanks for your help,
Peter.