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.

KwikOpen Update

A year and a half ago, I decided that I was going to make a change to my KwikOpen add-in to get rid of recent files that no longer exist. Well, I finally got it done. No, it didn’t take that long to implement. The performance of the add-in has been fine so there wasn’t a pressing need. The other day, the addin seemed a little less peppy than usual and I thought it was time for a look.

I had 2,368 files in my MRU and 465 of them are dead links. That’s about 20% and it’s similar to the proportion I saw back in February 2015. Of the three options I listed at the bottom of my previous post, I chose none of them. Instead, I weeded out some files as I wrote them back out to disk.

The file names are written to the file with the most recent at the top – sort of. Because I’m using the built-in MRU as well as my own, it’s not exactly that way, but it’s close enough for government work. Instead of time stamping the entries, I decided to dump any nonexistent files that were near the bottom of the list. If a file is in the top 90% of the list, it stays regardless of whether it exists. If it’s in the bottom 10%, it only stays if it’s still where it was.

Iteration Total Files Orphaned Files
Beg. 2,368 465
1 2,250 345
2 2,226 321
3 2,225 320

Looking at the last 100 or so files, they’re mostly from 2014. I could cap this at 2,000 and probably not notice.

Unique Entries in Userform Dependent Listboxes

Deepthi commented

…could you please help me tweak the code so that I can make multiple selections in listbox 1 in such a way that the values selected in list box two has all the values applicable for the selections made (listbox) but removes all duplicates?

First, a word about that post. I have used the relationship listbox template exactly zero times. I simply prefer to build my classes from scratch with names that reflect the business objects they represent. But I did reuse the userform and I didn’t change the control names from Parent/Child to Class/Student. I’m conflicted about that, but I’ll get over it.

Let’s say we have some classes and students. A class can have many students and a student can have many classes.

When you select a class, the userform lists the students. If you select more than one class, the userform lists all the student from the selected classes, but each student is listed only once.

Andrew and Payton are only listed once.

There are some significant changes to the code, not the least of which is removing the grandchildren. Also instead of tracking ActiveParent (singular), I now track ActiveClasses (plural) because my top listbox is now multiselect. When my Parent listbox changes, I have to see all the classes that are selected.

To get a unique student list, I use a dictionary object. My favorite thing about dictionaries is returning a zero-based array from the Keys or Items properties.

You can check out the rest of the code in the downloadable file.

You can download

Searching Text Files in a Directory

I have several years of vendor invoices, in text file format, in some directories on a share. I need to search through these text files to find an order number, manifest number, or some other piece of information. I can’t search everything because it would take too long. And I don’t have control over the server, so if there is some indexing that could be done, I can’t do it. I’m stuck with good old VBA.

The folders are yyyymmdd (ex: 20150725 for July 25th) and corresponds to the invoice dates for any invoices in the file. Each file starts with a three letter abbreviation of the vendors name. Invoice date and vendor name are the only two pieces of information I can use to limit the search. The final piece of information is, of course, the search term. Here’s what the form looks like

I have a table of vendors and codes to populate the Vendor combobox. The QuickDate combobox populates the Date Range textboxes and contains common date ranges, namely, Last Month, This Month, Last Quarter, This Quarter, Last Year, This Year. I can change the dates to whatever I want if there isn’t a Quick Date that suits me. The Search Terms textbox takes a space separated list of terms to search for.

And now the fun part. The code. This converts the Quick Dates into real dates

This makes sure a real date is entered, but provides for 6 or 8 digit date entry.

And the big one, the actual search. This is pretty long and needs to be refactored, but it works for now.

It takes about 60 seconds per month to search the files. That’s a long time so it’s necessary to entertain the user while he waits. The top entry in the results listbox is whatever the current file is. It rapidly changes the display as it loops through the folder. When there’s a hit, that file becomes the second entry and any prior hits move down. This little animation lets the user know that it’s still working and gives him a list of what hits have been found already.

You can download

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.

KwikOpen Addin

I’ve been using this recent files userform for quite a while now and I like it. I haven’t added Eric’s comment yet (and for no good reason), but I’m going to in the next version. I don’t know if that solves the Sharepoint problem or just the ChDrive problem as neither are problems for me.

