Array Problem

[Jim L]

When you define an array as Dim Array() as Variant, you
simply set up a placeholder for a future array.

How do you get excel to recognized that fact. I've got a a
routine that needs to recognized that if the array has not
been filled or ReDim'ed, it needs to exit the sub.
Unfortunately, I can't seem to find the right function to
do this. IsEmpty and IsNull do not work.

Sample code is below. What I'm doing is sending the
location of page breaks to an array and applying those
page breaks elsewhere.

The relavent code is as follows:

Sub Code1
'Main routine
Dim ArrayPB() as Variant

Call Code2(ArrayPB())
Call Code3(ArrayPB)
end sub

Sub Code2(ArrayPB2 as variant)
'routine which captures page breaks

If ActiveSheet.HPageBreaks.Count = 0 Then Exit Sub

ReDim ArrayPB2(ActiveSheet.HPageBreaks.Count) as Variant
x = 1
For each pb in ActiveSheet.HPageBreaks
ArrayPB2(x) = pb.Location.Address
x = x + 1
Next PB

End Sub

Sub Code3(ArrayPB3 as variant)
'rountine which applies page breaks

For X = 1 to ubound(ArrayPB3)
ActiveSheet.Rows(Right(ArrayPB3(x),len(ArrayPB3(x))_
- InStr(2,ArrayPB3(x),"$"))-1).PageBreak =_
xlPageBreakManual
Next x

end sub

The macros crashed on sheets with no defined pagebreaks;
hence, I added the Exit Sub statement. This generates a
subscript out of range error when the routine reaches
the "ActiveSheet.Rows" statement in Code3.

I'm looking for a solution where I could but something
simple such as "If IsEmpty(ArrayPB3) then exit sub" before
the For statement in Code3.

Thanks.

Jim.

 

[Steve Garman]

Something like this?

Sub test()
Dim ArrayPB() As Variant
'ReDim ArrayPB(2)
If noReDim(ArrayPB()) Then Exit Sub
MsgBox "ReDimmed"
End Sub

Function noReDim(arr() As Variant) As Boolean
Dim max&
noReDim = True
On Error Resume Next
max& = UBound(arr)
If Err.Number = 9 Then
Exit Function
End If
noReDim = False
End Function

 

[Guest]

This is less than elegant, but will work

Sub Foo(
Dim r() As Varian
' ReDim r(1 To 4) As Varian
If isArrayEmpty(r) The
MsgBox "r hasn't been sized.
Exit Su
Els
MsgBox "r has been sized
End I
End Su

Private Function isArrayEmpty(ByVal r As Variant) As Boolea
On Error Resume Nex
Dim l As Lon
l = UBound(r
If Err.Number = 9 The
' Subscript out of range
isArrayEmpty = Tru
Els
isArrayEmpty = Fals
End I
Err.Clea
On Error GoTo
End Functio

Also, you have a semantic error. This line

ReDim ArrayPB2(ActiveSheet.HPageBreaks.Count) as Varian

should be

ReDim ArrayPB2(1 to ActiveSheet.HPageBreaks.Count) as Varian

according to your logic in other procedures

HTH

-Brad Vontur

 

[Guest]

Thanks, but I'm trying to get away from writing a
function. I'm looking for something built in or at the
very least, if I don't fill the array, what does excel
think it is or how does it recognize it. I don't ReDim the
array if there are no PageBreaks.

I set Option Base to 1 (which I did not include) so the
ReDim is correct. I always use option base 1, sorry for
the omission.

Thanks again.

Jim.

 

[Guest]

Thanks Steve,

I'm trying to get away from building functions but I had
thought about simply placing an on error goto statement in
the code which would go to an exit sub statement.

You'd think though that there should be an easier way.

Thanks again.

 

[Alan Beban]

When you define an array as Dim Array() as Variant, you
simply set up a placeholder for a future array.

How do you get excel to recognized that fact. I've got a a
routine that needs to recognized that if the array has not
been filled or ReDim'ed, it needs to exit the sub. . . .

I'm looking for a solution where I could but something
simple such as "If IsEmpty(ArrayPB3) then exit sub" before
the For statement in Code3.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might
consider

If ArrayDimensions(ArrayPB3) = 0 then Exit Sub

Alan Beban

 

[Guest]

Thanks, I'll take a look at these.

Jim.

-----Original Message-----
sub. . . .


If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might
consider

If ArrayDimensions(ArrayPB3) = 0 then Exit Sub

Alan Beban
.