Pin Me Up, Pin Me Down

(in which we return to VBA for a Small But Useful macro-writing session)

My friend Jens comes up with interesting questions. Even better, he doesn’t come up with them too often.

Today’s poser refers to Excel 2007, a technological marvel that has only reached our work PCs relatively recently. Jens had discovered the Usefulness that is the “pin” tool on the “Recent Documents” list:

So the pinned documents don’t go away, but they do move around on the list, rising up when used, drifting down as lesser workbooks are opened. What Jens wanted was to have his pinned files stay at the top. Or the bottom. Either, really, providing they hung around together.

We couldn’t find anything in Options, Advanced or otherwise, and some Googling didn’t turn up much of use1 and at time of writing (admittedly not too long ago) I hadn’t got any useful feedback on Stackoverflow. Time to apply a different set of talents.

Excel has Application.RecentFiles, which does pretty much what it suggests. Even better, the “.Add” method, when called with a file that’s already on the list, promotes that file to the top. If we could call that method for the pinned files, we’d be laughing. If we could identify them. It turns out we can.

Excel has to store the list somewhere so it can find it when restarting. Where do “modern” Windows programs store that kind of information? The registry2. Specifically, “HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\File MRU”, where MRU stands, most likely, for Most Recently Used. My registry has 50 entries here, probably reflecting that Advanced Excel Options allows me to display up to 50 documents, screen real estate permitting.

So looking at the entries, we see that there’s one string for each of our MRU entries and that each string has some structure to it:

That first part: “[F00000001]”, is particularly interesting: the last digit is a “1” for all the files we’ve marked as pinned. So we can identify pinned files. All we need now is to be able to read the registry from VBA. We can’t use “GetSetting” because it’s limited in scope. More Googling and we discover that the Windows Scripting Host knows about registries so we add a reference to “Windows Script Host Object model” in order to be able to early-bind to WshShell.

So here’s the first attempt. Probably heaps of unhandled situations, but right now it works on my machine…

Public Sub PromotePinnedFiles()

Const KEY_ROOT = "HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\File MRU\Item "

Dim itemIndex As Long
Dim itemData As String
Dim itemPath As String

On Error Resume Next ' general-purpose error-ignoring

With New WshShell ' Or use CreateObject("WScript.Shell") if reference not set
 For itemIndex = 1 To 50 ' I think 50 is the most Excel will store
  itemData = .RegRead(KEY_ROOT & itemIndex) ' get the item (ignoring errors if fewer than 50 present)
  If Len(itemData) > 0 Then
   If InStr(itemData, "*") > 0 Then
    If Mid(itemData, 10, 1) = "1" Then
     itemPath = Mid(itemData, InStr(itemData, "*") + 1)
     Application.RecentFiles.Add itemPath ' "promotes" the item to #1 in the list
    End If
   End If
  End If
End With

End Sub

The effect of which is this:

It just remains to decide how best to package it for my learned colleague.

1 Inevitably, there will be any number of comments telling me precisely where I should have looked.
2 Readers who answered “INI files” are invited to remove themselves to the back of the classroom, where the machines are still running Excel 3.0.

Tie an Office 2007 Ribbon…

Credit’s still a-crunching in bank-land, although for once it’s just possible that I find myself in a relatively secure part. But I’ve felt secure in the face of crisis before: wrongly as it transpired, so we’ll see.

I can’t say I was ever much of a fan of the rather clunky way in which we used VBA to add and extend menus, toolbars and (in particular) categorise user functions (who’d have thought we’d still be calling Excel 4 macros in the 21st century?).

But xlUnit needs to expose its (minimal) UI into Excel 2007 in an appropriate fashion, so I need to bite the bullet and figure out how to tie me a ribbon. in Office Fluent UI. Gulp.

It’s times like these that turn a grumpy old man’s thoughts to How-tos. I need some step-by-step FLuent UI For Dummies education. Google time. Oho – MSDN have Office 2007 Visual How Tos. How convenient. Time to save a draft and peruse…

Let’s start with Adding Custom Buttons to the 2007 Office Fluent User Interface. Ooh, a video. Doesn’t work. And the XML is nonsense. And to be honest, for the first time in well over a decade I don’t actually have a Microsoft development environment loaded on my laptop – and I don’t seem to be missing it. There must be an easier way. Back to Google.

