Hello All,

I am looping through a range and populating an array which contains a user-defined data type. Now, in that loop I check to make sure the array is not full and if so I redimension it. Please see code sample below:

Sub getIntermediateArray()

    Dim intermediateWkb As Workbook
    Dim intermediateWksht As Worksheet
    Dim intermediateRng As Range
    Dim intermediateRngCounterInt As Integer

    ' Get a handle on the intermediate workbook and worksheet
    Windows(sAggregate).Activate
    Set intermediateWkb = ActiveWorkbook
    Set intermediateWksht = intermediateWkb.ActiveSheet

    intermediateWksht.Range("A1").Select
    intermediateWksht.Range(Selection, Selection.End(xlToRight)).Select
    intermediateWksht.Range(Selection, Selection.End(xlDown)).Select
    Set intermediateRng = Selection

    ' Loop through the range containing the intermediate file
    ' Ignore the first row which contains the column headings ie. start at 2
    For intermediateRngCounterInt = 2 To intermediateRng.Rows.Count
        ' After each pass check the size of the array
        ' If the array size limit has been reached, extend the array size
        If UBound(StockItems) < intermediateRngCounterInt Then
            ReDim Preserve StockItems(UBound(StockItems) + 10)
        End If

        ' Place each row in to a range
        Dim oneStock As Range
        Set oneStock = intermediateRng.Rows(intermediateRngCounterInt)

        ' Query the row range and populate each StockItem in the StockItem array
        StockItems(intermediateRngCounterInt - 1).StockName = oneStock.Cells(1, 1)
        StockItems(intermediateRngCounterInt - 1).AssetClass = oneStock.Cells(1, 3)
        StockItems(intermediateRngCounterInt - 1).Quantity = oneStock.Cells(1, 6)
        StockItems(intermediateRngCounterInt - 1).BankName = oneStock.Cells(1, 13)
    Next intermediateRngCounterInt

    ' Close the intermediate range which we queried to create the StockItem array
    Set intermediateRng = Nothing

End Sub



However, I am receiving an error:
"Compile error: Array already dimensioned"

Any ideas? I only started coding in VBA for the first time last week, so please excuse any stupidness.

Thanks, Selim

I managed to figure out the answer for myself in the end.

You cannot ReDim an array whose initial size has been set.

So in order for this to work:
    For intermediateRngCounterInt = 2 To intermediateRng.Rows.Count
        ' After each pass check the size of the array
        ' If the array size limit has been reached, extend the array size
        If intermediateRngCounterInt > UBound(StockItems) Then
            ReDim Preserve StockItems(UBound(StockItems) + 10)
        End If
    Next intermediateRngCounterInt

The StockItems array must be declared as:
    Public StockItems() As StockItem

I previously had it declared as:
    Public StockItems(1 to 100) As StockItem