Array Problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jim L (anonymous_at_discussions.microsoft.com)
Date: 03/22/04 

Date: Mon, 22 Mar 2004 13:25:59 -0800

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.
 

Relevant Pages

  • Re: ReDim not working as expected. Array expert needed.
    ... MsgBox TypeName) ' Type Variant() ... examples of how array dimensions work in VBA. ... Sub ArrayStudies14() ... Dim MyArray As Variant ' Declare nonarray variant. ...
    (microsoft.public.excel.programming)
  • Re: Need Help With Arrays and Passing Arguments
    ... array definition to illustrate without really thinking about it. ... Dim myArray() As Variant ... > Sub CallTest1() ...
    (microsoft.public.word.vba.general)
  • Re: Vartype() and vbObject
    ... If the range object refers to multiple cells Value returns a variant array, ... Sub Test2 processes TestVar ... I found the TypeName function. ...
    (microsoft.public.excel.programming)
  • Re: Passing arrays through procedures
    ... The problem arises because your Sort function declares its input ... parameter as an array of Variants: ... Sub Sortas Variant) ...
    (microsoft.public.excel.programming)
  • Re: Passing an array to a sub routine
    ... FileHandle, Array, Hash, Method, Regex}? ... The LoadDataFile routine works correctly, but the SaveDataFile ... > sub LoadDataFile ...
    (comp.lang.perl.misc)