Now this is more like it. Run a little utility to insert the XML – and the example code is valid this time – and hook up to a VBA routine. Not only that, but it explains how the process works so that you can work without the utility completely.

WIth the buttons and bows taken care of, I’d better take a step back and figure out what’s got to go on them…

Programming for (programmer) convenience

Here’s another piece of idiocy from Lotus Notes. I should say that I found this in version 6.5 and in the version that has now been inflicted on me, 8.0.1, it has been fixed. But I won’t let that stop me, it’s still a marvellous example of an entire development team failing to make that one last simple connection:

Could you just for once actually do it, could you?

Could you just for once actually do it, could you?

Have you spotted the idiocy? Notes has recognised that I have the post (sort of) open in the Preview Pane (much as in Outlook) and it doesn’t feel that it can delete it, perhaps because it might leave a decision about what to do with the empty space. So it closes the preview and invites us to try the delete again. Duh.

Just in case anyone’s struggling, I’ll spell it out: the program tells us it will do something we may not have known it could do (automatically close the Preview Pane) and then tells us to manually repeat an action that we know it definitely can do: delete a post. It says “document”, which might offer a clue, but I’m a user here – these are just emails as far as I’m concerned.

In the time it took to code the “problem” identification and the explanatory dialog, the developer could have just deleted the damn post.

Software like this does wonders for my self-esteem, I tell you.

Mmmmm, Shiny!

Browsers++, eh? Google have launched their browser, in beta form at least. Of course, “beta” for Google doesn’t always mean what it means for others – is gmail still in beta, by the way?

Anyway, ever ready to while away half an hour of work time looking at something new, off I went to the download page. A smallish (475KB) bootstrapper pulled down the actual installer, managing to find the necessary information about our somewhat complex Monte-Carlo proxy-server load-balancing  script without grief (presumably by digging into the IE or Firefox connection settings) and ran. Pretty pain-free, apart from this:

Getting warm, getting warmer, oops!

Getting warm, getting warmer, oops!

Ah well, it is a beta, after all. And it appears that the crash may have occurred at the run-after-install bit, since by the time it happened I had a desktop icon that seems – touch wood – to work.

A little detail that I really appreciated was that the install option page included a setting to make Chrome my default browser but it was unchecked by default. Nice one.

And then it just mostly worked. Some minor issues with font sizes, which seemed to randomly apply changes across tabs when I zoomed in or out using Control +?- or Control-mousewheel, but otherwise my regular stuff all seemed to render pretty well, internal or external.

It appears that the Chrome rendering engine shares the same standards book as Firefox’s – both render our IE-specific corporate intranet home page with the same set of “errors”. I tried looking to see what in the CSS was causing the problem but my limited skills weren’t up to the task. But while I was searching the source, which on a right-click/”View Source” request opens in a new browser window, which is nice, I discovered something nice. Nothing earth-shattering, but nice. I hit Control-F, which did what I expected, typed a few characters and the page was scrolled to the first found instance. As expected. Then I noticed something.

Score points for attention to detail

Score points for attention to detail

See what they did? No? Look at the vertical scroll bar. That’s a really nice touch. I like the way the “what to find” box organically grows from the surround too, and the animation is smooth, too. I suppose they could have made it slightly bouncy, in the way that Flash apps seem to like to work these days, although that can make one a little nauseous when over-done.

Oh, and another little plus on the view-source-in-the-browser thing is that links to, for example, stylesheets, are navigable. That removes a tiny piece of Firefox excise that I didn’t previously even know existed.

I’m sure there are all sorts of other little things. The address-bar within each tab may prove to be a boon, and the process-per-tab thing could be useful, although I can’t say crashing ranks very highly on my list of browser annoyances. We’ll have to keep sucking it to see.

I won’t be deleting Chrome. Neither will it be elevated to the status of default browser in the short term – I’m far too fond of my little set of FF add-ins. When Chrome has features that give me the capability provided by, at least, AdBlock, Firebug and Greasemonkey then we may be in business. But I think it’s going to be something of an uphill struggle until something really compelling and unique is offered. The thing is, Windows users who cared have already switched from IE to (mostly) Firefox and I don’t see a reason, other than possibly the bleeding-else coolness, to change again.

At least, I don’t see the compelling reason to switch yet.