Go To Special Blanks no longer my Go To guy…

So I’ve always used Excel’s Go To Special and VBA’s SpecialCells method to select things like formulas, constants, blanks etc from large ranges because I was under the impression that this was efficient. Is is, unless you’re using it to find blanks, in which case it’s a dog.

Try this: Select column A:A, and use Ctrl + Enter to enter say the number 1 into the whole column. Now, delete one of the cells so there’s a blank, push F5 to bring up Goto Special, select the Blanks option, click OK, and go put the kettle on.

Goto Special Blanks

It took about 54 seconds on my PC. Admittedly my PC has been running slow of late, but that’s ridiculous.

Now try the Constants option:
Goto Special Constants

Just over a second.

And in case you think the number of blanks (1) vs the number of constants(1048574) is the culprit, you’re wrong. This takes just as long:
Goto Special Blanks2

Ironically – perhaps moronically – if you use the Go To Special>Blanks option on a range outside of the used range:
Goto Special Blanks3

…it tells you there are none:
No Cells were found

…which is about as helpful as that “Was this information helpful?” message.

So from now on, instead of Go To Special > Blanks I’ll be using Chip Pearson’s FindAll function. You?

I’m using Excel 365 on Windows 8. Anyone NOT get the same behavior on different flavors? Googling vba specialcells xlCellTypeBlanks slow brings up heaps of hits. Quickly.

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.

Sub SwitchAggregate()
   
    Dim pf As PivotField
   
    'Make sure the activecell is in a pivot field
    On Error Resume Next
        Set pf = ActiveCell.PivotField
    On Error GoTo 0
   
    If Not pf Is Nothing Then
        'Toggle between sum and count
        If pf.Function = xlSum Then
            pf.Function = xlCount
        Else
            pf.Function = xlSum
        End If
    End If
   
End Sub

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.

Public Sub DisplayAttachment(olAtt As Attachment, sFile As String, sPath As String)
       
    Dim oShell As Object
    Dim miNew As MailItem
   
    On Error GoTo ErrHandler
   
    If olAtt.Type = olEmbeddeditem Then
        Set miNew = Application.GetNamespace("MAPI").OpenSharedItem(sPath & sFile)
        miNew.Display
    Else
        sFile = GetShortFileName(sPath & sFile)
        Set oShell = CreateObject("WScript.Shell")
        oShell.Run sFile
    End If

ErrExit:
    Exit Sub
   
ErrHandler:
    Select Case Err.Number
        Case -2147023741
            oShell.Run "C:\PROGRA~1\NOTEPA~1\NOTEPA~1.EXE" & Space(1) & sFile
        Case Else
            MsgBox Err.Number & vbNewLine & Err.Description
    End Select
   
    Resume ErrExit
End Sub

Good ol’ error handling. If WScript.Shell can’t open the file, it throws error -2147023741, better known as Automation error. No application is associated with the specified file for this operation. 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.

Joining Two Dimensional Arrays

The Join function takes an array and smushes it together into a String. I love the Join function. The only thing I don’t like about it is when I forget that it doesn’t work on 2d arrays. Join only works with 1-dimensional arrays. The last time my memory failed me, I decided to write my own. And here it is.

Public Function Join2D(ByVal vArray As Variant, Optional ByVal sWordDelim As String = " ", Optional ByVal sLineDelim As String = vbNewLine) As String
   
    Dim i As Long, j As Long
    Dim aReturn() As String
    Dim aLine() As String
   
    ReDim aReturn(LBound(vArray, 1) To UBound(vArray, 1))
    ReDim aLine(LBound(vArray, 2) To UBound(vArray, 2))
   
    For i = LBound(vArray, 1) To UBound(vArray, 1)
        For j = LBound(vArray, 2) To UBound(vArray, 2)
            'Put the current line into a 1d array
            aLine(j) = vArray(i, j)
        Next j
        'Join the current line into a 1d array
        aReturn(i) = Join(aLine, sWordDelim)
    Next i
   
    Join2D = Join(aReturn, sLineDelim)
   
End Function

It’s pretty simple. It loops through the first dimension (the row dimension) and joins each line with sLineDelim. Inside that loop, it joins each element in the second dimension with sWordDelim. What this function doesn’t do is automatically insert itself into only the projects I want. That requires me to remember that I wrote it and where I put it. In reality, I’ll probably reinvent the wheel the next time I need it.

Here’s my extensive testing procedure.

Sub TEST_Join2d()
   
    Dim a(1 To 2, 1 To 2) As String
   
    a(1, 1) = "The"
    a(1, 2) = "Quick"
    a(2, 1) = "Brown"
    a(2, 2) = "Fox"
   
    Debug.Print Join2D(a)
    Debug.Print
    Debug.Print Join2D(a, ",")
    Debug.Print
    Debug.Print Join2D(a, , "|")
    Debug.Print
    Debug.Print Join2D(a, ";", "||")
   
End Sub

Super Bowl Analysis

Every so often my worlds collide like when a football game is so popular that non-football fans are aware of it. This week a bunch of nerds will use Excel to analyze the game and I don’t want to be left out. I’ve isolated what I believe to be the most important factor and data-analyzed the hell out of it.

I think we can all appreciate that the 3D effects really drive the point home. And did you notice the use of color? I know. I’m a genius.

My favorite talking head quote about Super XLIX:

For many people in my family, the advertising shown during the Super Bowl provides as much or more entertainment than the game itself.

You mean a bunch of people only care about the ads? What an insightful thought – had you had it in 1975.

Quick Access Toolbar Usage Update

Ever since the great QAT Usage Survey of last year, I’ve had five controls on my QAT. Up from zero. Here’s how they’ve fared.

  1. Borders: Used zero times. I have my two most common border situations (single underline and grand total) available elsewhere. I guess I don’t muck around with borders as much as I thought.
  2. Text to Columns: Used zero times. I use TTC all the time; sometimes one of my own invention, but equally the built-in kind. The built-in kind is good when I have a lot of rows or if I’m tyring to convert a bunch of numbers into text. But I use Alt+A+E without even thinking, so this button has been lonely.
  3. Connections: Used exclusively. Well, kind of. I always start navigating the Ribbon before I remember I have a QAT at all. Then I back out of the Ribbon and Alt+3 to show the external data connections. I think the last couple times I’ve remembered so it’s possible I’ve turned a corner.
  4. Switch Windows: Used zero times. I Ctrl+Tab to switch windows and simply haven’t needed this.
  5. Table Name: Used exclusively. It didn’t take too long to wean myself from Alt+JT+A to Alt+5. This has been the biggest success.

The QAT has awesome Alt+n shortcuts that I need to use. I just don’t know what to put on there. I use Borders and Switch Windows far less than I thought. I need to find some that I’ll actually use.

The Amsterdam Excel Summit 2015

Hi Excel lovers!

Last year we had a terrific Excel event in Amsterdam in May. This year we’re in for a repeat!

I have just opened registration for what is going to be the place to be for anyone Excel-minded. We have two days full of excellent subjects. An impression:

  • Three in-depth Power Query sessions
  • Two sessions on improving your spreadsheet quality
  • Two sessions on charting, making your life easier and enabling you to build charts you didn’t even know you could
  • Two sessions on pivot tables and formulas
  • A session on how to build UDFs

So why don’t you book your flights and hotels and join us on April 13th and 14th for an unsurpassed Excel experience!

Regards,

Jan Karel Pieterse