Dynamic Creation of Arrays - Sorting into Bins problem

[Chris Flockhart]

Hi

In my vba code, I have an array of "structures", where the structure
has several fields including a User Name and other parameters. The
list is effectively unsorted as it has been created by reading a file
that contains entries in chronological order.

So far so good.

What I now want to do is to go through this array and produce a set of
new arrays, one per individual User Name, which each contain entries
from the original array that are specific to that UserName.

I also want to maintain a further array which keeps track of all of
the User specific arrays I have created.

This is a classic issue of processing a list into bins, dynamically
creating and reallocating the result bins as each item in the original
list is processed. I would have no problem implementing this in C,C++,
or Java, but I am unsure as to how to create an array from thin air at
run time in VBA.

I do not want to use magic numbers to create a "large enough" array,
as I would like this solution to be scalable.

Can anyone either describe how to create arrays completely dynamically
in vba, or point me at code which handles the sorting into bins
problem.

Thanks

Chris

 

[Jim Cone]

A very good place to start are these 3 subject areas in Excel VBA help...
Using Arrays
Declaring Arrays
Understanding Parameter Arrays
'---
Sub Demo()
Dim aThinAir() As String
Dim x As Long
Dim y As Long
Dim z As Long

x = 10: y = 5: z = 5
ReDim aThinAir(1 To x, 1 To y, 1 To z)
aThinAir(1, 2, 3) = "sludge"

MsgBox aThinAir(1, 2, 3)
End Sub
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)





"Chris Flockhart" <[email protected]>
wrote in message
news:84919946-78c9-4792-b1d5-8cbc1215a77b@a17g2000yqk.googlegroups.com...

 

[GS]

Hi

In my vba code, I have an array of "structures", where the structure
has several fields including a User Name and other parameters. The
list is effectively unsorted as it has been created by reading a file
that contains entries in chronological order.

So far so good.

What I now want to do is to go through this array and produce a set of
new arrays, one per individual User Name, which each contain entries
from the original array that are specific to that UserName.

I also want to maintain a further array which keeps track of all of
the User specific arrays I have created.

This is a classic issue of processing a list into bins, dynamically
creating and reallocating the result bins as each item in the original
list is processed. I would have no problem implementing this in C,C++,
or Java, but I am unsure as to how to create an array from thin air at
run time in VBA.

I do not want to use magic numbers to create a "large enough" array,
as I would like this solution to be scalable.

Can anyone either describe how to create arrays completely dynamically
in vba, or point me at code which handles the sorting into bins
problem.

Thanks

Chris

Jim's suggestion is indeed a good place to start!

Typically, I dump the file contents into a Variant (causing a dynamic
array) using the Split() function and specifying vbCrLf as the
delimiter. My files include headings (field names) in the first line,
but this is not necessary if, as you say, the data is structured.

Each line of the file contains a 'record' where each field of data is
delimited by a comma (or some other character). Looping the dynamic
array lets me Split() each element into a temp array to process it
however I need.

Perhaps this approach might work for you.