Array vs. Collection

Discussion in 'Microsoft Excel Programming' started by RyanH, Sep 18, 2008.

  1. RyanH

    RyanH Guest

    I have a Userform that when it is initialized I need to fill an Array or
    Collection with 1 to 8 Cell Values. Is there a difference between Array and
    Collection? Differences other than Array's hold values, Collection holds
    objects.
    --
    Cheers,
    Ryan
     
    RyanH, Sep 18, 2008
    #1
    1. Advertisements

  2. Collections and Dictionary objects have a key associated with them. You can
    directly access an item in the collection (or dictionary) if you know the
    key. To find a specific item in an array, unless you happen to know the index
    number you need to traverse the array to find the value.

    Think of it like worksheets. You can access a worksheet if you know the tab
    name. You do not need to know the index number. The tab name is the key value
    for the collection.

    --
    HTH...

    Jim Thomlinson


    "RyanH" wrote:

    > I have a Userform that when it is initialized I need to fill an Array or
    > Collection with 1 to 8 Cell Values. Is there a difference between Array and
    > Collection? Differences other than Array's hold values, Collection holds
    > objects.
    > --
    > Cheers,
    > Ryan
     
    Jim Thomlinson, Sep 18, 2008
    #2
    1. Advertisements

  3. RyanH

    JP Guest

    On top of what Jim stated, a Collection object can also be used to
    hold a unique list of items (similar to what you can do with Advanced
    Filter to eliminate duplicates in a list).

    See http://j-walk.com/ss/Excel/tips/tip47.htm for more info.

    --JP


    On Sep 18, 2:14 pm, RyanH <> wrote:
    > I have a Userform that when it is initialized I need to fill an Array or
    > Collection with 1 to 8 Cell Values.  Is there a difference between Array and
    > Collection?  Differences other than Array's hold values, Collection holds
    > objects.
    > --
    > Cheers,
    > Ryan
     
    JP, Sep 18, 2008
    #3
  4. Note to the RyanH... You can also late bind to get a dictionary object with

    dim dic as object
    set dic = createobject("scripting.dictionary")

    While you loose the intellisence doing this your code becomes portable
    without having to set library references at design time... Develope with the
    reference in place and then modify to late binding... There is a slight
    performance hit to late binding but it is pretty small.
    --
    HTH...

    Jim Thomlinson


    "Chip Pearson" wrote:

    > An array can be used to simply store a series of related values. However,
    > the only way of getting a value from an array is to use the positional index
    > into the array.
    >
    > For example, consider the following code
    >
    > '''''''''''' Array Example
    > Dim Arr(1 To 4)
    > Arr(1) = "a"
    > Arr(2) = "b"
    > Arr(3) = "c"
    > Arr(4) = "d"
    > Debug.Print Arr(3) ' displays "c"
    >
    > Here, the only way to get an element from the array is to use the index
    > number (3 in this example).
    >
    > '''''''''''''' Collection Example
    > Dim Coll As Collection
    > Set Coll = New Collection
    > Coll.Add 11, "My1" ' value is first parameter, key is second parameter. key
    > must be a (unique) string
    > Coll.Add 12, "My2"
    > Coll.Add 13, "My3"
    > Coll.Add 14, "My4"
    > ' access by array index only
    > Debug.Print Coll(3) ' displays 13
    > Debug.Print Coll("My3") 'displays 13
    >
    > Here, you can access an element in the collection by its sequential index
    > number (3 in this example) or by the unique key assigned to the item in the
    > collection ("My3" in this example). Sadly, given a collection, there is no
    > way to get a list of the keys (e.g., get the list "My1", "My2",....).
    > However, the Dictionary object gives you all the functionality of a
    > Collection but also allows you to retreive a list of the keys. For example,
    >
    > Dim Dict As Dictionary
    > Dim S() As Variant
    > Dim N As Long
    > Set Dict = New Dictionary
    > Dict.Add "My1", 1 ' order of key and valuie is reversed re: Collection.
    > Key first, value second. Key must be string.
    > Dict.Add "My2", 2
    > Dict.Add "My3", 3
    >
    > ' access by positional index
    > Debug.Print Dict(2) ' returns 2
    > ' access by key value
    > Debug.Print Dict("My2") ' returns 2
    > ' get all keys into an array
    > S = Dict.Keys
    > For N = LBound(S) To UBound(S)
    > ' list all the keys
    > Debug.Print "Key: " & S(N)
    > Next N
    >
    > In order to use a Dictionary object, you must set a reference to the
    > "Microsoft Scripting Runtime" library. In VBA, go to the Tools menu, choose
    > References, and scroll down the list until you find "Microsoft Scripting
    > Runtime". Check the box next to that entry.
    >
    > See also http://www.cpearson.com/Excel/CollectionsAndDictionaries.htm for
    > tons of code regarding Dictionaries and Collections and how to convert
    > between the two and converting on or the other to arrays and/or ranges.
    >
    >
    > --
    > Cordially,
    > Chip Pearson
    > Microsoft Most Valuable Professional
    > Excel Product Group
    > Pearson Software Consulting, LLC
    > www.cpearson.com
    > (email on web site)
    >
    >
    >
    >
    > "RyanH" <> wrote in message
    > news:...
    > >I have a Userform that when it is initialized I need to fill an Array or
    > > Collection with 1 to 8 Cell Values. Is there a difference between Array
    > > and
    > > Collection? Differences other than Array's hold values, Collection holds
    > > objects.
    > > --
    > > Cheers,
    > > Ryan

    >
     
    Jim Thomlinson, Sep 18, 2008
    #4
  5. RyanH

    Lonnie M. Guest

    Just to chime in, I recomend giving it a shot and use a collection or
    dictionary, even if an array will work. There will come a time when
    you will need to use one of these techniques and the more you get
    comfortable with them now the better. Another thing that is worth
    looking into is using ADO recordsets stored in memory. They are easily
    sorted and have number of useful methods, like 'Find', and many other
    useful techniques that are good to have ready in your back-pocket. ADO
    recordsets require you to reference the appropriate Microsoft ADO
    Extensibility library.

    Good luck—Lonnie M.
     
    Lonnie M., Sep 19, 2008
    #5
  6. RyanH

    Lonnie M. Guest

    Must be getting tired:
    > ADO recordsets require you to reference the appropriate Microsoft ADO
    > Extensibility library.


    Should reference the appropriate Microsoft ActiveX Data Objects
    Library (usually 2.1).

    Good luck—Lonnie M.
     
    Lonnie M., Sep 19, 2008
    #6
  7. RyanH

    JP Guest

    So what did you end up choosing?

    --JP

    On Sep 18, 2:14 pm, RyanH <> wrote:
    > I have a Userform that when it is initialized I need to fill an Array or
    > Collection with 1 to 8 Cell Values.  Is there a difference between Array and
    > Collection?  Differences other than Array's hold values, Collection holds
    > objects.
    > --
    > Cheers,
    > Ryan
     
    JP, Sep 19, 2008
    #7
  8. RyanH

    RyanH Guest

    Chip, you said:
    >However, the Dictionary object gives you all the functionality of a
    >Collection but also allows you to retreive a list of the keys.


    I'm trying to understand the difference in Dictionary Object and Collection.
    Can't you use this code to retrieve the list of keys in a collection as you
    did with the Dictionary Object?

    Sub GetKeys()

    For i = 0 to colMyCollection
    Debug.Print "Key" & i & ": " & colMyCollection(i)
    Next i

    End Sub

    --
    Cheers,
    Ryan


    "Chip Pearson" wrote:

    > An array can be used to simply store a series of related values. However,
    > the only way of getting a value from an array is to use the positional index
    > into the array.
    >
    > For example, consider the following code
    >
    > '''''''''''' Array Example
    > Dim Arr(1 To 4)
    > Arr(1) = "a"
    > Arr(2) = "b"
    > Arr(3) = "c"
    > Arr(4) = "d"
    > Debug.Print Arr(3) ' displays "c"
    >
    > Here, the only way to get an element from the array is to use the index
    > number (3 in this example).
    >
    > '''''''''''''' Collection Example
    > Dim Coll As Collection
    > Set Coll = New Collection
    > Coll.Add 11, "My1" ' value is first parameter, key is second parameter. key
    > must be a (unique) string
    > Coll.Add 12, "My2"
    > Coll.Add 13, "My3"
    > Coll.Add 14, "My4"
    > ' access by array index only
    > Debug.Print Coll(3) ' displays 13
    > Debug.Print Coll("My3") 'displays 13
    >
    > Here, you can access an element in the collection by its sequential index
    > number (3 in this example) or by the unique key assigned to the item in the
    > collection ("My3" in this example). Sadly, given a collection, there is no
    > way to get a list of the keys (e.g., get the list "My1", "My2",....).
    > However, the Dictionary object gives you all the functionality of a
    > Collection but also allows you to retreive a list of the keys. For example,
    >
    > Dim Dict As Dictionary
    > Dim S() As Variant
    > Dim N As Long
    > Set Dict = New Dictionary
    > Dict.Add "My1", 1 ' order of key and valuie is reversed re: Collection.
    > Key first, value second. Key must be string.
    > Dict.Add "My2", 2
    > Dict.Add "My3", 3
    >
    > ' access by positional index
    > Debug.Print Dict(2) ' returns 2
    > ' access by key value
    > Debug.Print Dict("My2") ' returns 2
    > ' get all keys into an array
    > S = Dict.Keys
    > For N = LBound(S) To UBound(S)
    > ' list all the keys
    > Debug.Print "Key: " & S(N)
    > Next N
    >
    > In order to use a Dictionary object, you must set a reference to the
    > "Microsoft Scripting Runtime" library. In VBA, go to the Tools menu, choose
    > References, and scroll down the list until you find "Microsoft Scripting
    > Runtime". Check the box next to that entry.
    >
    > See also http://www.cpearson.com/Excel/CollectionsAndDictionaries.htm for
    > tons of code regarding Dictionaries and Collections and how to convert
    > between the two and converting on or the other to arrays and/or ranges.
    >
    >
    > --
    > Cordially,
    > Chip Pearson
    > Microsoft Most Valuable Professional
    > Excel Product Group
    > Pearson Software Consulting, LLC
    > www.cpearson.com
    > (email on web site)
    >
    >
    >
    >
    > "RyanH" <> wrote in message
    > news:...
    > >I have a Userform that when it is initialized I need to fill an Array or
    > > Collection with 1 to 8 Cell Values. Is there a difference between Array
    > > and
    > > Collection? Differences other than Array's hold values, Collection holds
    > > objects.
    > > --
    > > Cheers,
    > > Ryan

    >
     
    RyanH, Oct 24, 2008
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Re: Writing to An Array or Collection

    Guest, Aug 23, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    169
    Tom Ogilvy
    Aug 23, 2005
  2. Guest

    VSTO: Sheets collection a 1-based array(?!?!?)

    Guest, Nov 10, 2005, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    177
    Guest
    Nov 10, 2005
  3. Guest

    Collection vs. Array of Arrays, nszim

    Guest, Feb 5, 2006, in forum: Microsoft Excel Programming
    Replies:
    14
    Views:
    442
    vba.NetPlease
    Aug 13, 2008
  4. Guest
    Replies:
    6
    Views:
    421
    RichardSchollar
    Jul 8, 2007
  5. Erazmus
    Replies:
    2
    Views:
    276
    Erazmus
    Sep 17, 2007
Loading...

Share This Page