It’s Almost IKSD Eve

You know that this Wednesday is International Keyboard Shortcut Day, right? To start the celebration, I’m reposting a blog from with permission of the author. Enjoy!

This week (Week 26 of 2014, for the Excel aficionados), we have had a very interesting discussion on the Word-PC e-mail listbook on Word, together with many others topics that you might benefit from.

First up, how not to learn keyboard shortcuts: Don’t search for a list and try to commit it to memory. This fails, because firstly we are overloading our brains with too much information in too short a space of time (any student can verify this three weeks after an exam). Secondly, we are dealing with the list as an abstract thing, and not relating it to our use.

My own experience has been that a very simple discipline (which I will get to shortly) has worked very well for learning keyboard shortcuts.

Before that, though, it is useful to examine how we discover keyboard shortcuts. Yes, lists are useful for that—every time I see an article on keyboard shortcuts, I make certain that I read it (I just picked up a new Excel keyboard shortcut this week in that manner). Second, we all (or at least those who type as inaccurately as I do) have had the experience of pressing something on the keyboard, and seeing something go haywire (e.g. we wanted Shift+R for an uppercase R, but hit Ctrl+R and right-aligned our paragraph). Normally we just undo (Ctrl+Z), but I normally take some time out, try to see if I can figure out what I just hit (I will sometimes undo and try to recreate the keyboard shortcut with what I thought I was typing). I will also evaluate whether this keyboard shortcut is useful for me—if not (e.g., the keyboard shortcuts in Word for Danish characters are some that, no offense to the Danes, I hope I never have to use), then I just forget about it and carry on with my life. If it does appear to be useful, then I will set about learning it. Also, it pays to be observant. It amazes me that people look at menus, and never see (i.e., observe) the keyboard shortcuts listed in those menus (of course, since Office 2007, the Office keyboard shortcuts are displayed in better-hidden tooltips—yet another way Microsoft seems intent on de-cluttering by dumbing down).

One other note before I get to the learning bit. Some keyboard shortcuts are system-wide—meaning they will either work on the OS level, or will work for almost all programs known to man (if you will excuse the exaggeration). Examples are Ctrl+C for Copy, Ctrl+P for Print, Ctrl+S for Save, etc.). Some are, for want of a better term, platform-wide (e.g., Ctrl+H for Replace in all of the Microsoft Office programs, while Ctrl+R does the same in many other programs). And some are program-specific (e.g., Ctrl+Alt+M for a comment in Word, which is Shift+F2 in Excel). If I am working in a new program, I will (after having duly saved my work), freely experiment in that program with keyboard shortcuts that I believe would work there (i.e., those keyboard shortcuts that are in the first two categories I mentioned). I’ve never had a computer meltdown as a result of this (yet!), and it gives me, so to speak, more bang for my buck for the effort of learning keyboard shortcuts.

So, how do we learn keyboard shortcuts?

Still not there yet…. Some keyboard shortcuts are mnemonic (Ctrl+B for Bold, Ctrl+I for italics, Ctrl+C for Copy—and, on that point, notice the position of the X,C,V keys on a qwerty keyboard, and remember Cut, Copy, Paste—and, in Word, Ctrl+L for Left alignment, Ctrl+R for Right alignment, and Ctrl+J for Justified alignment, etc.). Unfortunately, not all keyboard shortcuts can be mnemonic (e.g., Ctrl+C is already taken, so Centre alignment in Word becomes Ctrl+E). Obviously, mnemonic keyboard shortcuts are going to be easier to remember, but we need to go further.

So, how do we learn keyboard shortcuts?

