Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quick Question: Dynamic Array (VBA)

    Hello, can anyone help me? I was trying to calculate the minimum value of a dynamic Array. The program was very simple:

    Sub test()
    dim myarray() as integer
    dim x as integer
    dim i as integer

    Redim myarray(15)
    for i=1 to 15
    myarray(i)=i
    next i

    x = Application.WorksheetFunction.Application.Min(data )

    End sub

    I expected "x" to be 1 in this sample program. But it ended up being 0. Why? Any suggestions?

    Thanks a lot.

    JJ.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Quick Question: Dynamic Array (VBA)

    By default, VB starts the indexing of arrays at 0, so in your case you have an array of 16 elements from 0 o 15, with element 0 having a value 0. To get around this you have a number of options,

    1
    Redim myarray(14)
    for i=0 to 14
    myarray(i)=i + 1
    next i

    2
    Redim myarray(1 to 15)
    for i=1 to 15
    myarray(i)=i
    next i

    3
    Include Option Base 1 at the very top of your module. This overrides the default zero based system, and starts with 1

    Andrew C

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Quick Question: Dynamic Array (VBA)

    A second way to attack it is to declare both the lower and upper bounds on the array, Dim myArray(1 to 15).

  4. #4
    New Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quick Question: Dynamic Array (VBA)

    Thank you, Andrew and jscher2000. You guys saved me at least 2 hours of work - I was cracking a very annoying program this weekend.
    BTW, I used "Option Base 1" because I want my whole program to be based on this.
    Thanks again.

    JJ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •