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.

String concatenation is like the weather…

…everyone complains about it, but nobody does anything about it. Well, certainly not Microsoft, anyhows. But back in 2012 Nigel Heffernan at Excellerando.Blogspot.com did: he put up some nifty code for joining and splitting two dimensional arrays that I just stumbled across. I thought I’d have a go at turning the join one into a function that can be called from the worksheet, and add a few more options while I’m at it. More butchering than tweaking, as you’re about to see.

My revision can be called from the worksheet, and has the following arguments:
=JoinText(Array,[Delimiter],[FieldDelimiter],[EndDelimiter],[SkipBlanks],[Transpose])

Yes, more arguments than at my last social outing. Most are optional and have defaults. Take that how you will. The default delimiter is a comma. The Field Delimiter is a separate Delimiter that gets added if your input array is 2D, and the default is also a comma. EndDelimiter puts an extra Delimiter of your choice on the end if you want one. Aesthetics only, really. The rest are explained below.

First, the result:
JoinText 20141115

  • That orange block is my data.
  • Column D shows the result if you point the function at each respective row
  • Row 8 shows the result of pointing the function at each respective column
  • In rows 12 to 15 you see the result of pointing it at the entire 2D block of data, under different settings.

Those last two results are what happens if the data is laid out by row and then by column, and you’ve incorrectly told the UDF to transpose the input array. If your data happenned to be laid out like this, you wouldn’t need that Transpose argument:
 
JoinText 20141115 2
 
 

The DelimitEnd argument does something pretty minor, really. If we include it, the end of the string gets padded with it – in this case an Exclamation Mark . If we exclude it, the string doesn’t get padded with any extra delimiters:
 
JoinText 20141115 3
 
 

You might notice it skips blanks. It doesn’t have to, if you don’t want it to:
 
JoinText 20141115 4
 
 

And it doesn’t need your two arrays to be the same size:
 

JoinText 20141115 5
 
 

A real-world example where this might be useful is when concatenating lists of names, where some may have more parts than others:
 
JoinText 20141115 6

Both the last two screenshots show examples of using three different delimiters…a space between words, a comma between columns, and something different on the end.

Here’s the code and workbook:
Join Function_20141115

Public Function JoinText(target As Range, _
                           Optional Delimiter As String = ",", _
                           Optional FieldDelimiter As String = ",", _
                           Optional EndDelimiter As String = "", _
                           Optional SkipBlanks As Boolean = False, _
                           Optional Transpose As Boolean = False) As String

'Based on code from Nigel Heffernan at Excellerando.Blogspot.com
'http://excellerando.blogspot.co.nz/2012/08/join-and-split-functions-for-2.html

' Join up a 2-dimensional array into a string.

'   ####################
'   # Revision history #
'   ####################

'   Date (YYYYMMDD)     Revised by:         Changes:
'   20141114            Jeff Weir           Turned into worksheet function, added FinalDelimiter and Transpose options
'   20141115            Jeff Weir           Changed FinalDelimiter to EndDelimiter that accepts string, with default of ""


Dim InputArray As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim lngNext As Long
Dim i_lBound As Long
Dim i_uBound As Long
Dim j_lBound As Long
Dim j_uBound As Long
Dim arrTemp1() As String
Dim arrTemp2() As String



    If target.Rows.Count = 1 Then
        If target.Columns.Count = 1 Then
            GoTo errhandler 'Target is a single cell
        Else
            ' Selection is a Row Vector
            InputArray = Application.Transpose(target)
            Transpose = True
        End If
    Else
        If target.Columns.Count = 1 Then
            ' Selection is a Column Vecton
            InputArray = target
        Else:
            'Selection is 2D range. Transpose it if that's what the user has asked for
            If Transpose Then
                InputArray = Application.Transpose(target)
                Transpose = True
            Else: InputArray = target
            End If
        End If
    End If

    i_lBound = LBound(InputArray, 1)
    i_uBound = UBound(InputArray, 1)
    j_lBound = LBound(InputArray, 2)
    j_uBound = UBound(InputArray, 2)
   
    ReDim arrTemp1(j_lBound To j_uBound)
    ReDim arrTemp2(i_lBound To i_uBound)

    lngNext = 1
    For i = j_lBound To j_uBound
        On Error Resume Next
        If SkipBlanks Then
            If Transpose Then
                ReDim arrTemp2(i_lBound To WorksheetFunction.CountA(target.Rows(i)))
            Else
                ReDim arrTemp2(i_lBound To WorksheetFunction.CountA(target.Columns(i)))
            End If
        End If
        If Err.Number = 0 Then
            k = 1
            For j = i_lBound To i_uBound
                If SkipBlanks Then
                    If InputArray(j, i) <> "" Then
                        arrTemp2(k) = InputArray(j, i)
                        k = k + 1
                    End If
                Else
                    arrTemp2(j) = InputArray(j, i)
                End If
            Next j
            arrTemp1(lngNext) = Join(arrTemp2, Delimiter)
            lngNext = lngNext + 1
        Else:
            Err.Clear
        End If
    Next i
     
    If SkipBlanks Then ReDim Preserve arrTemp1(1 To lngNext - 1)
    If lngNext > 2 Then
        JoinText = Join(arrTemp1, FieldDelimiter)
    Else: JoinText = arrTemp1(1)
    End If
    If JoinText <> "" Then JoinText = JoinText & EndDelimiter


errhandler:
End Function

I like this function. I’m sure I’ll like it even more when you’re all finished polishing it to a bright sheen.

Sheen

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(strOld).Select
            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:
 

Selection1

 
 

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:
 
Selection2
 
 
…which frees me up to try again:
 
Selection4
 
 
In fact, as long as I keep holding Ctrl down, I can deselect as many ranges as I want:
Selection5

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.

—UPDATE—

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
        Err.Clear
      Next
      sel.Select
    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.

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.