A 2013 VBA collections

[Dick7Access]

While studying a course on VBA I came up to a lesson on "Collections". It explained how to code a collection but did not say of what use they are. What are collections use for?

 

[The_Doc_Man]

Collections are a concise way of saying "an indefinitely sized gathering of a variable number of distinguishable objects having the same exact data type."

In Access, you have collections such as "AllTables" "AllQueries" "AllForms" etc etc

You always have certain abilities within collections. For instance

collection-object.Count - tells you how many of the object you have

collection-object - points to the nth element of the collection

collection-object.Name - tells you the name of the nth element

collection-object("name") - selects the element of the collecting having that name - but you can get an error if "name" isn't the name of one of the members of the collection.

Usually, you do a SET obj-var = collection("name") and then work through the object variable if you have a lot to do with the object.

This one sometimes gives someone grief. Collections can contain nested collections! For instance, AllTables is a collection of TableDef objects. But ... a TableDef object contains a collection of FieldDef objects and a collection of Index objects.

AllForms is a collection of Form objects. A Form Object contains a collection of Control Objects. Some control objects such as list boxes can be set to multi-select, in which case the control contains a collection of Selected rows (which is a type of object).

External objects can also contain collections. The file system (FileSystem object) is an object with collections. Each disk contains a collection of folders; each folder contains a collection of files.

If you open an Excel workbook object, it contains a collection of sheets. Each sheet contains a collection of rows, each of which contain a collection of cells (representing columns.) Simultaneously, that same sheet contains a collection of columns, each of which contain a collection of cells (representing rows.)

