Author Archive

Quick Access Toolbar Usage Survey

Chris Macro at TheSpreadsheetGuru surveyed some Excel users to see what’s on their QAT. Here’s my contribution:

I Don’t Use That Thing!

To my surprise there were a handful of Excel bloggers I reached out to who don’t use the Quick Access Toolbar at all! This includes the likes of Petros Chatzipantazis (Spreadsheet1.com & RibbonCommander.com), Andy Pope (AndyPope.info), Dick Kusleika (DailyDoseOfExcel.com), and Oscar Cronquist (GetDigitalHelp.com). Jon Peltier (PeltierTech.com) even went as far as to state that he “hate, hate, hates the QAT (it ain’t worth squat!).” I found this extremely intriguing and I hope these guys will share their philosophy on not making use of the QAT in the comments section below.

That’s good enough company for me. I don’t hate the QAT, I’m simply indifferent to it. I was at home when I responded to Chris’ request and when I got to work I noticed that I had added Speak Cells and Stop Speaking Cells, although I’m sure I’ve never used them. If I have used the speaking thing, I hunted for it on the Ribbon oblivious that I had added it to the QAT.

Incidentally (and uninterestingly) I use it extensively in Outlook. There’s no Application.OnKey so I have to have some way to get at those macros.

Where Are The Macros?

One of the biggest surprises for me was that there were not too many people running macros out of there QAT. I was especially surprised that some people who have dedicated blogs for VBA (cough, cough…Jordan Goldmeier….yeah I’m calling you out!) didn’t have one trace of VBA code hanging out in the QAT. I did get feedback from some stating that most of their macro code used on a regular basis was executed via assigned keyboard shortcuts and that does make sense. About 5 mouths ago I started to shy away from using shortcuts with my macros. Here was my reasoning:

Tell us how you use (or don’t use) the QAT in the comments here or at Chris’ site.

The Future of VBA Development

Remember nine years ago when I posted about the future of vba? Neither did I, but I just re-read it. I think if we keep talking about how VBA is dead, it might actually die someday. Nah, probably not.

John at Global Electronic Trading has the latest VBA eulogy. He asked several VBA community members (including me) to answer four questions about the future of VBA. Here is my response to what killed VBA

[DK] Time killed it. Nothing last forever. Cobol developers were once in high demand. Now Cobol developers are in very high demand – both of them. Microsoft killed it by not updating the IDE or supporting VBA as a viable development platform. Had they invested in VBA, say by integrating .Net into Office the way they did with VB, then it still may have been a viable platform today. But even if that were true, time would kill it eventually.
The internet killed it by adopting Ajax. A lot of developer resources went to web apps and away from COM based development.

Apple killed it by inventing the App Store. None of those developer resources came back to COM, they’re all developing mobile apps now.
So a bunch of stuff killed VBA, but all that means is that evolution killed it. MS evolved their development platform away from VBA just like they evolved away from ANSI C before that.

Go read the rest of the answers. You won’t be surprised by any of the answers, I’ll bet.

I draw two conclusions from this experience:

  1. I need to proof read my emails before I send them.
  2. I don’t care if VBA is dead. It still works for me now, I’m very effective with it, and I’m still solving real problems using it every day. If it’s dead, it’s the best damn corpse in the office.

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.

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

Ctrl+S: 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.

Ctrl+W: 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: Ctrl+F4 works to.

Bonus Material

Alt+F4: 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 Alt+F4 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 Ctrl key while using the arrow keys is the same as the End key. For purposes of End, 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 Ctrl+{Down} you go to the last cell in the contiguous range, or C5. Similarly, if C3 is selected, Ctrl+{Down} takes you to C5 – the last cell in the contiguous range. Conversely, if you’re in C4 or C5 and press Ctrl+{Up} 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, Ctrl+{Down} 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 Ctrl+Arrow until you get to the right neighborhood, then use the arrow keys with Ctrl to hone in your target.

Extending the Selection

The Shift key is used to extend the selection. If, for example, you’re in E1 and you hold the Shift key while arrowing down, the selected range becomes E1:E2, E1:E3, E1:E4, etc. In the below screenshot, cell E1 was selected. The Shift key was held down and the down arrow was pressed three times.

Now you can combine Ctrl and Shift to select large ranges quickly. The following keystrokes selected the range you see below, staring in C2: Hold down Ctrl+Shift and press {Down}{Down}{Down}{Right}.

Happy keyboarding.

Three Easy Formatting Hotkeys

In honor of International Keyboard Shortcut Day, here are three easy to remember shortcuts for formatting text.

Ctrl+B: Makes the selected text bold. Get it? ‘B’ is the first letter of ‘bold’.

Ctrl+I: Makes the selected text italics. Get it? ‘I’ is the first letter of ‘italics’.

Ctrl+U: Makes the selected text underlined. Get it? Of course you do.

International Keyboard Shortcut Day


Did you know that International Keyboard Shortcut Day is the first Wednesday in November? Considering I just made that up, I’ll bet you didn’t. But I hereby proclaim it so.

Mission
IKSD was created to spread awareness that you can become more efficient by knowing and practicing keyboard shortcuts.

How to Celebrate
From 2:30PM to 3:30PM your local time on the first Wednesday in November, don’t use your mouse for any computer activity. There are three levels of participation:

Novice: You’re intrigued by the promise of efficiency, but skeptical the investment will pay off. To observe the day, you’ll move your mouse to the opposite side of your keyboard for that one hour.

Journeyman: You’ve already learned quite a few shortcuts and you just need the discipline to apply them. To observe the day, you’ll use the keyboard in every program except in your browser for that one hour.

Fanatic: You don’t need any convincing that this is the greatest thing since electronic spreadsheets. To observe the day, you will disconnect your mouse for that one hour.

History
I thought of this on my way to work this morning when I (finally) started listening to Chandoo’s shortcut podcast.

Committing to one hour of keyboard only computing is an investment. As with any investment, there will be costs up front. This will be the most unproductive hour of your year. Everything will take longer than it should as you struggle to find the keyboard way of doing things. But it will pay off. During that hour you will learn one shortcut that will stick and it will pay dividends for rest of your life.

Don’t forget to tell your friends.

Autohotkey for WordPress Blogs

I’ve added some autohotkey dealies for when I’m writing blog posts. If you’re blog isn’t about Excel and/or doesn’t use the CodeColorer plugin, they won’t be of much use to you.

;
; AutoHotkey Version: 1.x
; Language:       English
; Platform:       Win9x/NT
; Author:         A.N.Other 
;
; Script Function:
;	Template script (you can customize this template by editing "ShellNew\Template.ahk" in your Windows folder)
;

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

GroupAdd, DDoEPosts, Edit Post ‹ Daily Dose of Excel — WordPress - Mozilla Firefox
GroupAdd, DDoEPosts, Add New Post ‹ Daily Dose of Excel — WordPress - Mozilla Firefox
#IfWinActive ahk_group DDoEPosts
:*:ccvb::
    SendInput [cc_vb][/cc_vb]{left 8}
	Return
	
:*:ccivb::
	SendInput [cci_vb][/cci_vb]{left 9}
	Return
	
:*:pmw::
	SendInput PMW
	Return
	
:*:vbe::
	SendInput VBE
	Return

The first one expands brackets so I can paste in VB code. The second does the same but for inline vb code.

The third and fourth take common abbreviations and expand those so the definition appears when you hover over them. More of those to come, I’ll bet.

Awesome.