Weeding the KwikOpen Garden

A little less than a year ago, I said

Why 1,000 files? I don’t know. We’ll see how the performance holds up. I’ve been using it for three days and my text file is only up to 58 files – the 50 Excel stores plus eight additional. I guess it will take a bit longer to get to 1,000 than I thought, but I think it will be clear when there are too many and I can pare it down.

I hit 1,000 files a few days ago. Performance? Not even an issue. I upped it to 2000 and have been humming along nicely. The only downside is when I’m not on my machine and have to navigate the File Open dialog like an animal.

I know there are some files in my MRU that no longer exist. I didn’t try to delete them, I just let them stay in the list until I tried to open one and it said it didn’t exist. At the point, the code would allow me to navigate to its new location. I wanted to see how many files were no longer there.

This told me that it couldn’t find 234 files. That’s a lot. I really need a way to weed those files out of my MRU.

When I first wrote this code, I checked to see if the file existed before I added it to the listbox on the userform. If the file didn’t exist at that location, it didn’t get added to the listbox. If it didn’t get added to the listbox, it didn’t get written back out to the MRU. This culled the list nicely, but presented a problem pretty early on. A couple of days into using my new creation, I typed in a file name that I new I had recently opened. I didn’t remember that I moved that file to a different file. Of course, I go no results when I typed in the name even though I was certain I should have.

Once I realized why, I decided that having files disappear was not good for my psyche. It would be better to show the file, select it, then get a message that it didn’t exist. I removed the code that checked whether the file exists and didn’t implement anything that would remove files from the list short of clicking on them. Basically, I pushed that problem into the future. Well, the future is now. With 20% of my MRUs missing, I suppose it’s time to take a smarter tack.

I’m faced with a design decision. I need missing files to hang around for at least some amount of time, but not forever. Here are some choices I’ve been considering:

  1. Time stamps: I could time stamp each entry with the “last open date”. Entries less than one month old are never deleted. Missing entries older than one month get deleted automatically. The dissonance I experienced searching for a missing file that I was sure wasn’t missing occurred because I had had that file open within the last few days. I don’t think I would have the same experience with a file that I’d opened last month. Instead, I would assume I was misremembering as opposed to being crazy. I like the fact that this happens automatically – with no user intervention. I don’t like the fact that I have to store the date. My file goes from a clean, simple list to a data structure.
  2. Marking missing files: I could put an asterisk in front of files in the listbox that were missing. That way I would know what was missing and could click on them to clean them up, even if I didn’t intend to open them at that time. As I type this option, I hate it even more. Distracting myself with pointless housekeeping while I’m trying to get something done is a terrible idea.
  3. Cleanup utility: I could make a separate utility that the user could periodically run. It would list the missing files and allow the user to “find” any of them that he thinks is important and remove the rest. I wouldn’t have to touch any existing code or data for this, which is a positive. It’s not automatic like the #1, which is a negative.

I’ll probably go with #1, but I haven’t decided yet.

18 thoughts on “Weeding the KwikOpen Garden

  1. Hi Dick, I’m not sure of the performance implication, but how about, if it doesn’t exist, checking in your recent directories and changing the path based on this result.

    This may cause problems if you have multiple files of the same name though!

  2. I actually tried to install your original code last week but was not successful in getting it working – I suspect it had to do with the Toolc->References options within VBA. Would you consider posting/emailing comprehensive instrucions as to what I need to do to get this working on my Excel 2010 installation?



  3. Thanks for the ADDIN version of the tool that I can use at work. At home I have Excel 2013 64 bit and it gives me an error stating that it must be updated for 64 bit systems.

    I get a new company laptop on Tuesday and am not sure if it will be Excel 2010 or Excel 2013.

  4. Hi Dick,

    I also have an issue with 64 bit support. I tried changing the fuctions to PtrSafe, but then I get a complaint about type mismatch here:
    Public Property Set Parent(obj As CRcntFiles): mlParentPtr = ObjPtr(obj): End Property

    Any idea what else needs to be done to get this to work in 64 bit Excel?

  5. @Gene To: That uses a pointer in memory to avoid problems with circular references between classes. I put it in every class I build, but only uses it sometimes. Guess what, I don’t use it in this project so it’s causing problems for nothing. Here’s how to fix it:

    In CRcntFiles Add method, remove

    In CRcntFile declaration section remove

    Elsewhere in CRcntFile, remove

    You can see I just commented those out rather than remove them. That should get rid of the error without any loss of function.

  6. I’m also having problems with 64-bit Excel 2016. Whenever I click either the Open or Close file button in the Recent Files user form, I get an Excel crash with the following signature:

    Problem signature:
    Problem Event Name: APPCRASH
    Application Name: EXCEL.EXE
    Application Version: 16.0.7167.2040
    Application Timestamp: 57ad3696
    Fault Module Name: ntdll.dll
    Fault Module Version: 6.3.9600.18233
    Fault Module Timestamp: 56bb4ebb
    Exception Code: c0000028
    Exception Offset: 00000000000ecdd0
    OS Version: 6.3.9600.
    Locale ID: 2057

  7. I switched to 64-bit Excel 2010 this week and this crashes every time.

    Fun stuff. I’ll keep you posted.

  8. And sometimes it’s different

  9. Here’s the thing. If I delete the text file that contains all of recent files, it works – except that it throws a normal, non-crashing error at the end because there’s no file to write to.

    If I put the text file back and delete all of the entries except one (that I know exists), it crashes.

    If I delete everything in the text file, it works. And it writes stuff out to the text file. Then when I run it again, it crashes.

  10. I started rewriting this from scratch and I ran into the same crashing problem. I’m fairly convinced it has to do with NewEnum, but I can’t quite nail it down. I changed the RcntFileByFullName property of the RcntFiles class to this:

    Instead of looping through via NewEnum, it uses a simple Long variable. And it works so far. Here’s the wacky part: I use NewEnum everywhere, not just here. Why it fails on this one and not on any of the others, I don’t know.

  11. I have had strange Excel crashes since switching to 64-Bit. My solution (which has worked so far) has been to put sleep(1000) statements in the offending function(s). It seems that slowing it down (just for a second – or sometimes less) is enough for VBA to keep going without crashing.

    You can test this theory by stepping through your code (F8) and seeing if you still get the crash. If not – try a sleep statement.

  12. Interesting. I was not able to reproduce the crash when stepping through my code, so that might be it. Thanks.

  13. When I add Sleep inside my loop, it works, but obviously waiting 1 sec inside a loop isn’t going to work. When I add Sleep just before my loop – after I close the text file but before I start the loop – it just spins and spins. No crash, but I have to kill the process.

    In another spreadsheet, I replaced a For Each with a For i and it solved my problem there. If I have to ditch the NewEnum, I might as well use dictionaries instead of collections. Not all bad.

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

Leave a Reply

Your email address will not be published.