Results 1 to 15 of 15
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Persistent/ global collection (VBA Excel 2000)

    I'm wondering about the best way to generate an updatable collection of names (strings) with global (project) scope, which will always be available while the workbook is open. The collection is initially generated from values stored on a sheet and these may change, so the collection would need to be able to be rebuilt on demand. I think I could do this with an array, but would prefer the flexibility of traversing a collection with For Each... Next constructs.

    Thanks for any ideas.

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Persistent/ global collection (VBA Excel 2000)

    A collection is a bit more durable than an array and is supposed to be usable with For ... Each. I've never been able to use it that way, so I can't testify to the validity of the help files on this subject, <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> If you build the collection with a text key, you can easily refer to an individual element using the key as well.
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    Thanks Charlotte. I haven't had any problems using my homespun collections with For Each... Next, but my biggest problem is the scoping and persistence aspect I mention.

    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Persistent/ global collection (VBA Excel 2000)

    Declare the collectionand make it public in a standard module in your project. I don't work with Excel enough to make a specific suggestion as to where to declare it.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    You wrote that the list originates in values stored in a sheet. Why not keep them there, or copy them to a hidden sheet? You can loop through the cells in a range with For Each ... Next, and you know for sure that the range will be available whenever the workbook is open.

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    I'm not a big fan of collections based on old experience; perhaps they work as documented now? With complex data, e.g., an integer, a string, and a boolean comprising a single record, I typically use an array of User-Defined Types (UDTs). However, the syntax for that is a clunkier than a variant array, if you don't mind the overhead of variants. Could one construct a collection of UDTs??

    {Testing... testing...}

    The answer to the last question is, not easily. VBA doesn't want to "coerce" the UDT to a variant so that I can insert it into a collection unless the UDT is defined in a "public object module" (which apparently is something other than ThisDocument, a code module, or a class module). Oh well, I prefer arrays anyway. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    Alan,

    As the names are already stored in a collection (a range of cells) that is global and ever available, why not just use the relevant range. When the cell values change, your collection is immediately up to date.

    Or am I missing something ?.


    Andrew C

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    For ... Each works just fine with an array, try the code below.

    <pre>Public Sub Test()
    Dim strW(3) As String, strWk As Variant
    strW(0) = "String 1"
    strW(1) = "String 2"
    strW(2) = "String 3"
    strW(3) = "String 4"
    For Each strWk In strW()
    MsgBox strWk
    Next strWk
    End Sub
    </pre>


    However, I agree with the others that leaving it in a range on the worksheet is probably best based on what you have said so far.
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    Thanks Hans, and to everybody else for their input. I had thought of the named range alternative. I could generate it in known empty space on the sheet from which the data is generated. This sheet is, in fact, already "very hidden" so it represents a good repository.

    The only thing that really swayed me towards a collection (or even array*) is that a named range appears in the dropdown Name Box, which is something I'd rather avoid. Is there any way to exclude this? If so, there's nothing really wrong with that solution, since the "collection" is just a fairly lightweight group of strings.

    thanks

    Alan

    * An array would have worked quite satisfactorily, but I didn't believe the For Each... Next construct would be easily adaptable (if at all) to arrays. Legare showed that it is, but the thing I don't ever know with certainty is how many values I'll be working with. An array is still workable in such a case, but not as neat as a collection in these circumstances. True?

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    Named ranges do have a visible property, which when set to False, so removing the name from the dropdown namebox. The following code will create an "nvisible" named range :

    ActiveWorkbook.Names.Add "TestName", Range("A1:A100"), False

    Andrew C

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    Excellent! Thanks Andrew <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>. I think we have a winner <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    Alan

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    Why do you need the name at all? If you know the sheet name, and the first cell where the list is kept, the code should be able to find the end of the range.
    Legare Coleman

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    You might consider downloading my Name Manager from the webpage below.

    It will however, also reveal your hidden names to others using this utility, so don't count on them not noticing your names.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    Thanks Jan. I'll certainly d/l to add it to my kit. I'm not really worried about total concealment, more removing temptation/ confusion from the casual users of this project. They tend to be at the lower end of the computer literacy scale (certainly with Excel) and they (naturally) tend to start playing around with things when they don't understand how to do something... and what does this button do? All I'm wanting to do in this context is to keep the interface as plain as possible, save for the features I want to "draw" them towards using.

    Alan

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Persistent/ global collection (VBA Excel 2000)

    I'm sure you're correct on that Legare <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. It's just that I use this "list" so often that I wanted a clean way of doing it, and a collection seemed very appealing at first. I do have the named range option working now and it seems to be a satisfactory solution. I don't doubt that there are other ways of achieving it, but the "appeal" here is being able to use the For Each... Next construct neatly on said range.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •