Combining Single Dimension to Multi Dimension Array

[Stathy K]

I haven't seen this anywhere. I'd like to combine a group of single arrays to
a multi-dimension. The item array(s) have 5 characteristics (e.g. A,B,C,D,E).
I'd like to make a new product family array that consists of all the item
arrays
(e.g. A,B,C,D,E
F,G,H,I,E
A,G,H,J,K)
Then I could use the product family array as a look up table to extract
info. I'd like to get away from creating a worksheet to store the info, since
it would only be temporary. Also, since new items are introduced every 6
months I can just add a new single item array, and then rebuild the product
array.
Any ideas?
TIA,
SK

 

[Rick Rothstein]

I don't think I understand what you are ultimately trying to do. From what
you posted, I would ask why you don't just create the multi-dimensional
array right at the beginning and just use it for all your array needs?

 

[Stathy K]

I could do that with the current product line up, and it would work. But I
would think it would be easier to maintain single arrays as products are
added or retired. For example a new product would be (excuse punctuation in
syntax),
ProdA=Array(1/1/09,3456,85,widget,east)
ProdB=Array(1/1/09,7689,90,bolts,east)

And then 6 mos later, I'd like to add/delete single arrays as product lineup
changes:
(New) ProdC=Array(7/1/09,1245,85,west)
And I would only have to delete a line/array when a product is retired.

I would like it to remain as simple as possible, so that other users would
only have to enter a single dim array without needing any fancy VBA
experience.
After original post, I thought about a db but we do so much work in Excel
I'd rather stay native.

Thanks,
SK

 

[Rick Rothstein]

I'm still not 100% sure how you plan to put this idea to use; however, if I
understand your request correctly, there is a way to combine those existing
arrays into a multi-dimensional array... sort of.<g> The following method of
producing a combined array will function the way you want, but the syntax
will look odd. Given these single arrays...

ProdA = Array("1/1/9", 3456, 85, widget, east)
ProdB = Array("1/1/9", 7689, 90, bolts, east)
ProdC = Array("7/1/9", 1245, 85, west)

I notice that the ProdC array has less elements than the other two arrays...
that is okay, this method will allow for that. You can make a
multi-dimensional array from them by doing this...

ComboArray = Array(ProdA, ProdB, ProdC)

Here is where the odd syntax comes in. You do NOT address an element like
this...

ComboArray(2, 1)

but, rather, you do it like this instead...

ComboArray(2)(1)

The indexing mechanism is the same an with a normal multi-dimensional array;
for example, in the above Combo(2)(1) reference, assuming the default Option
Base of 0 (so that array elements default to an initial index of 0), the 2nd
element (1245) of the 3rd array (ProdC) is being addressed. In other words,
the first number is parentheses is the index of the product arrays and the
second number is the index number of the element within that array. You can
find out the upper bound of the product arrays (ProdA, ProdB, etc.) with
this...

UBound(ComboArray)

so that the number of product arrays is UBound(ComboArray)-1, again,
assuming an Option Base of 0. Since each of the arrays that were combined
can possibly have different number of elements in them, you must check the
UBound for each one individually. For example, if you wanted to know the
upper bound of the ProdA array, you would find out using this...

UBound(ComboArray(0))

If you wanted to find the upper bound of the ProdC array, you would do
this...

UBound(ComboArray(2))

That is pretty much it... I sure hope all of the above is clear (if not, let
me know and I'll try to word the concept differently). I cannot think of any
other way to do what you appear to want to do within VB. Well, that is not
entirely true... you might be able to use a Collection to house your product
arrays, but I think the above array method may be more "robust".

 

[Stathy K]

Rick,
Thanks for your feedback. You've given me good guidance on how to move
ahead. I'll apply your concepts and see if it works out.
(Note: All the arrays have the same # of elements; I just typed ProdC
incorrectly.)

Thanks,
Stathy

 

[Rick Rothstein]

If you have any trouble with the concepts I presented to you, feel free to
post back for clarification.

I figured your ProdC entry was mistyped, but it doesn't change anything...
the method I showed you works the same whether the individual array elements
have the same number of elements or not. However, knowing that they do have
the same number of elements, you can then check the upper bound of any one
of the included arrays to get the upper bound for all of them. This means if
you want to know the upper bound of the 2nd element, just executing this...

UBound(ComboArray(0))

will return it. The convenience being that there is always going to be a
first (product) element no matter how many product elements are present in
the ComboArray, so testing against that first element will always work
whether there is one or one hundred product arrays included in the
ComboArray.