VBA Redim Preserve subscript out of range

[thorpyuk]

Hi All,
I have an array containing 45 items in a single column, and can't for the life of me see whats wrong with this:

dim avarData()
avarData = dic.keys
k = UBound(avarData, 1)
    ReDim Preserve avarData(1 To k, 1 To 6)

I want to add an additional 5 columns into my array and populate them, but i cant get past this redim preserve statement!
k evaluates correctly as 45 by the way. dic.keys refers to a scripting dictionary containing 45 unique items

Thanks all

 

[Kyle123]

Re: VAB Redim Preserve subscript out of range

You can only redim preserve the last dimension of the array. You're trying to resize the first

 

[thorpyuk]

Re: VAB Redim Preserve subscript out of range

Ah, i figured since there was only 1 column it would be both the first and the last.
How do i make it redim the last dimension?

 

[RoryA]

Re: VAB Redim Preserve subscript out of range

You can't change the number of dimensions. You'd have to create a new array and populate it in a loop.

 

[thorpyuk]

Re: VAB Redim Preserve subscript out of range

Thanks both, here's what i've done:

avarData = dic.keys
k = UBound(avarData, 1)
ReDim NewList(1 To k, 1 To 3)
For b = 1 To k
    NewList(b, 1) = avarData(b)
    NewList(b, 2) = avarData(b)
    NewList(b, 3) = avarData(b)
Next b

PS i'm aware that this is replicated 3 identical columns..

 

[pgc01]

Re: VAB Redim Preserve subscript out of range

Hi

Remark:
You may already have taken care of it, but dic.keys will return a zero based array by default.

 

[thorpyuk]

Re: VAB Redim Preserve subscript out of range (sorted)

Hi pgc01, is that why my code is cutting off the first value? I've tried all ways to amend it, but cant seem to get it sorted. Here's what i've got:

avarData = dic.keys

k = UBound(avarData, 1)
ReDim NewList(1 To k, 1 To 6)
For b = 1 To k
    NewList(b, 1) = avarData(b)
    NewList(b, 2) = Application.WorksheetFunction.Index(Sheets("Barrels").Range("D:D"), Application.WorksheetFunction.Match(avarData(b), Sheets("Barrels").Range("E:E"), 0))
    NewList(b, 3) = avarData(b)
    NewList(b, 4) = avarData(b)
    NewList(b, 5) = Format(DateAdd("m", -12, ComboBox1.Value), "MMM YY")
    NewList(b, 6) = Format(Application.WorksheetFunction.SumIfs(Sheets("Barrels").Range("I:I"), Sheets("Barrels").Range("H:H"), Format(DateAdd("m", -12, ComboBox1.Value), "MMM YY"), Sheets("Barrels").Range("E:E"), avarData(b)), "0.0")
Next b


    Me.ListBox1.List = NewList

EDIT - Sorted! Changed for 1 to k to for 0 to k

 

[pgc01]

Re: VAB Redim Preserve subscript out of range (sorted)

Hi pgc01, is that why my code is cutting off the first value?

Yes it is (was) but I see you've solved the problem.
Another way would be to set the option base 1, but that would impact all other arrays in the module.

 

[Rick Rothstein]

Re: VAB Redim Preserve subscript out of range

You can't change the number of dimensions. You'd have to create a new array and populate it in a loop.

If there are less than 65,536 elements in the array, you could also do it this way...

  k = UBound(avarData, 1)
  avarData = Application.Transpose(avarData)
  ReDim avarData(1 To k, 1 To UBound(avarData, 1))
  avarData = Application.Transpose(avarData)

I am not sure how efficient one of these methods are compared to the other, but I thought I would offer it as an alternative.