In Word, your document has a collection of paragraphs. Each paragraph contains a collection of words. (No, I'm not exaggerating this for a single minute.) It also has a collection of tables (if the document has any tables). If it is a traditional Word table, that table has collections of Rows and Columns similar to the way Excel does what it does.

I will not bother you with Outlook and Power Point, but yes, they have their share of collections too.

In essence, the Microsoft Common Object Model defines the collection as the way for managing multiple similar but distinguishable objects and it allows for all sorts of hierarchies.

Don't fear collections. They are your friends, even if traversing them is a pain in the toches. Once you figure out collection management paradigms, you have got a significant portion of complex VBA issues solved and understood.

 

[Dick7Access]

Collections are a concise way of saying "an indefinitely sized gathering of a variable number of distinguishable objects having the same exact data type."

In Access, you have collections such as "AllTables" "AllQueries" "AllForms" etc etc

You always have certain abilities within collections. For instance

collection-object.Count - tells you how many of the object you have

collection-object - points to the nth element of the collection

collection-object.Name - tells you the name of the nth element

collection-object("name") - selects the element of the collecting having that name - but you can get an error if "name" isn't the name of one of the members of the collection.

Usually, you do a SET obj-var = collection("name") and then work through the object variable if you have a lot to do with the object.

This one sometimes gives someone grief. Collections can contain nested collections! For instance, AllTables is a collection of TableDef objects. But ... a TableDef object contains a collection of FieldDef objects and a collection of Index objects.

AllForms is a collection of Form objects. A Form Object contains a collection of Control Objects. Some control objects such as list boxes can be set to multi-select, in which case the control contains a collection of Selected rows (which is a type of object).

External objects can also contain collections. The file system (FileSystem object) is an object with collections. Each disk contains a collection of folders; each folder contains a collection of files.

If you open an Excel workbook object, it contains a collection of sheets. Each sheet contains a collection of rows, each of which contain a collection of cells (representing columns.) Simultaneously, that same sheet contains a collection of columns, each of which contain a collection of cells (representing rows.)

In Word, your document has a collection of paragraphs. Each paragraph contains a collection of words. (No, I'm not exaggerating this for a single minute.) It also has a collection of tables (if the document has any tables). If it is a traditional Word table, that table has collections of Rows and Columns similar to the way Excel does what it does.

I will not bother you with Outlook and Power Point, but yes, they have their share of collections too.

In essence, the Microsoft Common Object Model defines the collection as the way for managing multiple similar but distinguishable objects and it allows for all sorts of hierarchies.

Don't fear collections. They are your friends, even if traversing them is a pain in the toches. Once you figure out collection management paradigms, you have got a significant portion of complex VBA issues solved and understood.

What caught my attention was a collection of images in the lesson, as I am working on a db at this time that will have peoples picture under their a name. I now have all the bit maps in a sub folder with the db, Is this a candidate to be put in to a collection and what would be the advantage?

 

[stopher]

What caught my attention was a collection of images in the lesson, as I am working on a db at this time that will have peoples picture under their a name. I now have all the bit maps in a sub folder with the db, Is this a candidate to be put in to a collection and what would be the advantage?

A db table is in essence a collection of records. So if you decided to use a VBA Collection you would be mimicking albeit in a much different way what your database is already doing.

Whether you need to employ a VBA collection really depends on whether you want the ability to manipulate your data "in memory" or not prior to it being saved to a file. Consider the Word and Excel examples that The_Doc_Man gave. The user loads a whole Word file and then edits the document in memory. Only when the user is satisfied with the result does the user decide to save the document back to a file. Contrast this with a typical Access database where the manipulation is done on a record by record basis i.e. as soon as one record is edited it is immediately saved to disk.

So when would you use both a db and a VBA Collection? Suppose the people example you gave is a db designed to manage task management i.e. on a daily basis you have a set of tasks to be done and a set of people to allocate to those tasks. In particular you may not have enough people to do all the tasks and some people may not be skilled in some tasks. In this example you might wish to load the people and tasks into memory (e.g. into VBA collections) so that you can play around with different scenarios until you are satisfied with the allocation. Then you would save the final scenario back to your db.

Of course once you have your data in a VBA Collection you still need to code your interface to interact with your collections (or more likely objects) i.e. display required information from your collections appropriately on screen. But that's another science again.

hth
Chris

 

[Galaxiom]

Collections are a concise way of saying "an indefinitely sized gathering of a variable number of distinguishable objects having the same exact data type."

Although they are generally used to collect objects of the same data type that is not a requirement. It would be somewhat messy to deal with but they can hold a mix of anything.

I prefer the Dictionary from the Scripting library to Collections.

They are very similar except the Dictionary has a searchable index.

 

[Dick7Access]

Although they are generally used to collect objects of the same data type that is not a requirement. It would be somewhat messy to deal with but they can hold a mix of anything.

I prefer the Dictionary from the Scripting library to Collections.

They are very similar except the Dictionary has a searchable index.

Oh! thanks, now I have to study Dictionary!

 

[The_Doc_Man]

I now have all the bit maps in a sub folder with the db, Is this a candidate to be put in to a collection and what would be the advantage?

The trick is not collecting them in memory, but rather linking the images to something else that you might wish to use, and there is already a mechanism for that. An IMAGE control allows you to hyperlink to the image file. The last time I did something like this, all I did was put the image into the folder and put the image's name in a record in a field of the appropriate type. Then, when I navigated on the bound form, I had the OnCurrent routine load the name of the file to the IMAGE control's appropriate link field.

Making your own collection is tricky. I don't know that I've ever seen an article on exactly how to do that. The hard part is to let Access know that something should be treated as a collection in the first place, because your collection isn't based on an intrinsic data type that the COM paradigm normally places in a collection.

The trick is getting Access and the COM paradigm to recognize your collection as such. There is where the can of worms gets opened.

 

[gemma-the-husky]

i find collections to be of limited use. it's probably me, but i have found that a collection is hard to manipulate in the same way that a pointer chain can be managed. it is therefore hard to represent and manipulate structures such as say, a queue, a tree, or a doubly linked list.

i tend to use collections as a replacement for array-type structures in some cases - eg, a collection of attachments to be added to an email - where the order of insertion/retrieval is not too important.

I am sure I could use them more, but there generally seems another way to achieve what i want anyway.

 

[Dick7Access]

The trick is not collecting them in memory, but rather linking the images to something else that you might wish to use, and there is already a mechanism for that. An IMAGE control allows you to hyperlink to the image file. The last time I did something like this, all I did was put the image into the folder and put the image's name in a record in a field of the appropriate type. Then, when I navigated on the bound form, I had the OnCurrent routine load the name of the file to the IMAGE control's appropriate link field.

Making your own collection is tricky. I don't know that I've ever seen an article on exactly how to do that. The hard part is to let Access know that something should be treated as a collection in the first place, because your collection isn't based on an intrinsic data type that the COM paradigm normally places in a collection.

The trick is getting Access and the COM paradigm to recognize your collection as such. There is where the can of worms gets opened.

The course I am studying shows how to code a collection but It didn't tell me where to use it, and it seems from this tread is not something I need.

Now linking images, correct me if I am wrong, is not something that a user could do?

 

[The_Doc_Man]

Linking images is, indeed, something a user can do. I do it via hyperlinks for which the value of the hyperlink is in the database and I just load that value to the image control when I want to display the image.

 

[Dick7Access]

Linking images is, indeed, something a user can do. I do it via hyperlinks for which the value of the hyperlink is in the database and I just load that value to the image control when I want to display the image.

That's what I am getting at. Once I ship it out the door how is the user going to hyperlink the image he wants to put in? Right now I put the image in a folder that is in the same folder at the db file. I copy and paste the image into the image control. I think most end user could do that. Once he gets the db he will be changing 99% of the images. Mine will only be in there so he can get to know the program.

 

[Cronk]

I've used collections from time to time, mostly for opening a number of instances of the same form.

For example, in a training course application where a form is used to show details of a course eg Course name and venue/date, trainer(s), students. The user may be in the middle of editing one course and gets an enquiry about another course.

Multiple instances of the form can be opened, each showing a particular course, by using collections so the other course can be checked without disrupting the edit process on the original course.