No you CANNOT have more of the same

I like Doug Glancy’s UndoSelections code via his Selectracker utility. It allows you to deselect a cell from a selection that you made while holding the Ctrl Key…something that Excel doesn’t let you do out of the box. Nifty.

(Aside: If you’re NOT a Ctrl freak, then you can also put Excel into Add To Selection mode by pushing Shift + F8, which adds any further cells you click on to the current selection without the need to hold down Ctrl. When you’ve got the cells you want, just push Shift + F8 again.)

I thought I’d try a simpler approach…if the user tries to select something that’s already selected, simply dump it from the current selection. So I came up with this:

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Deselect Target
End Sub

Sub Deselect(Target As Range)
    Dim lngCount As Long
    Dim lngLast As Long
    Dim strTarget As String
    Dim strOld As String
    Dim strNew As String

    'This code allows you to deselect cells when CTRL + Clicking
    strTarget = Target.Address
    lngCount = UBound(Split(strTarget, ","))
    If lngCount > 0 Then
        strNew = "," & Split(strTarget, ",")(lngCount) & ","
        'Need to add the "," as a delimiter so we don't incorrectly identify say $A$1 and $A$10 as the same
        strOld = "," & Left(strTarget, Len(strTarget) - Len(strNew) + 1) & ","
        If InStr(strOld, strNew) > 0 Then
            If strOld <> strNew Then
                strOld = Replace(strOld, strNew, ",")
            End If
            If Right(strOld, 1) = "," Then strOld = Left(strOld, Len(strOld) - 1)
            If Left(strOld, 1) = "," Then strOld = Mid(strOld, 2, Len(strOld))
            Application.EnableEvents = False
            Range(Split(strOld, ",")(UBound(Split(strOld, ",")))).Activate
            Application.EnableEvents = True
        End If
    End If
End Sub

Here’s an illustration: below is a screenshot where I was trying to select cells in a Checker-board pattern while holding Ctrl, but made a stuff-up a couple of clicks ago:



Without VBA, I’d need to start from scratch, because Excel doesn’t let you deselect particular blocks from your current selection. But with my trusty code, all I need to do is try to select the offending block again, and Excel will say Hey…you’ve already got that in your selection. Oh wait…I guess you’re trying to tell me that you want to dump that particular range from the selection, given it’s already selected.

And so it does just that:
…which frees me up to try again:
In fact, as long as I keep holding Ctrl down, I can deselect as many ranges as I want:

It works pretty well. See for yourself: Open the below sample file, hold Ctrl down and do some crazy clicking, and occasionally click something you’ve already selected. ZAP! It’s removed from the current selection.
Unselect_20141111 v3 (Note: I’ve updated this file with snb’s version of the code listed further below.)

Why this isn’t the native behavior right out of the box is beyond me.

There’s bound to be coding improvements, so let’s have ‘em.


snb has a much smarter approach in the comments that lets users deselect individual cells within a particular subs-selection OR deselect a sub-selection in its entirety. I’ve amended the sample file accordingly.

His approach goes a little something like so:

Private WithEvents App As Application
Option Explicit

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Deselect Target
End Sub

Sub Deselect(Target As Range)

  Dim rn As Range
  Dim cl As Range
  Dim sel As Range
  On Error Resume Next
  Set rn = Target.Areas(Target.Areas.Count)

  If Target.Count > 1 And Target.Areas.Count > 1 Then
    If Not Intersect(Range(Replace(Target.Address & "~", "," & rn.Address & "~", "")), rn) Is Nothing Then
      For Each cl In Target
        If Intersect(cl, rn) Is Nothing Then Set sel = Union(sel, cl)
        If Err.Number <> 0 Then Set sel = cl
    End If
  End If
End Sub

And so with SNB’s code, if I were to select a block:
snb block
…and I wanted to ditch the cell in the middle, then I can simply select it while holding Ctrl, and it gets ditched:
snb block 2
Meaning that I can then say apply formatting, to create an in-cell donut:
snb block 3
Much better than my approach. Cheers, snb!

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.

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.

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.

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.

Putting together an Excel VBA course

Hi everyone!

I’m putting together an advanced VBA course and I am looking for feedback, as I plan to make this a top-notch training.
If you have a couple of minutes to spare (or perhaps are interested in attending an advanced VBA training in The Netherlands), please fill out my survey

Thank you in advance!

Jan Karel Pieterse