Here are my two problems:
Not enough recent files. I’m shocked – SHOCKED – at how often 50 recent files is not enough. It’s usually when I have to open a whole bunch of very similar files that I will never open again, but that clean out my recent files list. I decided to, sort of, maintain my own list. Because I use class modules *ahem* changing things to maintain my own list was pretty easy. I had to change how I fill the CRcntFiles class, but everything that consumes that class downstream just works. Here’s the new Fill method in CRnctFiles.

Public Sub Fill()

Dim rf As RecentFile
Dim clsRcntFile As CRcntFile
Dim sFile As String, lFile As Long
Dim vaFiles As Variant
Dim i As Long

For Each rf In Application.RecentFiles
Set clsRcntFile = New CRcntFile
clsRcntFile.FullName = rf.Path
Me.Add clsRcntFile
Next rf

sFile = ThisWorkbook.Path & Application.PathSeparator & msMRUFILE
lFile = FreeFile
Open sFile For Input As lFile

vaFiles = Split(Input$(LOF(lFile), lFile), vbNewLine)

Close lFile

For i = LBound(vaFiles) To UBound(vaFiles)
If Len(vaFiles(i)) > 0 Then
Set clsRcntFile = Nothing
Set clsRcntFile = Me.RcntFileByFullName(vaFiles(i))

If clsRcntFile Is Nothing Then
Set clsRcntFile = New CRcntFile
clsRcntFile.FullName = vaFiles(i)
Me.Add clsRcntFile
End If
End If
Next i

End Sub

First, I read in the 50 Excel most recently used files. Then I read in the 1,000 most recently used files that I store in a text file, weeding out the duplicates as I go. The advantage of continuing to use the Excel MRU is that I can leverage its pinning feature. I don’t have to write my own pinning bullshit – if you want to pin something, do it via Excel and it will always be in the MRU. Awesome.

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 their are too many and I can pare it down.

Next I need a way to write the files back to the text file. When the userform closes, the CRcntFiles.WriteToDisk method is called.

Public Sub WriteToDisk()

Dim sFile As String
Dim lFile As Long
Dim clsRcntFile As CRcntFile
Dim aFiles(1 To 1000) As String
Dim lCnt As Long

lCnt = 0
For Each clsRcntFile In Me
lCnt = lCnt + 1
If lCnt > UBound(aFiles) Then Exit For
aFiles(lCnt) = clsRcntFile.FullName
Next clsRcntFile

sFile = ThisWorkbook.Path & Application.PathSeparator & msMRUFILE
lFile = FreeFile

Open sFile For Output As lFile
Print #lFile, Join(aFiles, vbNewLine)
Close lFile

End Sub

I lazily write 1,000 lines to the disk even if I don’t have that many. I mean efficiently, not lazily. The text file is 6KB, so I’m not losing sleep over it. I would be pretty trivial to Redim Preserve that after I’ve filled it up, so I supposed I’ll do that after the alpha test.

And other than a few minor tweaks, that’s the only changes I had to make. If that’s not a case for using class modules, I don’t know what is. My userform consumes a CRcntFiles class. It doesn’t care how that class gets filled up or where the list comes from. I could change to storing those recent files in the registry, in an XML file, or tattooed to my back. As long as I can get them into a CRcntFiles instance, the rest of the code is happy.

Save As is jealous of Open. My next problem is that while I can quickly open a recent file, I can’t quickly save a file to a recent place. This is primarily a problem when I open attachments in Outlook. It stores an opened attachment in the Temp folder and when I choose Save As, that’s the folder it starts me in. Nuts to that. If you download this add-in, you’ll also see that I’ve hooked up a SaveAs userform to Ctrl+Shift+S. It’s got a few problems too (it prompts to replace a file twice), but you can try it if you like.

You can download

Recent Files and Places Search

I love what Excel has done to the list of recently opened files. It went from up to nine files to up to 50 files. You can pin certain files so they never leave the list. Every item on the list has a keyboard shortcut. But is it every good enough? No. No it’s not.

