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

and

, 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

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.

Celebrating my MVP award: Discount offer

Hi everyone!

Every quarter Microsoft announces who are the lucky ones to receive their Most Valuable Professional Award. An MVP award lasts a year, so for a quarter of the MVPs, October 1st is an important day.

I got re-awarded!

And to celebrate that I am offering a 3 day 50 percent discount on my Formula auditing tool: RefTreeAnalyser

From October 8, 2014 to October 10, 2104 you receive 50 % off of the list price when you enter this coupon code: MVP2014

Head over to my website and download the tool, you can try it for free!

Regards,
Jan Karel Pieterse
www.jkp-ads.com

Pimpin’ My Site

When Doug posted about Data Comparison Tricks, I saw Dick tell him to “pimp his site” in the comments.

Having a vivid imagination, this is what went through my head.

Yo Dawg!

Anyway, that’s what I’ll now proceed to do. (Having obtained Dick’s permission first of course!)

Here’s some stuff I’ve been working on recently.

A multi-field Find and Select/Replace tool.

AET Find and Replace

Although a bit old, (like me), some of the code came from this.

AET Cell Watch Form

Here’s the old post about it from back in 2009. (From my former blog, which I’ll also pimp!)

An alternative Status Bar that recognizes numbers even if the format is text. Woohoo!

AET Status Bar

And some games. (For the kids, but you can play too)
Grrr...
That’s enough pimpin’ for now. (I’m making new stuff as I write this) See you next time?

Copy Selection Sum to Clipboard

Last month I posted some metrics on the keyboard shortcuts I use. One of the pieces of code that I could not link to (because I’ve never posted it) is CopySum. I don’t remember what prompted me to write this little procedure, but it has been surprisingly useful. It sums the selected cells and puts that sum into the clipboard. That’s all it does.

Sub CopySum()

Dim doClip As MSForms.DataObject

On Error Resume Next

gclsAppEvents.AddLog "^+c", "CopySum"

Set doClip = New MSForms.DataObject

If TypeName(Selection) = "Range" Then
doClip.SetText Application.WorksheetFunction.Sum(Selection)
doClip.PutInClipboard
End If

End Sub

If I want to get a one-off sum of something and use it in another program, this comes in handy. I could SUM in a cell, copy that cell, paste it, and delete it. If I paste into Notepad, it’s fine, but if I try to paste into Outlook or even Gmail those programs try to get fancy and make an HTML table. Sometimes I just want the text.

One shortcoming of this procedure is that it doesn’t do well with filtered cells. The Selection includes both visible and hidden cells, but I probably only want visible. I’m changing the code to

doClip.SetText Application.WorksheetFunction.Subtotal(9, Selection)

so it works with filtered data.

Opening the Addin Dialog like a Pro

Back in the old days when Excel had menus and toolbars, a guy could use Alt+t+i to open the Addins dialog (Tools – Addins). But that would only work if there was an open workbook. No open workbook, no dialog. Now in the days of the Ribbon, the shortcut is Alt+f+t a a Alt+g (File – Options – Addins – Go). You don’t need to have a workbook open, which is nice, but there is a bit of delay between the two “a’s” in the keyboard sequence.

MS did a wonderful thing when they made the old 2003 menu navigation still work in later versions. Even though there’s no longer a Tools menu, you can still use Alt+t+i to open the dialog. Unfortunately you still need to have a workbook open for it to work. I can’t imagine why that is, but it is.

Well, it’s VBA to the rescue. You can show most any dialog with Applicaiton.Dialogs().Show. But showing the Addins dialog returns an error if there is not an active workbook, just like with the old menus. It’s trivial enough to fix, to wit:

Sub ShowAddinDialog()

Dim wb As Workbook

'Dialog won’t show if there’s no workbook showing
If ActiveWorkbook Is Nothing Then
Set wb = Workbooks.Add
End If

'Show addin dialog
Application.Dialogs(xlDialogAddinManager).Show

'Close wb if it was created
On Error Resume Next
wb.Close False

End Sub

