ReDim Preserve 2D Array

[SeanDamnit]

Hello Internet,

I have a dynamically sized array that I get from a SQL Query RecordSet. One of the fields contains a pipe delimited string that I'd like to parse out and add to the array.

Lets say my data looks like this:

StoreList(0,0) = "District"
StoreList(0,1) = "NCAL2"
SotreList(0,3) = "ALB01"

StoreList(1,0) = "Location"
StoreList(1,1) = "17th & Mission"
StoreList(1,2) = "Crossgates Mall"

StoreList(2,0) = "Comments"
StoreList(2,1) = "FACTID | 11111 | AgentCode | ABCDE | ArmsID | 555"
StoreList(2,2) = "FACTID | 22222 | AgentCode | FGHIJ | ArmsID | 666"

I want it to look like this:

StoreList(0,0) = "District"
StoreList(0,1) = "NCAL2"
StoreList(0,2) = "ALB01"

StoreList(1,0) = "Location"
StoreList(1,1) = "17th & Mission"
StoreList(1,2) = "Crossgates Mall"

StoreList(2,0) = "Comments"
StoreList(2,1) = "FACTID | 11111 | AgentCode | ABCDE | ArmsID | 555"
StoreList(2,2) = "FACTID | 22222 | AgentCode | FGHIJ | ArmsID | 666"

StoreList(3,0) = "FACTID"
StoreList(3,1) = "11111"
StoreList(3,2) = "22222"

StoreList(4,0) = "AgentCode"
StoreList(4,1) = "ABCDE"
StoreList(4,2) = "FGHIJ"

StoreList(5,0) = "ArmsID"
StoreList(5,1) = "555"
StoreList(5,2) = "666"

Problem is that I'm reading ReDim Preserve only works on the last dimension in the array. So....

vCol = UBound(StoreList,1)
vRow = UBound(StoreList,2)

Redim Preserve StoreList(vCol + 3, vRow) ' This doesn't work
ReDim Preserve StoreList(vCol, vRow + 3) ' This does work, but obviously doesn't make sense with the way this array is setup.

Any advise? Is this even the best way to go about this?

Thanks!

 

[Rick Rothstein]

You can only change the last dimension for a multi-dimension array... there is no way to change any other dimension. The only option I can think of for your situation is to reverse the two elements in the array and always address them "backwards" from how they are really laid out. So, if your array is laid out RealArray(Rows, Columns), reverse it so that Columns are first and Rows are second... FakeArray(Columns, Rows)... now you can change how many rows are in the array.

 

[SeanDamnit]

You can only change the last dimension for a multi-dimension array... there is no way to change any other dimension. The only option I can think of for your situation is to reverse the two elements in the array and always address them "backwards" from how they are really laid out. So, if your array is laid out RealArray(Rows, Columns), reverse it so that Columns are first and Rows are second... FakeArray(Columns, Rows)... now you can change how many rows are in the array.

I see now, simple enough. Thanks for the help!