Weeding the KwikOpen Garden

Posted on February 11, 2015 by Dick Kusleika

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.

Public Sub FindMissing()
        
    Dim clsRcntFiles As CRcntFiles
    Dim clsRcntFile As CRcntFile
    Dim lCnt As Long
    
    Set clsRcntFiles = New CRcntFiles
    clsRcntFiles.Fill
    
    For Each clsRcntFile In clsRcntFiles
        If Len(Dir(clsRcntFile.FullName)) = 0 Then
            Debug.Print clsRcntFile.FullName
            lCnt = lCnt + 1
        End If
    Next clsRcntFile
    
    Debug.Print lCnt
    
End Sub

Public Sub FindMissing()

Dim clsRcntFiles As CRcntFiles

Dim clsRcntFile As CRcntFile

Dim lCnt As Long

Set clsRcntFiles = New CRcntFiles

clsRcntFiles.Fill

For Each clsRcntFile In clsRcntFiles

If Len(Dir(clsRcntFile.FullName)) = 0 Then

Debug.Print clsRcntFile.FullName

lCnt = lCnt + 1

End If

Next clsRcntFile

Debug.Print lCnt

End Sub

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:

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.
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.
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”

John Stark says:

February 11, 2015 at 9:38 am

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!
JoeC says:

February 11, 2015 at 2:57 pm

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?

Thanks.

Joe
Dick Kusleika says:

February 12, 2015 at 10:22 am

I’ve packaged it into an add-in. This should make it easier.

http://dailydoseofexcel.com/excel/KwikOpen.zip

Just unzip and install like you would any add-in.
Dick Kusleika says:

February 12, 2015 at 10:23 am

Ooh, that’s a good idea @John Stark. Definitely worth some testing.
JoeC says:

February 21, 2015 at 12:18 pm

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.
Dick Kusleika says:

February 23, 2015 at 1:12 pm

I guess there is an API call in there that I forgot about. You can replace the SetCurrentDirectory API with

Private Declare PtrSafe Function SetCurrentDirectoryA Lib “kernel32” (ByVal lpPathName As String) As Long

from http://www.jkp-ads.com/Articles/apideclarations.asp?AllComments=True
joec says:

February 24, 2015 at 9:25 am

Thanks! I’ll give it a whirl.
Gene To says:

February 27, 2015 at 6:57 pm

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?

@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

Set clsRcntFile.Parent = Me

1	Set clsRcntFile.Parent = Me

In CRcntFile declaration section remove

'Private mlParentPtr As Long
'Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
    (dest As Any, Source As Any, ByVal bytes As Long)

'Private mlParentPtr As Long

'Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _

(dest As Any, Source As Any, ByVal bytes As Long)

Elsewhere in CRcntFile, remove

'Public Property Get Parent() As CRcntFiles: Set Parent = ObjFromPtr(mlParentPtr): End Property
'Public Property Set Parent(obj As CRcntFiles): mlParentPtr = ObjPtr(obj): End Property

