none
Variable arrays? RRS feed

  • Question

  • Hi

    In my application i have a variable number of records that i read in and want to store (some of the data - e.g. user email address)  in an array.

    At the start of the application I don't known how many records I will be processing. I can create an array like:

    Dim strUser (1 to 5000) as string

    but feel that this is not a great way to do this as surely one day it will fail when I have more that 5000 records!

    I am not know if there a  better way to deal with this issue - but  I am sure you will know one! :)

    thanks

    Peter

     

    Monday, August 21, 2017 2:56 PM

Answers

  • If you initially declare the array as

        Dim strUser() As String

    you can later redimension it as

        ReDim strUser(1 To 5000, 1 To 3)

    With ReDim Preserve, you can only change the last dimension. So after the previous line,

        ReDim Preserve strUser(1 To 5000, 1 To 4)

    would be OK, but

        ReDim Preserve strUser(1 To 5001, 1 To 3)

    would throw an error. So if you need a dynamic two-dimensional array, you'll have to plan carefully.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Chenchen LiModerator Tuesday, August 22, 2017 6:14 AM
    • Marked as answer by py1 Tuesday, August 22, 2017 7:51 AM
    Monday, August 21, 2017 7:54 PM

All replies

  • You can declare the array as dynamic:

        Dim strUser () As String

    If you can determine the number of elements during the execution of the macro, you can use code like this; I will assume that the variable lngCount holds the number of elements to be allocated:

        ReDim strUser(1 To lngCount)

    Alternatively, if you need to add elements one by one as you go, without knowing how many there will be:

        Dim lngCount As Long
        ...
        Do While some_condition
            ...
            lngCount = lngCount + 1
            ReDim Preserve strUser(1 To lngCount)
            strUser(lngCount) = ...
            ...
        Loop


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, August 21, 2017 3:33 PM
  • Hi Hans

    Thanks for the response.

    I understand that if my array is a one dimension array that the Redim Preserve would be fine.

    If I had to change my array t0 2 diensions (e.g. Dim struser (1 to 5000, 1 to 3) as string then this ould not work.

    Would htere be any other mechanism for collecting this data?

    sorry - I know I have chancge the question a bit!

    thanks,

    Peter

    Monday, August 21, 2017 5:25 PM
  • If you initially declare the array as

        Dim strUser() As String

    you can later redimension it as

        ReDim strUser(1 To 5000, 1 To 3)

    With ReDim Preserve, you can only change the last dimension. So after the previous line,

        ReDim Preserve strUser(1 To 5000, 1 To 4)

    would be OK, but

        ReDim Preserve strUser(1 To 5001, 1 To 3)

    would throw an error. So if you need a dynamic two-dimensional array, you'll have to plan carefully.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Chenchen LiModerator Tuesday, August 22, 2017 6:14 AM
    • Marked as answer by py1 Tuesday, August 22, 2017 7:51 AM
    Monday, August 21, 2017 7:54 PM
  • ok thanks Hans
    Tuesday, August 22, 2017 7:51 AM