Dictionary VBA Class update

I’ve uploaded a new version of the Dictionary Class and a helper KeyValuePair Class. This exposes the KeyValuePairs collection so iteration is now very fast, about 2 seconds to iterate 1 million items using a for each loop:

 
dim oKeyValuePair as KeyItemPair 
For Each oKeyValuePair In td.KeyValuePairs ' direct access to collection
   var = oKeyValuePair.Value 'either of .key,.value
Next

Updated 25-Nov-11: The need for the helper class is to allow updates to the .Item without changing the order.

Advertisements

About Patrick O'Beirne, spreadsheet auditor

Patrick provides consultancy and training in spreadsheet development, auditing / testing and model review; and the Excel addin XLtest
This entry was posted in Excel/VBA and tagged , , , , . Bookmark the permalink.

23 Responses to Dictionary VBA Class update

  1. Pingback: Dictionary Class in VBA instead of Scripting.Dictionary | Patrick O'Beirne @ sysmod

  2. SJT says:

    Thanks for the quick reply. I will get try that tomorrow; sound like that should do it.

  3. SJT says:

    Partially successful. Added the code to my wbk and it works. However, I want to save the wbk as a Excel 2007 macro-enabled template. I can save it, but when I start a new wbk with the template, I am not allowed to save the wbk. Error message says VB project will be lost but it will not save either with or without the project. Is there anything about your code that would restrict version (e.g. Excel 2003)?

    • sysmod says:

      I’ve just tried creating a .xltm template in Excel 2010 and when I create a workbook from it, and save, the only thing I need to do is to specify xlsm (macro enabled) format otherwise it would default to xlsx which of course is without macros.
      If I do click Yes to save without the VBA project, it does it and the xlsx can be closed and opened again normally.
      I also tried it in Excel 2003, in case that’s what you’re using, and it works fine saving as .xls and .xlt
      When you say “will not save either with or without the project”, what does it do, what error message do you get, what file name are you using and what file format are you specifying?

      • SJT says:

        Yes, I have verified that your code does work when saved as a template. My problem seems to be coming from something else I am doing in my workbook that is affecting the workbook after it is saved as a template (I’m using Excel 2007). When I open a new workbook with the template file and then try to save it, it refuses to retain the VP project and will not save even if I ask it to continue without the VB project. No error messages of any kind. So it isn’t anything you have done, it’s me. Thanks anyway for your attempts to help me.

  4. sysmod says:

    If you like, try the save again and grab the screen with the refusal message (PrtSc, paste into Word or Paint) and email it to me : mail3 at my sysmod.com site.

  5. Zach says:

    Just tested this out and it worked beautifully. I had to adjust a couple of my calls to conform better with the syntax but was pretty easy work. Thanks for this.

  6. tapas says:

    Hi Patrick,

    Could you kindly show me an simple example implementation of this for a custom collection class? For example, say I want to create mcTasks that is a collection of mcTask. The mcTask has three attributes: TaskID (as String), RoleID (as Integer), and TaskDesc (as String), with a conjugate key as TaskID+RoleID.

    I am not very good at it and am trying to learn the subject. A simple example will be very helpful for me to see the connections and usage and will provide significant clarity.

    Also, I am on a Mac using Excel 2011, if that is of any value.

    Thank you so very much.

  7. Frank Root says:

    sysmod,
    I’m attempting to use your Dictionary class. When I put it in a module the VERSION and Attribute lines at the top of the file are causing a compile error. What don’t I know about that syntax?

    • sysmod says:

      Thanks for adding ” You can ignore my previous post. I found information on how to import your code and it worked fine. Thanks for providing this valuable code.”
      Just in case anyone else has this problem, I should add here: Use the VBE to Import the code, don’t copy & paste the file in.

      Ribbon > Developer menu > Visual Basic
      or press Alt+F11

      Select your project.
      File > Import File
      Select the .bas file

      Thanks,
      Patrick

  8. Pingback: Excel Instrumentation: Activity Tracking with Visual Basic for Applications | mmatthews06

  9. Jiri says:

    This does not work on a Mac Excel with the above mentioned Dictionary and KeyValuePair classes.

    Sub test()
    Dim dict As Dictionary
    Set dict = New Dictionary
    Dim values(1 To 2) As String
    values(1) = “one”
    values(2) = “two”

    dict.Add “key”, values
    MsgBox dict(“key”)(1)
    MsgBox dict(“key”)(2)
    End SubÏ

    Under a windows excel one gets two successive messages:
    “one”
    “two”

    Jiri

  10. Ok, let’s break in into steps:
    try
    dim v as variant
    dict.Add “key”, values
    v = dict(“key”)
    debug.print typename(v),lbound(v),ubound(v)
    debug.print v(1)

    • Jiri says:

      The Test2() says:

      Run-time error 438
      Object does not support this property or method.

      v = dict(“key”) ‘ This row HIGHLIGHTED

      Sub test2()

      Dim dict As Dictionary
      Set dict = New Dictionary
      Dim values(1 To 2) As String
      values(1) = “one”
      values(2) = “two”

      Dim v As Variant
      dict.Add “key”, values
      v = dict(“key”) ‘ This row HIGHLIGHTED
      Debug.Print TypeName(v), LBound(v), UBound(v)
      Debug.Print v(1)

      End Sub

  11. Thanks, Jiri,
    I see you are using the default property dict(“key”) rather than dict.item(“key”)
    v = dict.item(“key”) works in Windows.

    In which case I recommend you make the change in the code offered by Tachy on March 26 at the original post :

    https://sysmod.wordpress.com/2011/11/02/dictionary-class-in-vba-instead-of-scripting-dictionary/

    Good luck

    P

    • Jiri says:

      Finally it works! It was a hell to make it right.
      =====
      (according to Tachy, March 26)
      1. Insert:
      Attribute Item.VB_UserMemId = 0
      after
      Public Property Get Item(Key As String) As Variant

      (The result looks like this ..)
      Public Property Get Item(Key As String) As Variant
      Attribute Item.VB_UserMemId = 0
      AssignVariable Item, KeyValuePairs.Item(Key).Value
      End Property

      (according to http://productivebytes.blogspot.cz)
      2. You’ll get a “Syntax Error” – Ignore it.
      3. Export the Class
      4. Remove the Class
      5. Reimport it again.
      ======

      What a piece of sh** is this Microsoft producing … !?!

      Thanks Patrick!
      Jiri

  12. Jason P. says:

    Helpful post Jiri, thanks. Seems to work perfectly so far.

  13. Carter P. says:

    Hello,
    How do you insert the code into the Visual Basics Editor for Excel for Mac 2016?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s