'Private Function ObjFromPtr(ByVal pObj As Long) As Object
'    Dim obj As Object
'    CopyMemory obj, pObj, 4
'    Set ObjFromPtr = obj
'    ' manually destroy the temporary object variable
'    ' (if you omit this step you'll get a GPF!)
'    CopyMemory obj, 0&, 4
'End Function

'Public Property Get Parent() As CRcntFiles: Set Parent = ObjFromPtr(mlParentPtr): End Property

'Public Property Set Parent(obj As CRcntFiles): mlParentPtr = ObjPtr(obj): End Property

'Private Function ObjFromPtr(ByVal pObj As Long) As Object

' Dim obj As Object

' CopyMemory obj, pObj, 4

' Set ObjFromPtr = obj

' ' manually destroy the temporary object variable

' ' (if you omit this step you'll get a GPF!)

' CopyMemory obj, 0&, 4

'End Function

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

Steve James says:

September 2, 2016 at 3:55 am

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.2.0.0.256.4
Locale ID: 2057

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

<pre>Faulting application name: EXCEL.EXE, version: 14.0.7173.5000, time stamp: 0x57b4acbf
Faulting module name: OLEAUT32.dll, version: 10.0.10240.16384, time stamp: 0x559f389d
Exception code: 0xc0000005
Fault offset: 0x00000000000172d3
Faulting process id: 0xa45c
Faulting application start time: 0x01d20f8061ca4c54
Faulting application path: C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
Faulting module path: C:\WINDOWS\system32\OLEAUT32.dll
Report Id: a8b6e8bf-7b74-11e6-9bcc-b88a6067d4bd
Faulting package full name: 
Faulting package-relative application ID:

<pre>Faulting application name: EXCEL.EXE, version: 14.0.7173.5000, time stamp: 0x57b4acbf

Faulting module name: OLEAUT32.dll, version: 10.0.10240.16384, time stamp: 0x559f389d

Exception code: 0xc0000005

Fault offset: 0x00000000000172d3

Faulting process id: 0xa45c

Faulting application start time: 0x01d20f8061ca4c54

Faulting application path: C:\Program Files\Microsoft Office\Office14\EXCEL.EXE

Faulting module path: C:\WINDOWS\system32\OLEAUT32.dll

Report Id: a8b6e8bf-7b74-11e6-9bcc-b88a6067d4bd

Faulting package full name:

Faulting package-relative application ID:

Fun stuff. I’ll keep you posted.

And sometimes it’s different

<pre>Faulting application name: EXCEL.EXE, version: 14.0.7173.5000, time stamp: 0x57b4acbf
Faulting module name: VBE7.DLL, version: 7.0.16.39, time stamp: 0x56e20862
Exception code: 0xc0000005
Fault offset: 0x00000000000e60bc
Faulting process id: 0xa298
Faulting application start time: 0x01d20f8ab631f611
Faulting application path: C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
Faulting module path: C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
Report Id: f6e08a0b-7b7d-11e6-9bcc-b88a6067d4bd
Faulting package full name: 
Faulting package-relative application ID:

<pre>Faulting application name: EXCEL.EXE, version: 14.0.7173.5000, time stamp: 0x57b4acbf

Faulting module name: VBE7.DLL, version: 7.0.16.39, time stamp: 0x56e20862

Exception code: 0xc0000005

Fault offset: 0x00000000000e60bc

Faulting process id: 0xa298

Faulting application start time: 0x01d20f8ab631f611

Faulting application path: C:\Program Files\Microsoft Office\Office14\EXCEL.EXE

Faulting module path: C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL

Report Id: f6e08a0b-7b7d-11e6-9bcc-b88a6067d4bd

Faulting package full name:

Faulting package-relative application ID:

Dick Kusleika says:

September 15, 2016 at 3:31 pm

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.
Steve James says:

September 19, 2016 at 10:06 am

Sounds like maybe an issue with opening / reading the text file…

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:

<pre>Public Property Get RcntFileByFullName(ByVal sFullName As String) As CRcntFile

    Dim clsReturn As CRcntFile
    Dim clsRcntFile As CRcntFile
    Dim i As Long

    For i = 1 To Me.Count
        Set clsRcntFile = New CRcntFile
        If clsRcntFile.FullName = sFullName Then
            Set clsReturn = clsRcntFile
            Exit For
        End If
    Next i

    Set RcntFileByFullName = clsReturn

End Property

<pre>Public Property Get RcntFileByFullName(ByVal sFullName As String) As CRcntFile

Dim clsReturn As CRcntFile

Dim clsRcntFile As CRcntFile

Dim i As Long

For i = 1 To Me.Count

Set clsRcntFile = New CRcntFile

If clsRcntFile.FullName = sFullName Then

Set clsReturn = clsRcntFile

Exit For

End If

Next i

Set RcntFileByFullName = clsReturn

End Property

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.

James Brown says:

September 22, 2016 at 3:20 am

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.
Dick Kusleika says:

September 22, 2016 at 8:01 am

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

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.

<pre>Public Property Get UnwrittenByGLAccount(ByVal sGLAccount As String) As CUnwritten

    Dim clsReturn As CUnwritten
    Dim clsUnwritten As CUnwritten
    Dim i As Long

    For i = 1 To Me.Count
        Set clsUnwritten = Me.Unwritten(i)
    'For Each clsUnwritten In Me
        If clsUnwritten.GLAccount = sGLAccount Then
            Set clsReturn = clsUnwritten
            Exit For
        End If
    'Next clsUnwritten
    Next i

    Set UnwrittenByGLAccount = clsReturn

End Property

<pre>Public Property Get UnwrittenByGLAccount(ByVal sGLAccount As String) As CUnwritten

Dim clsReturn As CUnwritten

Dim clsUnwritten As CUnwritten

Dim i As Long

For i = 1 To Me.Count

Set clsUnwritten = Me.Unwritten(i)

'For Each clsUnwritten In Me

If clsUnwritten.GLAccount = sGLAccount Then

Set clsReturn = clsUnwritten

Exit For

End If

'Next clsUnwritten

Next i

Set UnwrittenByGLAccount = clsReturn

End Property

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

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Weeding the KwikOpen Garden

18 thoughts on “Weeding the KwikOpen Garden”

Leave a Reply