AutoHotKey Update

Have you been wondering what’s up my keyboarding sleeve lately? Too bad, I’m telling you anyway.

Insert Date

You know how pressing Ctrl+; inserts the current date in Excel? Now I can do that in any program. It’s particularly useful when I’m updating an item at goodtodo.com where I date stamp notes.

I’ve already re-purposed Ctrl+semicolon in Excel, so it’s in the section that only works outside of Excel.

Stop Helping Me

The other day I accidentally pressed F1. In Excel. For the last time. I could have just disabled F1, but I thought I’d try something different. This figures out what’s in the active cell and searches for it in Firefox.

Now when I press F1, I no longer get a separate window, stealing the focus, and not being very helpful. Instead I get a Google search that’s slightly more helpful.

I had to upgrade my AHK to use the ComObjActive function. The

is because Firefox is pinned to my Windows 7 taskbar in the second location (# is the Windows key in AHK).

(control+k) moves the focus to the Firefox search textbox. If you are using Chrome, for instance, you’d want Ctrl+d because the address bar and the search bar are the same.

That’s not as nice as if I parsed the function, but it gets the job done. I tried to use the clipboard so that I could edit a cell, select a function, and press F1 to get a Google search for this function. I couldn’t get the clipboard to work reliably, no doubt because I was in edit mode. Hmmm, maybe Ctrl+C, then esc to get out of edit mode? Dangerous.

Seriously, Stop Helping Me

Don’t forget VBA help.

This copies the selection. If what it copies is more than a single character, it searches for that. If it’s one character or less, it selects the current word under the cursor and searches for that. In both cases, it puts “excel vba” before the search term. If you want to change that to

, you won’t hear me complain.

Some VBE Sugar

Type ThisPath and it expands to

Type ppg and it expands to

And Some Problems

If I type acd in a Windows Explorer address bar (or File Open or File Save dialog) it expands to the Accounting\Restricted\ folder on our main network share. That shortcut used to be acr until I figured out how common acr is in English words. The combination acd appears almost never, at least in my admittedly low brow circles. It does exist in Access’ DoCmd.OpenForm when you want to open the form as a dialog box.

The other major problem I have is writing comments in the VBE. Thankfully I don’t write a bunch of comments, but I do use the words With, If, and For a lot as I’m sure we all do. When I type them, they expand as if I were typing VBA statements. I tried to code something in AHK that would recognize a single apostrophe and ignore stuff that’s typed after it, but I never got it to work. It’s not simply that it didn’t work, it broke just about everything else in that AHK file. I was clearly over my head and deleted the whole attempt. I wish I had saved it so I could at least shown you what not to do.

Switching Aggregates in Pivot Fields

We’ve all been there. You create a pivot table, add your Values fields, and Excel thinks you want to Count them instead of Sum them just because you have a few blanks.

To fix it, you can click the yellow Count of Labor (for example), choose Value Field Settings, and change the aggregate. Or you can right click on any field and choose Summarize Values By and switch it to Sum. Both good options, but not good enough. I assigned Ctrl+Shft+A to this happy little customer and I’m toggling aggregates like crazy.

There’s probably a bug or two, but so far so good.

Handling Errors when Opening Outlook Attachments

Back in 2013 when I returned to using Outlook as an email client (new job, prior job used Google Apps), I was sprucing up some old code. I have two problems with the code on that page; one I’m solving here and one I don’t know how to solve yet.

The first problem is when someone sends me two attachments. I want to open the first, but have no interest in the second. Most recently this problem manifests itself as an invoice and a packing list. I need the invoice, but I don’t need the packing list. Alt+3 (this macro is third on my QAT) opens the last attachment first, so I’m stuck opening the packing list, closing it, then opening the invoice. In practice, I open it the old fashioned way (Shft+Tab, Home, Ctrl+Shft+RightArrow, Menu, O). Go ahead and try it. You know you want to. The Menu key is the key between Alt and Ctrl on the right side of my keyboard. Even if I concentrate really hard on the first attachment, the code still opens them just like a programmed. I don’t have a solution for this.

The second problem is when someone sends me an attachment with no file extension or some bullshit file extension. I get a text file with a .success extension from a website telling me my upload worked. I’m not sure if they’re just being clever or if there is some other significance, but I do know that Windows, and more specifically WScript.Shell, doesn’t know how to open it. I had some code that checked for no extension and opened it in Notepad++, but recently changed it to handle any unknown file extension.

Good ol’ error handling. If WScript.Shell can’t open the file, it throws error -2147023741, better known as

When that happens, it opens the file in Notepad++. That may not always be the best choice, but usually is. Happy keyboarding.

KeyRocket

Fellow keyboarder, Peter, said I should try KeyRocket.

KeyRocket is an application that teaches shortcuts.

Sounds right up my alley, so I download the evaluation version. It doesn’t say, or I couldn’t find, how long the evaluation lasts. I have a couple of thoughts about the install process. First, I like that when you click the Download button, you not only go to the download instructions, but the file downloads automatically instead of having to click another link. I can see how some people might not like that, but I do. Second, when the installation is complete you get this:

That’s a great message. You don’t have to do anything except read these five instructions or skip them. What I didn’t like about it? The buttons don’t have accelerators so you have to use the mouse to click Next or Skip. Deliciously ironic.

I “used” it for half a day and didn’t notice it was there. I simply don’t use my mouse, particularly in Excel, so there was nothing for KeyRocket to show me. It’s not KeyRocket’s fault; I’m just not the target customer.

The programs that KeyRocket supports are:

  • Windows – I don’t use the mouse much in Windows
  • Excel – Apparently I never use the mouse in Excel
  • Outlook – I don’t use the mouse here either. I’ve already created shortcuts for the things I do in here.
  • Powerpoint – Please. If I had to use PPT in my job, I would weep a thousand tears.
  • Word – I’ve used Word in my day job probably a dozen times in two years. That’s enough.
  • Visual Studio – I program in VBA, so no dice here.

For those programs I use often, I’ve learned the shortcuts or developed by own. The other programs that it supports, I just don’t use.

There are a couple dozen Shortcuts exclusive to KeyRocket, i.e. not built-in to Excel, but created by KeyRocket. Some of those overwrite my existing special shortcuts, so would have to re-assign those if I were sticking with it.

When I used one of the KeyRocket shortcuts, it showed a little box in the bottom right corner that said “First use of a KeyRocket Shortcut” or something like that. It was very unobtrusive. I’m really impressed with the design decisions these guys made.

After a couple days I was having problems with the VBE. I was getting Out of Memory errors and orphaned instances of Excel and the VBE wouldn’t close. I don’t have any evidence that KeyRocket was causing this, but I had to uninstall it along with a couple add-ins because I couldn’t afford to have the errors. I would have been nice to remove those one-by-one to see which was causing and it would be nice to have all the time in the world and $1 million. I don’t have any of those.

Finally, the premium version is $135 per year. It appears they have a premium version and an enterprise version, but I couldn’t tell was what the premium version was premium in relation to. Is there a standard version? Not that I could find. If premium is the base version, then $135 seems steep. Actually $135 one time would seem steep. Are they supposing that after a year you’ve learned all the shortcuts and you don’t renew? Are they supposing that big, faceless companies pay gobs for software and aren’t that price sensitive? Maybe both.

I’m unequivocally in favor of anything that teaches people keyboard shortcuts. On top of that, I was impressed by the design of this product at every turn. If you want to learn some keyboard shortcuts and your boss doesn’t mind parting with $135, give it a try. If you do try it, even for just the trial period, leave a comment with your impressions.

Tidying Up the Project Explorer

You know that pressing Ctrl+R in the VBE takes you to the Project Explorer. Of course you do. I’m not going to sit here and insult your intelligence by pretending that you don’t. But that means you are probably also just as confused as I am about which projects the VBE “chooses” to expand and which stay closed.

I have a lot of add-ins and that makes my Project Explorer very messy. Ideally I want the active project to be expanded and everything else collapsed. But I can’t have that. I decided I’ve had enough and added the following hotkey to my AutoHotKey VBE.ahk file.

^+r::
Send ^r
Send {End}
Loop 20 {
Send {Left 2}{Up}
}

When I’m in the VBE (an IfWinActive condition in VBE.ahk) and press Ctrl+Shift+R, the focus goes to the Project Explorer, goes to the last project, then closes up to 20 of them. Two left arrows will collapse an expanded project and has no effect on a collapsed project. The up arrow goes to the next project. Genius, you say? I humbly agree.

I don’t keep my modules in Folders (Toggle folders using the button at the top of the Project Explorer). If you do, you’ll need to change the {Left 2} to {Left 4}.

Until Next Year

International Keyboard Shortcut Day was a huge success. I expected that I would be the only one participating and I have pretty good evidence that a few other did. Therefore, we blew right through expectations and set the bar high in Year 1.

I put my mouse away at 2:30 and worked pretty much nonstop for an hour. At 3:30, I hadn’t touched my mouse once. I had actually opened a text file to record all the times that I was tempted to use my mouse and the keyboard method I used instead. But there weren’t any. I’m sure I go an hour without reaching for my mouse all the time, so it’s not so surprising that this hour was without temptation. I was really hoping to have some seemingly intractable problem so I could post my crazy keyboard gyrations, but alas.

I tweeted that I had completed my observance of the day and Twitter’s web client presented a big obstacle. I couldn’t compose and send a tweet without using the mouse. Normally I would ‘Find’ the text on the page and hit enter to activate the hyperlink, but it didn’t work in this case. The web is surely the worst for keyboard accessibility, but it was ironic that my IKSD tweet required using the mouse.

I think the benefit of going cold turkey for 1 hour once a year is that you’ll discover a keyboard method for accomplishing some task that you’ll continue to use beyond the day.

Please share your IKSD experience in the comments below. If you used your mouse at all, tell me what the situation was. If you discovered any shortcuts or other keyboard methods that were new to you, share those too. Not all keyboarding is shortcuts – nearly everything on the Ribbon is keyboard accessible, although sometimes it takes more keys than a guy likes.

Until next year, keep those fingers on home row.

Opening, Closing, and Saving Workbooks

Happy International Keyboard Shortcut Day. Today, three quick ones for dealing with workbooks.

: Shows the Open dailog box to open a file from disk. The menu is File – Open (Alt + F + O)

: Saves the active workbook. If the active workbook is previously unsaved, shows the Save As dialog to choose a location to save to. The menu is File – Save (Alt + F + S) for Save and File – Save As (Alt + F + A) for Save As.

: Closes the active workbook. Actually it closes the active window, but if you only have one window open, the workbook is closed. If the workbook is unsaved, Excel will prompt you to save it. The menu File – Close (Alt + F + C). Fun fact:

works to.

Bonus Material

: Closes the Excel application. Excel will prompt you for any unsaved workbooks. I still use Excel 2010, so I have no idea how the SDI works in 2013. I imagine

only closes the active workbook. If you use use Excel 2013, leave a comment.

Navigating and Selecting Cells via the Keyboard

In honor of International Keyboard Shortcut Day, here are some tips for navigating around and selecting ranges via the keyboard.

Arrows
The arrow keys are how you navigate cell-to-cell with your keyboard. Earth shattering revelation, isn’t it?

Ctrl = End
Holding down the

key while using the arrow keys is the same as the

key. For purposes of

, cells are either blank or their not blank. Let’s start with a column of cells with something (anything) in them as in column C below.

C2:C5 are all filled and are contiguous. C6:C7 are both blank. C8:C11 are filled and contiguous and everything below that is blank.

If you’re on C2 and press

you go to the last cell in the contiguous range, or C5. Similarly, if C3 is selected,

takes you to C5 – the last cell in the contiguous range. Conversely, if you’re in C4 or C5 and press

you go to the top of the contiguous range – C2.

The general rule is this: If you’re on a blank cell, you go to the next cell that’s non-blank (or the end of the spreadsheet). If you’re on a non-blank cell, you go to the cell just before the next blank cell (like going from C3 to C5 described above). If you’re on a non-blank cell and the next cell in the direction you’re heading is blank (ex: C5 and you’re going down (C6 is blank)), then you’ll go to the next non-blank cell (ex: C5 down to C8).

It works the same in Column A. If you’re in A1, A3, or A5,

takes you to the cell below it (the next non-blank cell). If you’re in A2 or A4, you go to the next non-blank cell because you’re in a non-blank cell and the next cell is blank.

The reality is that you really don’t need to know all that. Just hit

until you get to the right neighborhood, then use the arrow keys with

to hone in your target.

Extending the Selection

The

key is used to extend the selection. If, for example, you’re in E1 and you hold the

key while arrowing down, the selected range becomes E1:E2, E1:E3, E1:E4, etc. In the below screenshot, cell E1 was selected. The

key was held down and the down arrow was pressed three times.

Now you can combine

and

to select large ranges quickly. The following keystrokes selected the range you see below, staring in C2: Hold down

and press

.

Happy keyboarding.