I have my options set to show 50 recent files, buy they don’t all fit on the screen. And 50 is a bit much. In fact, 25 (the default setting) can be hard to look through. All of the recent files have keyboard shortcuts and all of the push pins for the files have keyboard shortcuts. By the time you get to about the 20th file, the keyboard shortcut is YY2. It gets a little unwieldy, I think.

What if we could filter the list by typing in a search box? What if indeed.

And I filter on 201303 (that’s the third quarter of 2013 for you non-accountants).

Now that’s a list I can get my head around. Let’s see how it works. First I fill the listboxes based on the Application.RecentFiles collection.

Public Sub FillFilesAndPlaces(Optional sFilter As String = vbNullString)

Dim dcPlaces As Scripting.Dictionary
Dim rf As RecentFile
Dim sPlace As String
Dim sFile As String
Dim aFiles() As String
Dim lCnt As Long

Set dcPlaces = New Scripting.Dictionary

ReDim aFiles(1 To 2, 1 To 1)

For Each rf In Application.RecentFiles
sFile = Dir(rf.Name)
sPlace = Replace(rf.Path, Dir(rf.Path), vbNullString)

'If file isn't found, don't include it
If Len(sFile) > 0 Then
'Put file names and paths in an array
If UCase(sFile) Like "*" & UCase(sFilter) & "*" Then
lCnt = lCnt + 1
ReDim Preserve aFiles(1 To 2, 1 To lCnt)
aFiles(1, lCnt) = rf.Path
aFiles(2, lCnt) = sFile
End If

'Put unique paths in a dictionary
If UCase(sPlace) Like "*" & UCase(sFilter) & "*" Then
If Not dcPlaces.Exists(sPlace) Then
dcPlaces.Add sPlace, sPlace
End If
End If

End If
Next rf

'If files match, put to listbox, otherwise clear
If lCnt = 1 Then
Me.lbxFiles.AddItem aFiles(1, 1)
Me.lbxFiles.List(Me.lbxFiles.ListCount - 1, 1) = aFiles(2, 1)
ElseIf lCnt > 1 Then
Me.lbxFiles.List = Application.WorksheetFunction.Transpose(aFiles)
End If

'Write keys array to places listbox
Me.lbxPlaces.List = dcPlaces.Keys

End Sub

When the userform is initialized, this procedure is called without the argument. That ensures that every file and place gets picked. I loop through the RecentFiles collection and capture the file name (using Dir) and the folder. The file names and fullnames go into a 2d array. The paths go into a dictionary so I can get a unique list. I never used to use dictionaries because I didn’t like the reference dependency. But that’s just stupid because every computer I use will have Scripting Runtime installed. So now I’ve embraced them. If I could use For Each with a dictionary in a custom class module, I’d quit using collections altogether.

Recent files can have the same name, which causes a problem. I included a textbox at the bottom of the form so I could get the whole path. I didn’t want the whole path in the listbox because it looks too cluttered. When something is selected in the files listbox, the textbox gets updated with the full path and filename.

Private Sub lbxFiles_Change()

If Me.lbxFiles.ListIndex > -1 Then
Me.tbxFullname.Text = Me.lbxFiles.Value
Me.lbxPlaces.ListIndex = -1
Me.tbxFullname.Text = vbNullString
End If


End Sub

I’m using the Enter and Change events of the listbox to make sure that only a file or a place is selected and not both. I originally had a button for each listbox, but that seemed silly. Now I’m wondering if I should have files and places all in the same listbox.

The Open button’s Enabled property is updated based on what’s selected.

Public Sub EnableOpen()

Dim wb As Workbook

Me.cmdOpen.Enabled = False

If Me.lbxPlaces.ListIndex > -1 Then
Me.cmdOpen.Enabled = True
ElseIf Me.lbxFiles.ListIndex > -1 Then
On Error Resume Next
Set wb = Workbooks(Me.lbxFiles.Column(1))
On Error GoTo 0

If wb Is Nothing Then
Me.cmdOpen.Enabled = True
If wb.FullName = Me.lbxFiles.Value Then
Me.cmdOpen.Enabled = True
Me.tbxFullname.Text = Me.tbxFullname.Text & Space(1) & "(naming conflict)"
End If
End If
End If

