Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: ReDim Nightmare

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ReDim Nightmare

    I have the following code:
    If intLiveRecord > UBound(strAppSettings, 2) Then
    Debug.Print UBound(strAppSettings, 1)
    Debug.Print UBound(strAppSettings, 2)
    Debug.Print intLiveRecord
    Stop
    ReDim Preserve strAppSettings(4, intLiveRecord)
    End If
    which fails on the ReDim line with 'subscript out of range'.
    Where am i going wrong please?
    I did start out trying to ReDim the first element but spotted that error in online help, now i have lost the thread completely!
    intLiveRecord is an integer, i have option base 1 set.
    the debug prints 4,2,3 so...
    to my way of thinking the array is currently(4,2) and ReDim should change this to (4,3) ?
    and keep the contents.

  2. #2
    calacuccia
    Guest

    Re: ReDim Nightmare

    This procedure runs smoothly for me:

    Dim mArray()
    ReDim mArray(4, 2)
    For i = 0 To UBound(mArray, 1)
    For j = 0 To UBound(mArray, 2)
    mArray(i, j) = i & j
    Next j
    Next i
    ReDim Preserve mArray(4, 3)

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    fatherjack,

    Once you have redimmed an array with values, you can only redimension (with preserve) the second dimension.

    So in this code:
    <pre>Dim strArray() As String
    ReDim Preserve strArray(2, 3)

    ReDim Preserve strArray(2, 8)
    ReDim Preserve strArray(5, 2) ' This falls over
    </pre>


    the second redim does not work.

    and again:
    <pre>Dim strArray() As String
    ReDim Preserve strArray(5, 2)
    ReDim Preserve strArray(2, 2) ' This falls over
    </pre>


    I guess it's to do with the way arrays are stored internally.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    calacuccia
    Guest

    Re: ReDim Nightmare

    g,

    He did redim the upper dimension.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    Yes, you're right.

    Maybe this would work:

    <pre>Dim intLiveRecord As Integer
    Dim strAppSettings() As String
    ReDim strAppSettings(1 To 7, 1 To 4)
    intLiveRecord = 8
    If intLiveRecord > UBound(strAppSettings, 2) Then
    Debug.Print UBound(strAppSettings, 1)
    Debug.Print UBound(strAppSettings, 2)
    Debug.Print intLiveRecord

    ReDim Preserve strAppSettings(1 To 7, 1 To intLiveRecord)
    End If
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    This runs for me in Excel 2000:

    <pre>Dim intLiveRecord As Integer, strAppSettings() As String
    ReDim strAppSettings(4, 2)
    intLiveRecord = 3
    If intLiveRecord > UBound(strAppSettings, 2) Then
    Debug.Print UBound(strAppSettings, 1)
    Debug.Print UBound(strAppSettings, 2)
    Debug.Print intLiveRecord
    'Stop
    ReDim Preserve strAppSettings(4, intLiveRecord)
    End If
    </pre>

    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    thanks for all the responses so far.
    I have now gone around the problem by setting the array to the largest possible size (11,4) so not too big a drain on resources. I just wanted to be as economical as possible!
    I will wait to tackle ReDim Preserve another day.
    thanks again

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    OK you guys. This works (well, at least it doesn't generate a syntax error).

    I believe the trick is you gotta REDIM and array to a size before you can REDIM PRESERVE.


    BTW. What on earth is an executable statement doing immediately after a STOP statement? Just curious (and being mischievous!)



    <pre>Sub TEST()

    'New below
    Dim intLiveRecord As Integer
    Dim strAppSettings() As String
    ReDim strAppSettings(2, 2)
    'New above
    If intLiveRecord > UBound(strAppSettings, 2) Then
    Debug.Print UBound(strAppSettings, 1)
    Debug.Print UBound(strAppSettings, 2)
    Debug.Print intLiveRecord
    Stop
    ReDim Preserve strAppSettings(4, intLiveRecord)
    End If
    End Sub
    </pre>


  9. #9
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    Chris,

    Your code does not produce a run-time error because "intLiveRecord" is zero, and the code in the "if" statement does not get executed. If you set intLiverecord to 6 at the start, then you get an error.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    >BTW. What on earth is an executable statement doing immediately after a STOP statement?

    Chris,

    The "stop" statement just pauses execution. If you run the code from outside the VBE editor, it will pause on that statement.

    Here's what the MS help has to say:
    <hr>
    You can place Stop statements anywhere in procedures to suspend execution. Using the Stop statement is similar to setting a breakpoint in the code.

    The Stop statement suspends execution, but unlike End, it doesn't close any files or clear variables, unless it is in a compiled executable (.exe) file.
    <hr>

    I prefer to use breakpoints myself though, as they are not kept with the code- and I dodn't have to remember to remove them before going live with the code.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    OK. This runs. I'd made my first REDIM to be (2, instead of (4,



    <pre>Sub TEST()
    'New below
    Dim intLiveRecord As Integer
    Dim strAppSettings() As String
    ReDim strAppSettings(4, 2)
    intLiveRecord = 6
    'New above
    If intLiveRecord > UBound(strAppSettings, 2) Then
    Debug.Print UBound(strAppSettings, 1)
    Debug.Print UBound(strAppSettings, 2)
    Debug.Print intLiveRecord
    Stop
    ReDim Preserve strAppSettings(4, intLiveRecord)
    End If
    End Sub

    </pre>


  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    > don't have to remember to remove them before going live with

    I agree.

    My age was showing. I've used languages where STOP meant STOP THE COMPUTER, or at least STOP THE INTERPRETER.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    Hey FatherJack,

    I feel like the whodunnit, where it turns out the person whodunnit did not appear in the story until the solution!

    But your solution was great. A good example of lateral thinking (though we poor sods who where trying to address the original problem, and not the real need, did not stand a chance).

    I have, on occasions, read thorough a sequential file jsut to determine how many records, then dimensioned (once), and then read through the file again. That's often more to do with 2-dimensional arrays than with anything else.

    Redim preserve apparently has a performance disadvantage- although I suspect it's not really important in 99.99% of cases.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    Sheesh, GeoffW! You're moderator. Can't you change his name to FatherHack (grin!)

  15. #15
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim Nightmare

    Even I would not do that, for fear of risking a dose of warts from She Who Must Be Obeyed (the swmbo in my own house is a Lower Case swmbo by comparison).
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Page 1 of 2 12 LastLast

Posting Permissions

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