Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel vba

ResolvedVersion Standard

Daniel has attended:
Excel VBA Intro Intermediate course

Excel VBA

What is the difference between a "Dictionary" and a "Collection"?
Also how can I create mine and initialise variables (or objects) held within them?

Edited on Fri 4 Apr 2008, 10:08

RE: Excel VBA

Hi Daniel.

"The Dictionary object is a data structure that can contain sets of pairs, where each pair consists of an item, which can be any data type, and a key, which is a unique String value that identifies the item. The Dictionary object is similar in some ways to the VBA Collection object; however, the Dictionary object offers certain features that the Collection object lacks, including:

The Exists method. You can use this method to determine whether a particular key, and its corresponding item, exist in a Dictionary object. The Exists method makes it simpler and more efficient to search a Dictionary object than to search a Collection object.

The CompareMode property. Setting this property specifies the text-comparison mode for the Dictionary object, so that you can search for a key in either a case-sensitive or case-insensitive manner. By default, it's set to BinaryCompare, which means that the Exists method will return True only if it finds a binary match. There's no way to specify a text-comparison mode for a key that retrieves an item from a Collection object.

The Key property. This property enables you to return the key for a particular item in the dictionary. An item in a Collection object also has a key, which you can use to retrieve that item; however, there's no way to retrieve the key itself.

The RemoveAll method. This method removes all items in the Dictionary object. A Collection object, on the other hand, has no method for removing all items at once, although setting the Collection object to Nothing has the same effect.

The primary advantage of the Dictionary object over the Collection object is the fact that it's easier to search a Dictionary object for a given item. Despite this advantage, the Dictionary object does not entirely replace the Collection object. The Collection object is useful in some situations where the Dictionary object is not. For example, if you're creating a custom object model, you can use a Collection object to store a reference to a custom collection, but you can't use a Dictionary object to do this."

Source:
http://visualbasic.ittoolbox.com/documents/popular-q-and-a/dictionary-object-vs-collection-object-1397

I hope this helps.

Regards, Rich

Excel tip:

Moving or Copying Sheets Between Workbooks in Excel 2010

Here's how to move or copy sheets between workbooks in Excel 2010:

Open the sheet you want to move or copy then on the Ribbon click the Home tab. Click Format. Under Organize Sheets, select the option Move or Copy Sheet and then choose where you want the sheet to be moved/copied to.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.