none
REDIM of subarrays in arrays passed by reference RRS feed

  • Question

  • The VBA guide says that arrays cannot be REDIMed in a called procedure and can only be REDIMed in the procedure in which they were declared. I have a function in which I want to process arrays (typically many thousands) that are themselves subarrays in what I call a holding array. I want to REDIM the subarrays and then have the calling code be able to take the holding array which now has all the processed subarrays. I am not REDIMing the holding array.

    Does anyone know if there would be problems in REDIMing the subarrays in the called function?

     
    Friday, May 24, 2013 11:34 AM

Answers

  • There's no problem to pass an array to a different procedure and ReDim as per the example I posted.

    However if in addition to the array you pass an element of the array, eg arr(2,1), by reference indeed Redim will fail as the array will be temporarily locked. Not sure where you got the idea about ending up back at the calling procedure at an unpredictable line though.

    Why not simply try the example I posted, and adapt to your needs.

    Peter Thornton

    • Marked as answer by Nigel Barton Saturday, May 25, 2013 8:17 AM
    Friday, May 24, 2013 10:05 PM
    Moderator

All replies

  • I'm not sure where you read that but array's can be redim'd in procedures they have been passed to. However the array should not be dimensioned when declared, eg

    Sub test()
    Dim arr() As Long
         foo arr
         MsgBox UBound(arr) ' 5
    End Sub
    Function foo(arr() As Long)
         ReDim arr(1 To 5, 1 To 2)
    End Function

    Peter Thornton

    Friday, May 24, 2013 1:22 PM
    Moderator
  • It is in the description of the REDIM statement of the VBA guide for 2013:

    "If you pass an array to a procedure by reference, you can't redimension the array within the procedure."

    See Office and SharePoint development > Office client development > Office 2013 > Office 2013 > Visual Basic for Applications language reference for Office 2013 > Visual Basic language reference > Statements > ReDim Statement

    As far as I recall when I tried this some time back the code compiles OK but makes the procedure bomb out at Runtime. You end up back in the calling procedure at an unpredictable line.

    Friday, May 24, 2013 7:54 PM
  • There's no problem to pass an array to a different procedure and ReDim as per the example I posted.

    However if in addition to the array you pass an element of the array, eg arr(2,1), by reference indeed Redim will fail as the array will be temporarily locked. Not sure where you got the idea about ending up back at the calling procedure at an unpredictable line though.

    Why not simply try the example I posted, and adapt to your needs.

    Peter Thornton

    • Marked as answer by Nigel Barton Saturday, May 25, 2013 8:17 AM
    Friday, May 24, 2013 10:05 PM
    Moderator
  • As you say, why not try it? It seems to work - including rediming of subarrays.

    But, why the comment in the user guide??

    Anyway, thanks for your help.

    Nigel Barton

    Saturday, May 25, 2013 8:17 AM
  • In general VB/A help is comprehensive and accurate, quite a tome of work in itself. However that particular comment is at best ambiguous if not misleading.

    It was written a very long time ago, I suspect the author originally had it right but something got lost in the editing, eg arrays can*only* be passed by reference whereas array elements can be passed ByRef or ByVal.

    Peter Thornton

    Saturday, May 25, 2013 10:19 AM
    Moderator