Firstly, one or two at a time. This overcomes the first mistake I noted at the start. Sometimes I will do a handful at a time (e.g., when I discovered various commonly used number formats in Excel with Ctrl+Shift+1 (Ctrl+!), Ctrl+Shift+2 (Ctrl+@), Ctrl+Shift+3 (Ctrl+#), Ctrl+Shift+4 (Ctrl+$), Ctrl+Shift+5 (Ctrl+%), Ctrl+Shift+6 (Ctrl+^)—although I never use the last one in my line of work). But when I do teach myself a whole bunch of keyboard shortcuts, there will be some relation between them, as in the list I just showed here.

Secondly, we want to relate it to usage (thus overcoming the second mistake mentioned above). Allow me to explain, because here I am finally coming to that little discipline that I hinted at. For example, this week I learned that Ctrl+6 hides all objects in an Excel worksheet. If I didn’t get that from a list (I did, in this instance: then I will make a note of it. Now I don’t work with objects in most of my sheets, so it may be two or three weeks before I get an opportunity to use that one. By then, I will have forgotten it. So three weeks from now, most people think to themselves:
“What was that keyboard shortcut for hiding objects again? Arrghh. Can’t remember it. Ah well, that just proves that this keyboard shortcut stuff is a load of junk. I’ll take my mouse, click Home | Find and Select | Selection Pane, and then click on Hide All.”

What I do, in contrast, is use this little discipline: I stop, go back to my list or note, look the keyboard shortcut up, and then use it. This process of using it activates a second memory channel (muscle engrams—go look it up), over and above the cognitive memory process. We all use this, we just don’t think about it. For example, I’m standing in front of the ATM to draw cash, and it’s one of those days where, for the life of me, I can’t remember my PIN number. So what do I do? Hopefully, not pull out the little slip of paper—that everyone can see!—where I scribbled it down. I know people who have had their accounts emptied in this way! No, I make as if I’m going to type the PIN, and suddenly it comes back to me. I am using my muscle engrams (the same things tennis or cricket or any other sport players use to perform those shots so masterfully) to bring back the memory of what my PIN is. In other words, the part of my brain that moves my hand to type my PIN also remembers that action—it is a second memory channel. So when I use that with keyboard shortcuts, I am actually memorising the keyboard shortcut in two different ways (cognitively and “physically”). In fact, even now, I sometimes struggle to recall what a certain keyboard shortcut is, but if I get behind the keyboard and make as if I’m going to do it, I can figure it out again.

Now sure, that process of stopping, looking up the keyboard shortcut, and using it, does take longer than just doing it with the mouse. But it is a short term loss for a long term gain. Once that keyboard shortcut is mastered, I will save a few seconds every time I use it, regaining and overtaking what I have lost in looking it up once or twice.

And one last thing. I know keyboard shortcuts are not for everyone. I must also confess that I am not musophbic (go look it up, although I am twisting the word beyond its original intention), and the mouse has its place—some things really cannot be done without the mouse (some programs, for example, are really not keyboard shortcut friendly) and some things (not many, but they are there) can be done faster with the mouse than the keyboard. Some things, of course, can only be done with the keyboard and mouse in conjunction (macros aside): Did you know that you can select one sentence in Word by holding the Ctrl key while clicking anywhere in that sentence? (This does sometimes trip over abbreviations, though.)

But keyboard shortcuts definitely do help you work faster, and keyboard shortcuts can help out in the most unlikely situations—those that you have never thought of (have you ever tried working with the touchpad of your laptop while seated in a plane going through some turbulence?). So much so that, in addition to keyboard shortcuts, I have memorised quite a few ribbon manipulations on the keyboard (for one example, Alt | JL | F | C to AutoFit a table in Word—I defy you to do it quicker with the mouse than I do with the keyboard. Sure, I could create a keyboard shortcut for that and do it seven split seconds faster, but typing that string is so quick, I hardly see the need.

The question then becomes twofold: So how many keyboard shortcuts do you know, punk? And, more importantly, how many keyboard shortcuts will you know a year from now?

Excel 2013 v Excel 2010 Speed Test

There are a lot of good comments on the Excel 2013 Is Unreasonably Slow post, but none of them worked for me. Even a generous offer from keepItCool, but I can’t send the problem child due to proprietary information. So I’m trying to demonstrate the problem in a simpler fashion.

I create a template and insert it into a new workbook five times. This is more or less what my other code is doing. Actually it’s quite a bit less and the templates in the other code are a lot fatter. But I think it demonstrates the point.

Split Excel 2013 (Home) Excel 2010 (Home) Excel 2013 (Office, Local) Excel 2013 (Office, Network)
Start 0.75 0.55 0.66 2.82
Create template 257.68 161.60 445.96 562.40
Insert template 1 449.85 246.10 739.31 927.03
Insert template 2 638.71 345.13 1,058.17 1,292.53
Insert template 3 832.92 429.95 1,367.04 1,716.61
Insert template 4 1,030.99 517.06 1,696.34 2,109.36
Insert template 5 1,247.35 611.32 2,023.30 2,467.81
End 1,247.63 611.70 2,024.43 2,523.20

Excel home is my machine. I have both versions installed. The office times are where my original code runs in production. The “Local” times are when the code is on the desktop and the “Network” time is when the code is on a server.

There’s a lot I’m not happy about here. If I could cut the 2013 times in half, I would be not-unhappy. Note also that I’m not protecting or unprotecting anything – a known change in 2013 that slows things down.

Give it a try, if you like, and let me know what your times are. The home hardware is Intel i7 860 @ 2.8Ghz with 8GB RAM / Windows 7 64 bit, Excel 32 bit.

You can download TemplateInsertTimeTest.xlsm

Conditional Formatting Icons with Relative References

This stack overflow question is intriguing. The way icon sets works is that you select a range and each cell within that range is evaluated against the other cells in that range (or a hardcoded number). The percent or value you set can be a cell reference, but not a relative cell reference. Let’s look at an example. Here are 24 numbers over two years. I want an icon in all the 2015 cells that shows how it compares to the prior year.

I set up a CF for B14 that looks at B2, but I can’t make B2 relative. It has to be absolute. Look at 7/31/2015. It’s less than 7/31/2014, but still shows an up arrow because it’s being compared to B2.

If I copy this down to the other months, the B2 remains – that’s how absolute works. If I copy B14’s formatting down to all the cells at once, I get two CF rules: one for B14 and one for B15:B25. If I copy the CF down one cell at a time, I get 12 CF rules, but they still all point to B2.

No problem. I’ll use a little

trickery. I select B14:B25 and make a rule that says

The relevant formula is

. You wants absolute references? I gots absolute references. No dice (I put some edge cases in there and copied the 2014 numbers down so I could see what was happening).

That should work, but it doesn’t. Instead of doing it to the whole range at once, I did that same CF to B14 only, then copied it down one cell at a time.

Et voilà! What a pain.

Editing SQL Statements in External Data Queries

Surprisingly, I’ve been using the

macro from this post quite a bit. SendKeys is dangerous, as I’ve said, but I like to live on the edge. Jan Karel commented that I should use Alt-DDE, which gives me the Command Text box to edit the SQL query, but doesn’t give me the opportunity to change the name of the Connection. As I thought about it more, changing the Connection name happens one time and isn’t really the major source of my frustration. In fact, if I were a little more disciplined I could change the name when I setup the Connection in the Friendly Name box.

Then it’s settled. I’ll use Alt-DDE to edit the SQL and I’ll force myself to set the name when I set it up. But wait. One of the things I was really looking forward to in building my own Command Text box was making it bigger by default so I could see the whole SQL string (or at least most of it). The Alt-DDE textbox is only slightly better than the Connection properties Command Text textbox. See for yourself.

That’s a crappy UI. And that’s from someone who spends a lot of time in the Visual Basic Editor.

Then it’s settled. I’ll build my own form for changing the properties I want to change. It’s what I really wanted to do anyway, so why stop lying to myself. What kind of features should I build into this UI? A big textbox is a must. Also, I’d like to be able to add white space and line breaks. Oh, and if I could have SQL parsing, autoformatting, and autocomplete… So basically what I want is SQL Server Management Studio. I already have that. It’s called SQL Server Management Studio. That lead me to my next bit of genius. If I want to edit the SQL, even only a little, I should do it in SSMS. I added a couple of buttons to the Ribbon.

The Copy button copies the SQL to the clipboard, ready for me to paste into SSMS.

I leave the button enabled and check to make sure a QueryTable exists in the procedure. If I wanted to enable/disable the button, I would need to run a SelectionChange event constantly. I didn’t test it, but it seems like too much overhead. The Paste button looks like this

I added one little safety step in here because I know how I am. I take what’s in the clipboard and insert it into the

property. But I put the previous

in the Clipboard when I’m done. That way, when I get distracted and accidentally put something else in the Clipboard before I paste, I can (relatively) easily revert back to what it was.

I’ll give this a try and see how it goes.

One unsolicited plug: I use Red Gate’s SQL Prompt in SSMS. I can’t imaging having to work in SSMS without it. It’s pricey, but if you’re spending any time in SSMS, you should give it a try.

Happy Gerbitz Day

This is the first year you can sing “Happy Birthday” to Excel without having to pay a royalty, so that’s nice.

Here’s my uninteresting Excel story: My first spreadsheet program was SuperCalc. I remember we had orange screens on our PCs. Eventually we graduated to VGA monitors and Lotus 1-2-3 v.1A. I stuck with that version for a long time. I had tons of keystroke macros – whatever the heck those were called in Lotus – and I wasn’t giving them up. Excel burst onto the scene and I barely blinked. I don’t need that fancy new stuff. I used v.1A until 1992 and I think 1-2-3 was on v4 by then.

In 1992, I miraculously got a job at KPMG (nee Peat Marwick). Apple was a client of KPMG, so everyone got a Mac and used Office. It was like living in hell. I was a PC, Lotus 1-2-3 guy and I was forced to use these toys in business. Over time, I got used to my Mac Plus, then my SE30, then my PowerBook. And, of course, I got used to Excel and its obviously superior features to the version of 1-2-3 I was using. I don’t remember what version of Excel that was, I just know that most people in my office sucked at using it. Thankfully 25 years later, every office worker is, at a minimum, competent at Excel. What? That’s not true, you say? There are still people who work with Excel and aren’t competent? What the hell have they been doing for the last 25 years? It’s not like learning Excel is exactly cutting edge. End rant.

Being forced to use Office was probably a pivotal point in my life. (Using Macs in an accounting firm in the early ’90s was just stupid.) Pivotal though it was, I think what really turned me into an Excel geek was an intranet message board that KPMG had. I think they called it the KPMG Knowledgebase, but my memory isn’t so good. I could go on the message board and answer people’s questions about Excel. And I was hooked. Then it was on to newsgroups (nntp), a blog, and the crazy post-Microsoft newsgroup period that has mostly meant for me. What the heck did I do after MS closed the newsgroups but before SO? I don’t remember. I know I visited once, saw a terrible answer from a moderator, and saw that the moderator had selected his own answer as “the” answer. I haven’t been back.

When I first started on the newsgroups, I was more of an Access guy than an Excel guy. I was surely answering more Access questions than Excel at the beginning. It was when I started reading Chip Pearson, Rob Bovey, Stephen Bullen, and others posting about VBA that the tables turned. I realize that Access has VBA, but the Excel object model was, and is, a thing of a beauty. I still do plenty of Access work, but it pales in comparison to the time I spend in Excel.

Other random memories:

  • At my first MVP Summit, everyone thought I was going to be a 60-year-old guy and I was in my mid-thirties. I guess I came off as cantankerous mature in my newsgroup postings.
  • I remember after a year of DDoE, a bunch of fellow Excellers joined as authors. There were some great posts back then.
  • I remember applying for a job and taking an Excel and an Access test. I aced them both. The secretary was looking at me like I was a witch. (If you’re reading this blog, you could ace them too.)
  • I remember planning an Australian Excel conference over beers and actually going through with it. If I had a nickel for every plan I made over beers that came to fruition, I’d have a nickel.

Connection Properties of External Data Ranges

I have a workbook with several connections to SQL Server. When I need to change the SQL statement, I do that in Connection Properties.

I added a command to the QAT to show the connection properties dialog, but there’s something I don’t like about it. If I’m in a table with a connection, it’s pretty likely that I want to see the properties of that particular connection and not just a list of all connections. Of course I’m awesome at naming my connections so I don’t have to guess which is which, but if you weren’t so awesome you might have trouble distinguishing them.

The long-term answer is to write my own interface to change the things I want to change. But in the mean time, I want to open the connections dialog and highlight the connection related to the table I’m in, if any.

When I open the Connections dialog, I can start typing the name of the connection to get down to it. For example, I could start typing “dup” and it will highlight the first connection that starts with those keys.

With SendKeys, I can type the entire name. First I see if the ActiveCell is in a QueryTable. If it’s not, I just open the dialog. If it is, I open the dialog, wait a couple seconds, then send all the keys in the connection’s name. SendKeys can be very dangerous, but we’re just experimenting here.

What the above code actually does is open the Connections dialog, wait for it to close, then send all those keystrokes into the ActiveCell. Dangerous. And not helpful. Apparently the Connections dialog is modal and all code is suspended until it’s closed. I did a little searching and found this command, which does not help.

Maybe the old CommandBars behave differently than the Ribbon.

Nope. Same as ExecuteMso. One last try. This opens the dialog with SendKeys.

And it works. For some reason sending Alt+A+O opens the Connections dialog modeless, the SendKeys executes, and takes me to the “active” connection. I have a couple of applications on my machine that like to steal the focus, so I try to avoid SendKeys whenever I can (which is always). In this code, I’m using it twice, so I won’t be using it all. Interesting, though, that it seems to be the only way to get what I want.

Along the way, I discovered I could get to the “active” connection’s property sheet with this key sequence:

  1. right-click key
  2. b
  3. a
  4. tab
  5. tab
  6. enter

I guess that will work. It’s a lot of keystrokes, though.

MaxMinFair Rewrite

I read Charles William’s MaxMinFair algorithm and I didn’t like his approach. That’s typical. I’ll read somebody’s code and think “They’re making that too hard”. Then I’ll set about rewriting it. In this case, as in most cases, it turns out that it is that hard, but I wasn’t going to convince myself until I tried it. I ended up with a different approach that’s not shorter, not easier to read, and not easier to follow. Oh well, here it is anyway.

In Charles’s implementation, he allocates an equal amount of the supply to each node, then takes back what that node didn’t need and puts it back in the available pool. When I was looking at the results, I was thinking that the smallest n nodes simply get their demand and only when there’s not enough to go around do we need to do something different than allocate the full demand.

In my implementation, I start by giving everyone what they demand. Then I start with the smallest demand, and if I can accommodate that amount for everyone, I just reduce the amount available and move to the second smallest demand. At some point (the sixth smallest demand in Charles’s data) I can’t meet that demand and still give everyone an equal share. At that point, I give anyone who hasn’t had their demand met an equal amount – the amount that’s already been distributed plus an equal share of what’s left.

Rank Demand Incremental Demand Allocated Remaining
7 0.70 0.70 4.90 13.40
6 1.00 0.30 1.80 11.60
5 1.30 0.30 1.50 10.10
4 2.00 0.70 2.80 7.30
3 3.50 1.50 4.50 2.80
2 7.40 3.90 7.80 (5.00)
1 10.00 2.60 2.60 (7.60)

In the first iteration, I hand out 0.70 to everyone because I have enough supply to do that. In the second iteration, I had out the differential, 0.30, to everyone who’s left because I have enough supply remaining. When I get to #2, I can’t hand out 3.90 to the remaining two nodes because I don’t have enough supply. I’ve allocated up to 3.5 to anyone who’s demanded it, so the last two get the 3.5 plus half of the 2.8 that remains.

Although I didn’t accomplish anything, it was still a fun exercise.

From True and False to Yes and No

I’m writing some code to turn the contents of class modules into an XML file for Affordable Care Act compliance purposes. The XML file spec says that my flag for whether the dependent is a spouse is “Y” or “N”. In my class, I have a Relation property that can be “Son”, “Daughter”, or “Spouse”. I made a new property to return the “Y” or “N”.

I hate writing all those lines to convert a Boolean into something else. I know it’s not that big of a deal, but it just bugs me. So I fixed it.

Now that’s fancy. The comparison is made and the True or False is converted to a Long via the Abs() function (to turn True to 1 instead of -1) and the proper element of the array is selected. It’s still not good enough.

Yeah, that’s better. But it’s so specific to spouses. Spouse is a dependent that gets special attention, so I don’t mind having a dedicated property to it. It’s appropriate for the domain, I think. But if I wanted to really generalize the hell out of it, I might make an IsRelation property and then take my conversion property into a function.

Now I can have complete customization of the return string.