Can't test for Empty objects in an array

[Peter Chatterton]

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.

 

[Norman Jones]

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

 

[Peter Chatterton]

Thanks a lot Norman.

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

Peter.

 

[Norman Jones]

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?