End Sub

I start with disabling the button. Then if a place is selected, it becomes enabled. Places is a unique list, so there’s no conflicts that can happen or any other error checking that’s needed. If a file is selected, it gets a bit more complicated. You can’t have two files with the same name open at the same time, even if they are in different folders. I try to set a variable to an open workbook, wrapped in On Error. If wb is Nothing, that means there are no open workbooks with that same name, so I enable the Open button. If wb is something, I check to see if the Fullname is the same. If it is, I enable Open, but in reality Open will just activate the workbook. Finally, if there’s an open workbook with the same Name, but a different Fullname, I keep Open disabled and put a message in the textbox. It’s not a great system, but it’s what I’ve got so far.

The only other code of interest is actually opening the file or place.

Private Sub cmdOpen_Click()

Dim wb As Workbook

If Me.lbxFiles.ListIndex > -1 Then
On Error Resume Next
Set wb = Workbooks(Me.lbxFiles.Column(1))
On Error GoTo 0

If wb Is Nothing Then
Workbooks.Open Me.lbxFiles.Value
End If
ChDrive Left$(Me.lbxPlaces.Value, 3)
ChDir Me.lbxPlaces.Value
End If

Unload Me

End Sub

If it’s a file, I do the same workbook variable trick to see if it’s already open. If it is, I activate it. I don’t have to worry about it being a different file with the same name, because if that was a problem, Open would be disabled. If it’s not open, open it. If it is open, activate it.

If it’s a place, I change the drive, change the directory, and show the File Open dialog. I just know that ChDrive is going to screw up at some point, like if I have UNC path in there. But this is just a first draft, so I’ll deal with those problems later.

Right now, I press Alt+F+R and look down the recent file list. If I don’t see the file, I ESC out and press Ctrl+O to navigate the old fashioned way. I’m thinking that this userform should replace all of that. I would press Ctrl+O and this userform would show up. I would start typing and the list gets filtered. If the list is filtered, I would click Open and it lets me navigate to the file – kind of like selecting a Recent Place that happens to be the current directory. That means I have to keep Open enabled all of the time and prevent workbook name conflicts some other way. Alternatively, I could just add another button to the form to navigate. So Ctrl+O to open the form then Alt+N to skip the recent part and just navigate to the file.

I’ve been using this for a week and, with a few minor exceptions, it’s worked wonderfully. I added the Navigate button to go directly to File Open. And I assigned Ctrl+O to open this form. It’s a little disconcerting at first – expecting the file open dialog and getting this userform – but I quickly got used to it. I’ve only had to ‘navigate’ once and that’s because I wasn’t thinking. The folder I wanted was in the recent places list, but I tend to be thinking about file names when searching, not folders.

You can download

Amazon Linkerator

Since I can’t install JPSoftwareTech’s Amazon link generator, I decided to write my own. Fewer features, less choice because that’s how I roll. I recommend JP’s version – it’s very well done.

My tastes and habits are very quirky, so this “utility” is very specialized to the way I like to work. There may be some good stuff in there even if it’s not something you’d use yourself. It looks like this:

amazon link generator

The normal workflow is to find the product on, launch the utility, tab to whichever link I want, and press Esc. I guess I’ll have to edit the Description field most of the time too.

Here’s how it works:

  • If the clipboard has a URL in it that matches this regular expression, the form is prefilled with the ASIN and description
  • The ASIN goes in the Product field, the hyperlink text goes in the Description field
  • Changes to either Product or Description updates the two link textboxes
  • Exiting either Product or Description refreshes the web browser control
  • Entering either link textbox puts that link in the clipboard
  • Esc closes the form

One shortcoming you may have noticed is that my Amazon Associates ID isn’t on the form. It’s in a constant in the code. It’s not intended to be a general purpose utility, but if you have the chops you can modify it for yourself. The other omission is that there are only two links. I only ever need a text link or a large image with a text link underneath it. I’m just boring that way.

Let’s look at some code for goodness sake.

Private Sub UserForm_Initialize()