That creates a new workbook if needed, then shows the dialog. It keeps track of whether it created a workbook and, if so, closes it without saving. Hardly worth your time to read this post, you say? You already knew about this, you say? Here’s the real magic. Those old 2003 commandbars still lurk behind the scenes in Excel. If you create new ones, they show up on the Add-ins tab. But you can modify the existing one too. I put this little gem in the Auto_Open macro in the same workbook as my ShowAddinDialog procedure.

With Application.CommandBars(1).Controls("Tools").Controls.Add(msoControlButton, , , 1)
.Caption = "&I"
.OnAction = "ShowAddinDialog"
End With

And then to clean it up in Auto_Close.

On Error Resume Next
Application.CommandBars(1).Controls("Tools").Controls("I").Delete

Commandbars(1) is the menu and Controls(“Tools”) is the Tools menu. I add a new control to position 1 on that Tools menu. I don’t need a fancy caption because I can’t see it anyway. I just need a caption with I as the hotkey. Whichever letter follows the ampersand (&) is the hotkey. The built-in addins menu item has a caption of Add-&Ins... making I the hotkey for it. But mine is higher up, so it wins.

In the previous post I referenced above, I add this macro to the QAT. But the muscle memory of Alt+t+i dies hard. Rather than retrain myself like a normal person, I’m embracing my quirks. I can now use Alt+t+i and get the desired results.

“Always” vs “Never”

I always select data from the top down, when I go to make a chart.
I never think to hit the Ctrl + Down Arrow first, so that I can select the range while leaving the active cell at the top.

I always end up with a chart waaay down at the bottom of that very long selection, where my active cell is.
I never want it all the way down there.

I always select the wayward chart, then move it gingerly up towards the Headings bar, in the vain hope that Excel will go into scroll mode, and let me release the chart where it belongs…at the top.
I never seem to hit that magic sweet spot, where Excel starts scrolling quicker than a crawl.

I always curse, then cut the chart, hit Ctrl + Up Arrow, then paste it up where it belongs.
I never remember this monkey business the next time I go to make a chart.

I always hope that MS will sort out basic usability stuff like this in the next release.
I never hold my breath.

“Yes please” vs “WTF?”

DDOE_Thank you vs. WTF_Formula Notation
Yes please.

DDOE_Thank you vs. WTF_Table Notation
WTF? That formula is the same as the last one, only it uses Table notation!

–Edit–

Out of interest, here’s how that 1st message looks in Excel 2010. (Apologies for the old-school look of these next two screenshots, vs the previous ones. I’m too tight to install Excel 2010 AND 2013 on all the machines in our house, so this screenshot comes from my wife’s PC, which runs XP, on account of that same monetary tightness.)

DDOE_Yes Please vs WTF_Old Correction

Let’s say I do decide to take the option offered in that 2nd bullet point – to close the message and correct the formula myself – and click NO. What do I get?

DDOE_Yes Please vs WTF_Old Yes

You told me that one click ago. Get out of my way, so I can do what I said I was going to do one click ago…i.e. fix the damn thing!

I spend heaps and heaps of my time on usability things when I build stuff in Excel. I can’t comprehend why these really crappy legacy usability issues are still perpetuated by the MS developers release after release. WTF.

Formula Auditing by RefTreeAnalyser: Objects included

Hi all,

I’ve been working on my RefTreeAnalyser again. What I’ve been up to this time is building tools which help with the analysis of dependencies which are mostly hidden from view:

  • Charts (series formula)
  • Pivot table (source data)
  • Data Validation formulas
  • Conditional Formatting formulas
  • Form controls (linked cell, listfillrange)
  • ActiveX controls (linked cell, listfillrange)
  • Picture objects (linked cell)

A new dialog has been added that shows all sources of the objects in your file:

Objects analysed for cell dependencies

Moreover, when you analyse a particular cell for its dependencies, objects are taken into account too (well, to be perfectly honest, only if you purchase a license):

RTAObjectsInRefs

If you haven’t already done so, why don’t you head over to my website and download the tool. The demo is free and (almost!) fully functional.

Regards,

Jan Karel Pieterse
www.jkp-ads.com