Adding Stuff to the Top of a Dictionary

I wrote a KwikOpen addin that I use about a million times a day. I ran into a little nagging problem. When I Save As’d a file from the addin, it never showed up on the recently opened list. I finally decided to track down the bug. A while back, I switched my custom class storage method from Collection to Dictionary. I don’t remember why, but I’m sure it was a fine reason. I ended up with this Add method

I have this optional argument, bToTop, so I can add it to the front of the list. But as you can see from the commented code at the bottom, that argument is basically ignored. Dictionaries don’t allow you to insert values into specific locations and that code no longer works.

So why a bug? Because I only store the most recent 2,000 files, and I’m at that limit, any Save As’d file would become 2,001 and not written to disk. When I’d go to open a file, it would read in from the file again and, of course, that recently saved file was not there.

Surely there’s a quick and easy method for pushing something to the top. Nope. All I could find was rewriting the whole Dictionary.

In that code, I create a temporary Dictionary, dcTemp, put my Save As’d file in first, then fill in the rest, finally replacing the old Dictionary with the temporary one. That’s not exactly elegant, but it gets the job done. I tested it and found that the recently saved file was not on the top of the list. It was near the top, but I inserted it first, it should be at the top. Then I remembered that I read in Excel’s MRU before I read in my file. That means there are 50 files ahead of the one I just saved. No biggie, but it gave me an idea.

Instead of recreating the Dictionary, why don’t I just add it to the MRU? There are some websites about adding entries to the registry but that won’t work. Excel reads the registry when it opens and I wasn’t about to close and reopen the app. Another way to add a file to the MRU are to specify the arguments in the Open and SaveAs methods. I am saving a file. Now my Add method looks like this

The heavy lifting is done when I save the file

That lone True out there is the AddToMru argument. By getting rid of the .Execute method and doing the SaveAs myself, I also got rid of a problem where overwriting an existing file caused two warning prompts. Now there’s no need for me to add it to my list (the commented out code at the bottom) because Excel adds it to its list and that’s what I read first.

8 thoughts on “Adding Stuff to the Top of a Dictionary

  1. A while back, I switched my custom class storage method from
    Collection to Dictionary. I don’t remember why, but I’m sure it was a
    fine reason.

    I can think of a few possible reasons:

    Dictionary keys can be almost any data type, while collection keys are only strings
    Dictionaries allow you to make string keys case sensitive, if you’re into that sort of thing
    A dictionary object gives you access to the keys as well as the items. A collection object does not give you access to the keys–you have to already “know” what they are
    Dictionaries can be faster than collections


  2. would you be able to post an updated version of the XLAM file you posted a (long) while ago?

    Thank you.

    Northville, MI

  3. Nope, that’s not right at all. I just uploaded the right one, so the link should work now. Thanks for checking that.

  4. Thanks for that. I see from the code that you run it with Ctrl + O (for Open) and Ctrl + Shift + S (for Save). If I run the Open routine, and hit Close straight away, I get a “File not found” error on UserForm_QueryClose . I get the same error if I use the Navigate button, once I dismiss the native dialog.

    This looks really handy. What would be cool is if when you double-click on a folder in the Recent Places pane you could drill down somehow to see the subfolders and files there. I guess a TreeMap would be the only way to handle that kind of drilldown.

  5. The error is in the class, but you need to use Tools – Options – Break in Class Module otherwise the VBE just breaks that the class call. No doubt, though, the error is because the mru file doesn’t exist yet.

    It doesn’t have a proper installer so there is no recently used file – you have to create it manually. It wouldn’t be that hard to create it if it doesn’t exist, but I haven’t really spent any time on making this user friendly from an installation standpoint. There is a sharepoint problem that prevents me from releasing more broadly.

    If you put a file named KwikOpenFiles.txt in your my docs folder, that should take care of the error.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.