Dim lFile As Long
Dim sInstructions As String
Dim doClip As MSForms.DataObject
Dim sClip As String
Dim regEx As VBScript_RegExp_55.RegExp
Dim regMatch As VBScript_RegExp_55.MatchCollection

'Create a template web page for when the form is blank
sInstructions = "Exit Product or Description to update browser

Enter Text Link or Image Link to copy to clipboard

Esc to close form"
msFile = Environ("TEMP") & Application.PathSeparator & "AMZNLNK.html"

lFile = FreeFile
Open msFile For Output As lFile
Print #lFile, sInstructions
Close lFile

'Check the clipboard for an amazon URL and fill in the form
Set doClip = New MSForms.DataObject
If doClip.GetFormat(1) Then
sClip = doClip.GetText
Set regEx = New VBScript_RegExp_55.RegExp
regEx.Pattern = "http://www\.amazon\.com/([\w\-]+/)?(?:dp|dp/product|gp/product|exec/obidos/asin)/(?:\w+/)?(\w{10})"
Set regMatch = regEx.Execute(sClip)

If regMatch.Count > 0 Then
Me.tbxDescription.Text = Replace(Replace(regMatch.Item(0).SubMatches.Item(0), "-", Space(1)), "/", vbNullString)
Me.tbxProduct.Text = regMatch.Item(0).SubMatches.Item(1)
End If

End If

'update the web browser <- comments that don't help are great UpdateWeb End Sub

Since you devoured that RegEx post from earlier this week, I'm sure that part needs no explanation. When there's a match, here's what the regMatch variable looks like.

vba regular expression object

I only have two groups in my regex pattern that store substrings. The other groups start with ?: so nothing is saved, only matched. That guarantees that I'll have a SubMatch.Count of exactly two, even if both are Empty.

The Change events for Product and Description call the UpdateLinks procedure.

Private Sub UpdateLinks(ByVal sProduct As String, ByVal sDesc As String)

Dim aImage(1 To 4) As String
Dim aLink(1 To 12) As String
Dim lFile As Long

Const sPLACE As String = "||||"

If Len(sDesc) = 0 Then sDesc = "No description set"

aLink(1) = "" & sPLACE & ""
aLink(8) = ""

aImage(1) = ""

Me.tbxText.Text = Replace(Join(aLink, vbNullString), sPLACE, sDesc)
Me.tbxImage.Text = Replace(Join(aLink, vbNullString), sPLACE, Join(aImage, vbNullString)) & "
" & Replace(Join(aLink, vbNullString), sPLACE, sDesc)

lFile = FreeFile

Open msFile For Output As lFile
Print #lFile, Me.tbxText.Text & "

" & Me.tbxImage.Text
Close lFile

End Sub

This procedure builds up a couple of strings, sticks them in the textboxes, and sticks them in an html file. There are three things that I'm forcing myself to do: use Join with arrays instead of strings of ampersands and line continuation characters (easier to maintain, I tell myself); use Space(1) instead of " "; and use vbNullString instead of "".

The Exit events for Product and Description update the web browser control for a preview of what the links look like. The Change events actually build the HTML file in the user's temp directory and the Exit events navigate to that.

Private Sub UpdateWeb()

If Len(msFile) > 0 Then
Me.webPreview.Navigate "file:///" & Replace(msFile, "\", "/")
End If

End Sub

Not much to that. If msFile is somehow not initialized, UserForm_Initialize will put up the instruction template.

Lastly, both of the link textboxes contents are copied to the clipboard using the Enter event. Here's the Image one:

Private Sub tbxImage_Enter()

Dim doClip As MSForms.DataObject

If Len(Me.tbxImage.Text) > 0 Then
Set doClip = New MSForms.DataObject
doClip.SetText Me.tbxImage.Text
Me.tbxMessage.Text = "Image copied to clipboard"
End If

End Sub

I have a textbox at the bottom to alert the user that something has happened. I should probably add a Copy To Clipboard button, a Refresh Preview button and a settings dialog so you could personalize it without changing code. But that's for next version. If you want the rough version, you can have it.

You can buy Amazon Linkerator