Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    redim of an array (2003)

    All

    I set out below some code which collects data into an array and then publishes it in a list box on a form. When I previously initially dimensioned the array as IBMArray(1 to 1000, 1 to 2) the list box contained many blank lines. To avoid this I tried to re-dimension the array incrementally as extra lines of data to be displayed are found. However I now receive one of the 2 errors shown at the line indicated by ******.

    Can someone please advise how I can achieve the result that the list box only shows lines with data in them?

    Thanks

    Alex

    Sub UserForm_Initialize()
    Dim j As Integer
    j = 1

    ListBox1.MultiSelect = fmMultiSelectSingle
    Dim IBMArray(1 To 1, 1 To 2) As String

    Dim i As Integer
    Dim k As Integer
    Dim found As Boolean

    For i = 1 To 1
    For j = 1 To 2
    IBMArray(i, j) = ""
    Next j
    Next i

    'The 1st list box contains 3 data columns
    ListBox1.ColumnCount = 2

    j = 1
    'Load integer values into first column of MyArray
    For i = 2 To LastCellInWS(Worksheets("PWO Detail")).Row
    found = False
    For k = 1 To j
    If ThisWorkbook.Worksheets("PWO Detail").Cells(i, 1).Value = IBMArray(k, 1) Then
    found = True
    End If
    Next k
    If found = False Then
    IBMArray(j, 1) = ThisWorkbook.Worksheets("PWO Detail").Cells(i, 1).Value
    IBMArray(j, 2) = ThisWorkbook.Worksheets("PWO Detail").Cells(i, 4).Value
    j = j + 1
    ReDim Preserve IBMArray(1 To j, 1 To 2)
    '*****Get statemment Array already dimensioned******
    '*****Another statement needs to be a constant******
    End If
    Next i

    'Load data into ListBox1 and ListBox2
    ListBox1.List() = IBMArray

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: redim of an array (2003)

    If you originally declare an array with a specified size, this size is static, the array cannot be resized later. To be able to resize an array, you must declare it as a dynamic array. Moreover, if you have a multidimensional array, you can only resize the last dimension if you want to preserve existing array items. So you must reverse the role of the two dimensions.

    ' Declare dynamic array
    Dim IBMArray() As String
    ' Initial size
    ReDim IBMArray(1 To 2, 1 To 1)
    ...
    ...
    ' Resize last dimension while keeping existing items
    ReDim Preserve IBMArray(1 To 2, 1 To j)
    ' Populate new items
    IBMArray(1, j) = ...
    IBMArray(2, j) = ...
    ...

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: redim of an array (2003)

    Also see the recent discussion in the thread starting at <post#=516,196>post 516,196</post: >.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: redim of an array (2003)

    Hi Alex,

    Adding to Hans' comments: if you want to access the array's size dynamically, rather than having to keep track of it, you can use:
    UBound(IBMArray)

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: redim of an array (2003)

    Thanks

    This seems to have fixed the problem

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: redim of an array (2003)

    For the case in hand, would that not be:
    UBound(IBMArray,2)
    or am I misunderstanding something?
    Regards
    Don

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: redim of an array (2003)

    It depends on what upper bound that you want

    UBound(IBMArray) is the same as UBound(IBMArray,1) the first dimension

    UBound(IBMArray,2) is the second dimension

    UBound(IBMArray,3) would be the third dimension if it had one

    Steve

Posting Permissions

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