topic Re: Array into Array or Collection? in VBA Forum

Array into Array or Collection?

Anonymous — Fri, 10 Aug 2007 00:27:34 GMT

I've been brainstorming on my little project that has to do with making some sort of automated BOM. One problem I ran into is how can I combine arrays?
Here's what I mean:
I prompt users to select a detail (1 detail), I get data from all the parts and build an array, then I could dump it into the BOM form, but I don't want to, I want to store it somehow, then they can select another detail and I store that array, as many details as they want. Then I would complie it all and finnally dump it into excel.
Any ideas?
I'm doing research on collections, not sure how they can be beneficial yet though.
I will also have a dropdown with a name of each detail extracted and when they select one I want to be able to bring that array back up into the list.

Any ideas are welcome.
Thanks,
Viktor.

Re: Array into Array or Collection?

Anonymous — Fri, 10 Aug 2007 01:00:43 GMT

no idea what your array holds but you could just put it in another array or
a collection

if you have a usable array of some data
a1
and you just want to continue collecting other similar arrays

While Not Done
GetNextArray a1
Redim Preserve bigArray(ubound(bigArray)+1)
bigArray(ubound(bigArray) = a1

If condition to end loop Then Done = True
Loop

or add them to a collection
While Not Done
GetNextArray a1
colArrays.Add a1
If condition to end loop Then Done = True
Loop

when you're done
For Each vArray in BigArray
Doarray vArray
or
For Each vArray in colArrays
Doarray vArray

but then that's so obvious maybe i'm not really understanding your question?

hth
Mark

wrote in message news:5685892@discussion.autodesk.com...
I've been brainstorming on my little project that has to do with making some
sort of automated BOM. One problem I ran into is how can I combine arrays?
Here's what I mean:
I prompt users to select a detail (1 detail), I get data from all the parts
and build an array, then I could dump it into the BOM form, but I don't want
to, I want to store it somehow, then they can select another detail and I
store that array, as many details as they want. Then I would complie it all
and finnally dump it into excel.
Any ideas?
I'm doing research on collections, not sure how they can be beneficial yet
though.
I will also have a dropdown with a name of each detail extracted and when
they select one I want to be able to bring that array back up into the list.

Any ideas are welcome.
Thanks,
Viktor.

Re: Array into Array or Collection?

Anonymous — Fri, 10 Aug 2007 08:30:04 GMT

Vik07 wrote:
> I've been brainstorming on my little project that has to do with making some sort of automated BOM. One problem I ran into is how can I combine arrays?
> Here's what I mean:
> I prompt users to select a detail (1 detail), I get data from all the parts and build an array, then I could dump it into the BOM form, but I don't want to, I want to store it somehow, then they can select another detail and I store that array, as many details as they want. Then I would complie it all and finnally dump it into excel.
> Any ideas?
> I'm doing research on collections, not sure how they can be beneficial yet though.
> I will also have a dropdown with a name of each detail extracted and when they select one I want to be able to bring that array back up into the list.
>
> Any ideas are welcome.
> Thanks,
> Viktor.

Not sure if it's what you need but you might want to check out Scripting
Dictionaries. I've been told on here (by Tony Tanzillo?) that they're
faster. There's a few examples in this NG. Don't forget to reference the
Microsoft Scripting Runtime in your project.

Dave F.

Re: Array into Array or Collection?

Anonymous — Fri, 10 Aug 2007 16:42:55 GMT

Thanks for pointers guys. Here's a little analogy of what I'd like to do:
Kind of like creating a book, you take a piece of paper (array) and you write a whole bunch of stuff on it, then you paste it unto a page. Use the next piece of paper and put it unto the next page. Then when you're done you can go thru the pages, one by one.
One other way I was thinking is writing it to a temp database. not sure though.

Re: Array into Array or Collection?

Anonymous — Fri, 10 Aug 2007 17:14:59 GMT

MP could you elaborate a bit more on this section:

or add them to a collection
While Not Done
GetNextArray a1
colArrays.Add a1
If condition to end loop Then Done = True
Loop

Re: Array into Array or Collection?

Anonymous — Mon, 13 Aug 2007 16:27:04 GMT

Per you op
" I get data from all the parts and build an array"
my example assumes you have a sub to perform this service
the arg to the sub is a byref variable a1
Sub GetNextArray (ByRef a1 as Variant)
a1 = getdatafromparts 'however you're doing that
End Sub
...so now a1 is your array of data

you said you wanted to continue collecting multiple arrays before "using"
them
so a loop is required,

colArrays.Add a1
adds the array to a collection

the loop continues til you're "done" collecting
If Then Done = True

' is whatever signal you create to tell you're done
collecting data

after the loop is done, the collection holds all your arrays
then you can call a sub that consumes that collection of arrays of data
Call UseCollectionOfArrays (colArrays)

that sub would loop through all collection items and process them
accordingly

hth
Mark

wrote in message news:5686675@discussion.autodesk.com...
MP could you elaborate a bit more on this section:

or add them to a collection
While Not Done
GetNextArray a1
colArrays.Add a1
If condition to end loop Then Done = True
Loop

Re: Array into Array or Collection?

Anonymous — Mon, 13 Aug 2007 16:59:03 GMT

see if this will help.

Public Bom_Items() As Variant

Public Sub BOM_Array(sPcmark As String, sDesc As String, dLength As Double, dWidth As Double)
Dim Items(3) As Variant

Items(0) = "PcMark"
Items(1) = "sDesc"
Items(2) = dLength
Items(3) = dWidth

ReDim Preserve Bom_Items(UBound(Bom_Items) + 1)
Bom_Items(UBound(Bom_Items)) = Items
Bom_Items(0) = UBound(Bom_Items) - 1

End Sub

Public Sub Bom_Test()
ReDim Bom_Items(0)

Call BOM_Array("pc1", "desc1", 1, 1)
Call BOM_Array("pc2", "desc2", 2, 2)
Call BOM_Array("pc3", "desc3", 3, 3)
Call BOM_Array("pc4", "desc4", 4, 4)
End Sub

Re: Array into Array or Collection?

Anonymous — Mon, 13 Aug 2007 16:59:15 GMT

Re: Array into Array or Collection?

Anonymous — Mon, 13 Aug 2007 17:27:01 GMT

Thanks MP, it's working well for me.

Re: Array into Array or Collection?

Anonymous — Mon, 13 Aug 2007 19:27:37 GMT

Great,
glad to help
Mark
wrote in message news:5688199@discussion.autodesk.com...
Thanks MP, it's working well for me.

Re: Array into Array or Collection?

Anonymous — Mon, 13 Aug 2007 20:22:44 GMT

Hey, wowens9, this works like a charm
Nice trick,
Regards,

~'J'~