Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I test if a cell is empty? | Excel Discussion (Misc queries) | |||
Test if the range is empty | Excel Discussion (Misc queries) | |||
Test for end of array of objects? | Excel Programming | |||
Test if Clipboard is empty | Excel Programming | |||
Better way to test for empty Recordset | Excel Programming |