Excel User Voice and Workbook Calculate

Excel User Voice

At the recent Excel Global Summit the Excel team were keen to explain how they have started using Excel User Voice to ask for and prioritise product improvement suggestions.

https://excel.uservoice.com/

There are 2 important things to note about this:

  1. The Excel Dev Team actually read your suggestions on User Voice!
  2. Starting with Excel 2016 the development and ship cycle is much much faster than the traditional 3 years.

So it really is worthwhile making your suggestions to improve the product.
You get 10 votes on suggestions, and the suggestions are categorised by end-point and product area. So if you want to make a performance suggestion for Excel on Android phones you can focus down, see what other suggestions have been made in this area, and either cast a vote for an existing suggestion or make a new suggestion.

Workbook Calculate

To try this out I made a suggestion (about calculation of course).

At the moment from the UI you can either calculate all open workbooks (F9 or Automatic) or a worksheet (Shift F9).
From VBA you can also calculate a range (Range.Calculate and Range.CalculateRowMajorOrder).

But you cannot calculate a single workbook.

This is really annoying when you have 2 versions of a large slow workbook open, or you have a small rapidly changing workbook open that links to a large mostly static workbook.

My suggestion is to add an additional calculation setting: Calculate Active Workbook Only and from VBA Workbook.Calculate.

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10601079-workbook-level-calculation

So please vote for my suggestion if you think it’s a good idea!

Using Keyboard Shortcuts for Windows Explorer

This content is reposted from insight.trueinsight.za.com with the permission of the author.

In support of International Keyboard Shortcut day, I want to increase everyone’s productivity in using Windows Explorer.

Now I must add that one of the best productivity steps you can make is not to use Windows Explorer! I use Total Commander for most of my file management duties, and there are several (hundred) good reasons why I do. But having said that, if you work on Windows, you cannot really escape Windows Explorer completely, as it is the native file management tool, so most programs, when opening or saving files, simply make use of Windows Explorer for that file management portion. So even if you are working in Word or Excel or a multitude of other programs, when you open or save a file, you are actually using Windows Explorer for that action. And, I have found that some these basic principles described below, with a little creativity, can also be used when doing file management on a Mac (although I am not showing that in this post).

Thus, learning to use Windows Explorer effectively will prove to be a big boost to your productivity (just think how many times you browse to open or save a file each day, and the gains will be readily apparent).

To distinguish between these contexts I will speak, below, of working in Windows Explorer or of working in File Management (some of the shortcuts only work in one of those contexts, or work differently between the two).

I will also not cover “standard” keyboard techniques, like using Tab to move between parts of the window, pressing Enter as the default action, selecting items (files/folders, in this instance) using Ctrl or Shift in combination with the mouse, etc.

One last note. These are specifically for Windows Explorer, meaning they will work for Windows 7, most of them will work for Vista, and a smaller subset will also work for Windows XP. Having said that, apart from the ribbon addition with its own set of navigation tools, most of these functions will also work with File Explorer (i.e., Windows 8 or Windows 10), although I might miss something new from those over here.

Just to help with the explanations below, Figure 1 shows my names for the various parts of the interface, so that you know what I am referring to.

Figure 1 Windows Explorer Interface

Getting there

Charity begins at home, they say. So keyboard shortcuts should start with actually accessing the tools. For many programs (including all the Office programs), these keyboard shortcuts will get you started:

Listing 1 Shortcuts for Opening or Accessing Windows Explorer

F12
File Save As dialog (from most applications)

Ctrl + O
File Open dialog (from most applications)

Win + E
Open Windows Explorer application

The basics

Like most Microsoft programs (I hate working in programs where the developers have not included a good stable of keyboard shortcuts), the Windows Explorer interface comes with a good set of keyboard shortcuts, only they are perhaps less easy to discover than the more traditional interfaces.

Interface

Listing 2 Shortcuts for Working with the Windows Explorer Interface

Alt + D or F4
Select Folder Address box (Alt + D selects the entry in the box, F4 selects the box and shows a history of previous entries)

Ctrl + F or F3 or Ctrl + E
Select Search box

F5
Refresh the current window

F6 or Tab
Moves among panes in Windows Explorer (Adding Shift reverses the direction)

Alt + P
Toggle the preview pane (one of my favourite “hidden” shortcuts)

Ctrl + Scroll
Switch between views (e.g., List, Details, Small/Medium/Large/Extra Large icons). (I wish there were a keyboard only version of this, so if you know of one, enlighten me. I find the scroll functionality on this particular tool a bit fiddly).

Esc
Cancel in Windows File Management

Ctrl + N
Opens a new window in Windows Explorer

Ctrl + W
Closes the current window in Windows Explorer

F11
Toggle full screen view—can also be done using standard Windows 7 navigation tools, ÿ+Up Arrow for full screen (maximize), or ÿ+Down Arrow for normal size (restore)

Management

Listing 3 Shortcuts for File and Folder Management

Ctrl + Shift + N
Create new folder

F2
Rename

Del
Delete selected files or folders (to recycle bin)

Shift + Del
Delete selected files or folders (semi) permanently

Alt + Enter
Open Properties dialog for selected item

Ctrl + Click
Selects multiple individual items

Ctrl + Click and drag with mouse
Copy the item (Windows appends ” – copy” to the file or folder name)

Ctrl + Shift + Click and drag with mouse
Create a shortcut (*.lnk) for the selected item (Windows appends ” – shortcut” to the file or folder name)

Navigation

Listing 4 Shortcuts for Windows Explorer Navigation

Alt + Right Arrow
Follow breadcrumbs forward

Alt + Left Arrow
Follow breadcrumbs backward

Alt + Up Arrow
Moves up one folder level in Windows Explorer

Backspace
Displays the previous folder in Windows Explorer (i.e., same as Alt + Up Arrow).
Moves up one folder level in Windows File Management

A short explanation of the breadcrumbs is in order. As you navigate through your various folders, Windows Explorer maintains a list of all the locations you visited. These locations are not always in linear succession (i.e., in a succession of child- or parent folders). For example, if you are in a certain sub-sub-sub-sub-folder of an external drive, and you click on one of the shortcuts in the Favorites section of the Windows Explorer folder pane (don’t forget these, they represent one place where the mouse can save you a lot of time, although they can be accessed in about the same amount of time with the keyboard shortcuts already shown). This move will have taken you to a different folder on a different drive. You can click on the Back button (rather press Alt + Left Arrow) to go back to that sub-sub-sub-sub-folder of your external drive). And having done that, you can click Forward (rather press Alt + Right Arrow) to go back once more to Documents. Pressing F4 (not Alt + D will show you the complete list of your previous locations, and you can move up and down that list with the Up Arrow and Down Arrow arrows, and press Enter to select the location of your choice.

Folder pane

These shortcuts work only in the folder pane on the left of the file browser window:

Listing 5 Windows Explorer Folder Pane Shortcuts

Right Arrow
Expands the subfolders of the currently selected folder or goes to the first child if already expanded

Numeric Keypad +
Expands the subfolders of the currently selected folder

Numeric Keypad *
Expands everything under the currently selected folder (don’t do this on C:\)!

Left Arrow
Collapses the subfolders of the currently selected folder or goes to the parent if already collapsed

Numeric Keypad –
Collapses the subfolders of the currently selected folder

Ctrl + Shift + E
Expands the Navigation pane to the folder in which the selected item is found.

More advanced bits

Remember that if you have clicked in the file list of Windows Explorer, you can easily jump to certain files by typing the first letters of a name (you have to type reasonably quickly). So, for example, in Figure 1 I can get to the WordPress folder (and bypass the Word folder) by typing “wordp.” Also, if I wanted to move through the various folders starting with a “c,” I could just press “c” repeatedly with a short break in between.

Figure 2 Selecting an item by typing its name

Then, of course, one of my personal favourites, is typing the destination I want to get it, instead of browsing by a series of mouse clicks. This relies on using the file syntax that derives pretty much all the way from MS-DOS. The first thing you need to know is that you can specify lower-level folders by typing their names. You specify (see below) higher level folders with “..\” and the root with only “\” (all sans the quotes, obviously). You can introduce a succession of lower-level folders by typing the folder names, separated by “\” (again sans quotes).

Listing 6 Text Entries Used for Specifying Higher-Level Folders

..\
Move one folder up (can be combined x number of times for x number of levels).

\
In Windows Explorer, takes you to the root of the main drive.
In Windows File Management, takes you to the root of the current drive.

aaa\bbb\ccc
Specifies folder ccc which is a subfolder of bbb, which is itself a subfolder of aaa, which is itself a subfolder of the current folder.

The best way to demonstrate the power of this technique is with a short video. I am saving a page from the Daily Dose of Excel blog by printing it as a pdf from Chrome. This is relevant for a tool that I am developing, and so I want to keep this, and look at it when I work on this tool. I obviously have to indicate where the pdf must be saved. I last saved a pdf in another folder two levels down in the Documents folder, so I first have to move two levels up (which I do by typing “..\..\”), and then down to “Computer Training\Excel\Tips – Excel\Macros\.” This is a lot of scrolling and clicking with the mouse. However, note that as I type the address into the file name box, Windows Explorer gives me all folder and files that match what I have typed. I then use this to select the folders I want in a fraction of the time it would take to do with the mouse (on an unrepresentative sample of one trial, 13s with the keyboard vs 20s with the mouse, for a 35% increase in efficiency!). Also note that by typing the folder names, I “lose” the file name, but Windows Explorer “remembers” it, so that when I eventually reach the folder I am aiming for, the name is given back (I can, of course, then type a different name if I want).

Video 1 Using typing to select a folder or file

One last tip. Sometimes, I have to “load” a file to a certain program. As one example: When I using the Spreadsheet Compare tool to compare two workbooks. I already have Windows Explorer open at the location where the file is (actually, I can do this trick much quicker in Total Commander, but this blog post is about Windows Explorer). In my program (e.g., Spreadsheet Compare) I have to click on the browse button and this opens the Windows Explorer file browser at a predefined location—e.g., a default location like “Documents” or the last used location, neither of which is what I want. But I can copy the address of the location for my file (in Total Commander I can copy the file name with its location, which means I often do not even have to click on Browse). I do this either by clicking in the Windows Explorer Folder Address box and copying the full location there (see Figure 3), or I can, of course, eschew the mouse and do it all with Alt + D, Ctrl + C, Esc (and I’m done before you even had a chance to see what I’m doing!). [As a side note, F4 doesn’t work as well as Alt + D for this one.] Now I can paste the location into the Spreadsheet Compare file browser (of course, I jumped there from Windows Explorer with Alt + Tab), press Enter, and jump straight to the location where the file is. Better yet, I can type “\” and start typing the file name, and Windows Explorer will give me the names that match—even quicker!

Figure 3 Selecting a folder address from the Address box

One last trick. You can also apply filters in the file name box. So, for example, in Figure 4 I want to see only the Excel files, not all the other files, so I type *.xlsx and press Enter. This will filter the display and show me only Excel (and only xlsx Excel) files.

Figure 4 Applying a filter in the file name box

Listbox bulk refresh won’t redraw if called by Listbox click.

I’ve been playing around with ListBoxes recently. Or more to the point, they’ve been playing around with me. After much staring and swearing, I’ve found that you can’t get a ListBox to repaint itself when feeding it an entire list in one go in response to a Listbox_Click event in that same ListBox. It does however repaint correctly if you run the exact same code from somewhere else…say a CommandButton-initiated action.

By new list, I’m talking about populating the ListBox with an array via the Listbox.list=SomeArray approach. The .AddItem method works fine. So there’s always that: you can simply remove all items one by one, then add all the new items one by one. But that’s really slow: on my laptop it takes something like 15 seconds to add 10,000 items to that listbox. And it gets slower the more you add. And you can probably double that, if you need to replace each and every one of those items with something else. Whereas the Listbox.list=SomeArray approach takes less than a second to add ten times as many items. It’s fast.

So why would you want to add tens of thousands of items to a ListBox? And why would you want to perform a wholesale update that list by clicking in the Listbox itself? Here’s why: I’m designing a UserForm to help filter PivotTables. It’s a cross between the existing Pivot Filter functionality and a Slicer. Here’s what you see if you double-click on a PivotField header:
 
Initial State
 
 
Note the Search field at the top, and the three buttons immediately below that search field. If you type something into that field, then instead of displaying everything that’s currently filtered, you instead get a list of any matches, and you can then apply those search results to the underlying PivotTable via those three CommandButtons. The first cb simply filters the PivotTable to reflect the search, and the other two let you add or remove any search result from an existing filter. (The native filter lets you add, but not remove).

I want to do away with those three command buttons, and instead (in the event that a search is performed) simply list those three options at the top of the ListBox above any search results returned. Clicking on those three options will then trigger the exact same code as currently triggered by the Command Button.

Here’s how that looks currently if I actually type something in that Search box (Note I haven’t yet removed the three command buttons this new functionality will make redundant from that Search frame):
 
test search
 
 

I’ve added a simple bit of code to the lbResults_Change() event handler that checks if a user clicks any of those first three options. All that code does is trigger the exact same routines as would be triggered if they’d simply clicked on the actual command buttons themselves:

For i = 0 To 4
    If Me.lbResults.Selected(i) Then Exit For
Next i
Select Case i
    Case 0: cmdApplySearch_Click
    Case 1: cmdAddToFilter_Click
    Case 2: cmdSubtractFromFilter_Click
    Case 3: Me.lbResults.Selected(3) = False
End Select

The listbox gets updated just fine if I click on the command buttons, as you can see from the below. The Pivot has been filtered accordingly, and those contextual search options have been removed from the top of the listbox (and the search field cleared):
 
CB press
 
 
But watch what happens if I trigger the exact same code by clicking that first option in the ListBox itself:
 
ListItem
 
 
As you can see from the above screenshot, it still shows those three options at the top, even though they are NOT in the array that I assigned to the listbox, as evidenced by the screenshot below:
 
Watch
 
 
The Listbox DID get updated, mind:
? .ListCount 3
? .List(0) 263213: ICT Systems Test Engineer
? .List(1) 263299: ICT Support and Test Engineers nec
? .List(2) 839313: Product Tester

…It just didn’t get redrawn. And it won’t get redrawn unless I refill the ListBox via one of those CommandButtons I’m trying to do away with. And it doesn’t seem to matter if I set the focus to the CommandButton before I try to refresh the Listbox, or even clear the ListBox entirely before I try to refresh it.

You can see this behavior for yourself in this sample file I’ve prepared.

When the userform opens, you’ll see this:
 
Userform1
 
 
Clicking the CommandButton correctly loads a new array into the ListBox (and increases the size of the ListBox accordingly):
 
Userform2
 
 
…but if you click in the ListBox itself – which triggers the exact same code – then while you’ll see that the ListBox got expanded, it did not get redrawn:
 
Userform3
 
 
Just as weird: you can see that the ListBox got expanded by two lines. That’s because the ListBox_Click event got executed twice…even though I have the requisite event suppression code in place (and I’m not talking about application.enable events here, because that doesn’t work for UserForm events). Putting a breakpoint in the code also shows that the 2nd time it runs occurs immediately after the previous run finishes, and not as a response to the .list = SomeList bit that normally triggers such repeat runs.

And if I now click that CommandButton, you can see that the missing numbers from the double ListBox_Click pass are in fact there, as well as the extra addition from the latest CommandButton_Click:
 
Userform4
 
 
If I click that Use .AddItem Approach radio button and then click in the ListBox, things go according to plan:
 
Userform5
 
 
So there’s always that approach. But that approach sucks. Maybe I’ll be forced to keep those CommandButtons in the UserForm after all. Anyone else experience this issue, or have a workaround up their sleeve?

International Keyboard Shortcut Day 2015

It’s the first Wednesday in November and you know what that means. It’s International Keyboard Shortcut Day. The day when people from all over the world become far less efficient in an effort to be more efficient the rest of the year.

IKSD was started way back in 2014 by me. There are currently zero countries that recognize this day, but that means we have only way to go: up.

How it Works

For one hour today, you commit to using a keyboard shortcut. For the particular operation you choose, you don’t use the mouse for that whole hour. Every time you reach for the mouse to perform your chosen task, you stop, return to the keyboard, and use the keyboard to do the thing. Then give your self a chocolate or some other reward.

Let’s say the keyboard shortcut you choose is Win+E to open a Windows Explorer window (that means hold down the Windows key and press E). If you find yourself reaching for the mouse to click on the little folder icon in your task bar, you stop. Return your hands to the keyboard and press the press Win+E. Congratulate yourself and have a treat.

If you don’t catch yourself and you actually click on the folder icon, close the folder, return to the keyboard and use the shortcut. IKSD isn’t about being more efficient today, it’s about being more efficient every other day of the rest of your life.

What to Choose

There are several degrees of participation.

  1. Pick one shortcut. Maybe Ctrl+O to open a file. Maybe Ctrl+S to save a file. Maybe both. If you accidentally open a file with your mouse, close it and reopen it with the keyboard.
  2. Pick a category. I suggest Navigation as I think it reaps the greatest benefits.
    • Use Alt+Tab to switch between programs.
    • Use Ctrl+Tab to switch between workbooks (go ahead and try Ctrl+Tab in applications other than Excel).
    • Use Win+1 (or 2, 3, 4, etc) to launch programs from your taskbar.
    • Use Win+{text} where {text} is the start of the name of a program you want to launch. I use Win+file to narrow the start menu and choose FileZilla whenever I need to FTP something (see image below).
  3. Move your mouse. Place your mouse on the left side of your keyboard (lefty’s, you know what to do) and only use the mouse with your non-dominant hand. It’s excruciating, but there’s no better way to learn the value of home row.

One hour for one day. Anybody can do that.

What the Hell is Wrong with You?

Several years ago I got tendinitis in my elbow. Some say it was caused by playing golf three times a week with a pretty poor swing. But I think it was a sign from the universe. My doctor told me to move my mouse to the other side of my keyboard. I did it. It sucked. I learned so many keyboard shortcuts just so I wouldn’t have to try to manipulate my mouse with the uncoordinated meat hook on the end of my left arm. And I was converted. Now my mouse lives most of the year on the left side (I switch it back for a few months in the Winter) and my hands only leave the keys when it’s more efficient to do so. I use my mouse several hundred times a day, but only when it’s more efficient.

Enjoy IKSD and leave a comment with how you celebrated. I’ll have a couple more keyboard-oriented posts this week, because here at IKSD headquarters, the party never stops.

It’s Almost IKSD Eve

You know that this Wednesday is International Keyboard Shortcut Day, right? To start the celebration, I’m reposting a blog from trueinsight.za.com with permission of the author. Enjoy!

This week (Week 26 of 2014, for the Excel aficionados), we have had a very interesting discussion on the Word-PC e-mail listbook on Word, together with many others topics that you might benefit from.

First up, how not to learn keyboard shortcuts: Don’t search for a list and try to commit it to memory. This fails, because firstly we are overloading our brains with too much information in too short a space of time (any student can verify this three weeks after an exam). Secondly, we are dealing with the list as an abstract thing, and not relating it to our use.

My own experience has been that a very simple discipline (which I will get to shortly) has worked very well for learning keyboard shortcuts.

Before that, though, it is useful to examine how we discover keyboard shortcuts. Yes, lists are useful for that—every time I see an article on keyboard shortcuts, I make certain that I read it (I just picked up a new Excel keyboard shortcut this week in that manner). Second, we all (or at least those who type as inaccurately as I do) have had the experience of pressing something on the keyboard, and seeing something go haywire (e.g. we wanted Shift+R for an uppercase R, but hit Ctrl+R and right-aligned our paragraph). Normally we just undo (Ctrl+Z), but I normally take some time out, try to see if I can figure out what I just hit (I will sometimes undo and try to recreate the keyboard shortcut with what I thought I was typing). I will also evaluate whether this keyboard shortcut is useful for me—if not (e.g., the keyboard shortcuts in Word for Danish characters are some that, no offense to the Danes, I hope I never have to use), then I just forget about it and carry on with my life. If it does appear to be useful, then I will set about learning it. Also, it pays to be observant. It amazes me that people look at menus, and never see (i.e., observe) the keyboard shortcuts listed in those menus (of course, since Office 2007, the Office keyboard shortcuts are displayed in better-hidden tooltips—yet another way Microsoft seems intent on de-cluttering by dumbing down).

One other note before I get to the learning bit. Some keyboard shortcuts are system-wide—meaning they will either work on the OS level, or will work for almost all programs known to man (if you will excuse the exaggeration). Examples are Ctrl+C for Copy, Ctrl+P for Print, Ctrl+S for Save, etc.). Some are, for want of a better term, platform-wide (e.g., Ctrl+H for Replace in all of the Microsoft Office programs, while Ctrl+R does the same in many other programs). And some are program-specific (e.g., Ctrl+Alt+M for a comment in Word, which is Shift+F2 in Excel). If I am working in a new program, I will (after having duly saved my work), freely experiment in that program with keyboard shortcuts that I believe would work there (i.e., those keyboard shortcuts that are in the first two categories I mentioned). I’ve never had a computer meltdown as a result of this (yet!), and it gives me, so to speak, more bang for my buck for the effort of learning keyboard shortcuts.

So, how do we learn keyboard shortcuts?

Still not there yet…. Some keyboard shortcuts are mnemonic (Ctrl+B for Bold, Ctrl+I for italics, Ctrl+C for Copy—and, on that point, notice the position of the X,C,V keys on a qwerty keyboard, and remember Cut, Copy, Paste—and, in Word, Ctrl+L for Left alignment, Ctrl+R for Right alignment, and Ctrl+J for Justified alignment, etc.). Unfortunately, not all keyboard shortcuts can be mnemonic (e.g., Ctrl+C is already taken, so Centre alignment in Word becomes Ctrl+E). Obviously, mnemonic keyboard shortcuts are going to be easier to remember, but we need to go further.

So, how do we learn keyboard shortcuts?

Firstly, one or two at a time. This overcomes the first mistake I noted at the start. Sometimes I will do a handful at a time (e.g., when I discovered various commonly used number formats in Excel with Ctrl+Shift+1 (Ctrl+!), Ctrl+Shift+2 (Ctrl+@), Ctrl+Shift+3 (Ctrl+#), Ctrl+Shift+4 (Ctrl+$), Ctrl+Shift+5 (Ctrl+%), Ctrl+Shift+6 (Ctrl+^)—although I never use the last one in my line of work). But when I do teach myself a whole bunch of keyboard shortcuts, there will be some relation between them, as in the list I just showed here.

Secondly, we want to relate it to usage (thus overcoming the second mistake mentioned above). Allow me to explain, because here I am finally coming to that little discipline that I hinted at. For example, this week I learned that Ctrl+6 hides all objects in an Excel worksheet. If I didn’t get that from a list (I did, in this instance: http://www.databison.com/so-how-many-of-these-excel-shortcuts-do-you-know-punk) then I will make a note of it. Now I don’t work with objects in most of my sheets, so it may be two or three weeks before I get an opportunity to use that one. By then, I will have forgotten it. So three weeks from now, most people think to themselves:
“What was that keyboard shortcut for hiding objects again? Arrghh. Can’t remember it. Ah well, that just proves that this keyboard shortcut stuff is a load of junk. I’ll take my mouse, click Home | Find and Select | Selection Pane, and then click on Hide All.”

What I do, in contrast, is use this little discipline: I stop, go back to my list or note, look the keyboard shortcut up, and then use it. This process of using it activates a second memory channel (muscle engrams—go look it up), over and above the cognitive memory process. We all use this, we just don’t think about it. For example, I’m standing in front of the ATM to draw cash, and it’s one of those days where, for the life of me, I can’t remember my PIN number. So what do I do? Hopefully, not pull out the little slip of paper—that everyone can see!—where I scribbled it down. I know people who have had their accounts emptied in this way! No, I make as if I’m going to type the PIN, and suddenly it comes back to me. I am using my muscle engrams (the same things tennis or cricket or any other sport players use to perform those shots so masterfully) to bring back the memory of what my PIN is. In other words, the part of my brain that moves my hand to type my PIN also remembers that action—it is a second memory channel. So when I use that with keyboard shortcuts, I am actually memorising the keyboard shortcut in two different ways (cognitively and “physically”). In fact, even now, I sometimes struggle to recall what a certain keyboard shortcut is, but if I get behind the keyboard and make as if I’m going to do it, I can figure it out again.

Now sure, that process of stopping, looking up the keyboard shortcut, and using it, does take longer than just doing it with the mouse. But it is a short term loss for a long term gain. Once that keyboard shortcut is mastered, I will save a few seconds every time I use it, regaining and overtaking what I have lost in looking it up once or twice.

And one last thing. I know keyboard shortcuts are not for everyone. I must also confess that I am not musophbic (go look it up, although I am twisting the word beyond its original intention), and the mouse has its place—some things really cannot be done without the mouse (some programs, for example, are really not keyboard shortcut friendly) and some things (not many, but they are there) can be done faster with the mouse than the keyboard. Some things, of course, can only be done with the keyboard and mouse in conjunction (macros aside): Did you know that you can select one sentence in Word by holding the Ctrl key while clicking anywhere in that sentence? (This does sometimes trip over abbreviations, though.)

But keyboard shortcuts definitely do help you work faster, and keyboard shortcuts can help out in the most unlikely situations—those that you have never thought of (have you ever tried working with the touchpad of your laptop while seated in a plane going through some turbulence?). So much so that, in addition to keyboard shortcuts, I have memorised quite a few ribbon manipulations on the keyboard (for one example, Alt | JL | F | C to AutoFit a table in Word—I defy you to do it quicker with the mouse than I do with the keyboard. Sure, I could create a keyboard shortcut for that and do it seven split seconds faster, but typing that string is so quick, I hardly see the need.

The question then becomes twofold: So how many keyboard shortcuts do you know, punk? And, more importantly, how many keyboard shortcuts will you know a year from now?

Excel 2013 v Excel 2010 Speed Test

There are a lot of good comments on the Excel 2013 Is Unreasonably Slow post, but none of them worked for me. Even a generous offer from keepItCool, but I can’t send the problem child due to proprietary information. So I’m trying to demonstrate the problem in a simpler fashion.

Sub TestTimes()
   
    Dim wb As Workbook
    Dim i As Long
    Dim clsTimer As New CTimer
   
    On Error Resume Next
        Kill Environ$("TEMP") & "\TemplateInsert.xlsx"
    On Error GoTo 0
   
    clsTimer.StartCounter "Version: " & Application.Version & Space(1) & Format(Now, "yymmddhhmmss")
    Set wb = Workbooks.Add
    wb.Sheets(1).Range("A1").Value = "Insert Template"
    wb.SaveAs Environ$("TEMP") & "\TemplateInsert.xlsx"
    wb.Close
    clsTimer.Split "Create template"
   
    For i = 1 To 5
        Set wb = Workbooks.Add(Environ$("TEMP") & "\TemplateInsert.xlsx")
        clsTimer.Split "Insert template " & i
    Next i
   
    clsTimer.StopCounter
   
End Sub

I create a template and insert it into a new workbook five times. This is more or less what my other code is doing. Actually it’s quite a bit less and the templates in the other code are a lot fatter. But I think it demonstrates the point.

Split Excel 2013 (Home) Excel 2010 (Home) Excel 2013 (Office, Local) Excel 2013 (Office, Network)
Start 0.75 0.55 0.66 2.82
Create template 257.68 161.60 445.96 562.40
Insert template 1 449.85 246.10 739.31 927.03
Insert template 2 638.71 345.13 1,058.17 1,292.53
Insert template 3 832.92 429.95 1,367.04 1,716.61
Insert template 4 1,030.99 517.06 1,696.34 2,109.36
Insert template 5 1,247.35 611.32 2,023.30 2,467.81
End 1,247.63 611.70 2,024.43 2,523.20

Excel home is my machine. I have both versions installed. The office times are where my original code runs in production. The “Local” times are when the code is on the desktop and the “Network” time is when the code is on a server.

There’s a lot I’m not happy about here. If I could cut the 2013 times in half, I would be not-unhappy. Note also that I’m not protecting or unprotecting anything – a known change in 2013 that slows things down.

Give it a try, if you like, and let me know what your times are. The home hardware is Intel i7 860 @ 2.8Ghz with 8GB RAM / Windows 7 64 bit, Excel 32 bit.

You can download TemplateInsertTimeTest.xlsm

Conditional Formatting Icons with Relative References

This stack overflow question is intriguing. The way icon sets works is that you select a range and each cell within that range is evaluated against the other cells in that range (or a hardcoded number). The percent or value you set can be a cell reference, but not a relative cell reference. Let’s look at an example. Here are 24 numbers over two years. I want an icon in all the 2015 cells that shows how it compares to the prior year.

I set up a CF for B14 that looks at B2, but I can’t make B2 relative. It has to be absolute. Look at 7/31/2015. It’s less than 7/31/2014, but still shows an up arrow because it’s being compared to B2.

If I copy this down to the other months, the B2 remains – that’s how absolute works. If I copy B14’s formatting down to all the cells at once, I get two CF rules: one for B14 and one for B15:B25. If I copy the CF down one cell at a time, I get 12 CF rules, but they still all point to B2.

No problem. I’ll use a little OFFSET trickery. I select B14:B25 and make a rule that says

The relevant formula is =OFFSET($B$2,ROW()-14,0,1,1). You wants absolute references? I gots absolute references. No dice (I put some edge cases in there and copied the 2014 numbers down so I could see what was happening).

That should work, but it doesn’t. Instead of doing it to the whole range at once, I did that same CF to B14 only, then copied it down one cell at a time.

Et voilà! What a pain.

Editing SQL Statements in External Data Queries

Surprisingly, I’ve been using the SendKeys macro from this post quite a bit. SendKeys is dangerous, as I’ve said, but I like to live on the edge. Jan Karel commented that I should use Alt-DDE, which gives me the Command Text box to edit the SQL query, but doesn’t give me the opportunity to change the name of the Connection. As I thought about it more, changing the Connection name happens one time and isn’t really the major source of my frustration. In fact, if I were a little more disciplined I could change the name when I setup the Connection in the Friendly Name box.

Then it’s settled. I’ll use Alt-DDE to edit the SQL and I’ll force myself to set the name when I set it up. But wait. One of the things I was really looking forward to in building my own Command Text box was making it bigger by default so I could see the whole SQL string (or at least most of it). The Alt-DDE textbox is only slightly better than the Connection properties Command Text textbox. See for yourself.


That’s a crappy UI. And that’s from someone who spends a lot of time in the Visual Basic Editor.

Then it’s settled. I’ll build my own form for changing the properties I want to change. It’s what I really wanted to do anyway, so why stop lying to myself. What kind of features should I build into this UI? A big textbox is a must. Also, I’d like to be able to add white space and line breaks. Oh, and if I could have SQL parsing, autoformatting, and autocomplete… So basically what I want is SQL Server Management Studio. I already have that. It’s called SQL Server Management Studio. That lead me to my next bit of genius. If I want to edit the SQL, even only a little, I should do it in SSMS. I added a couple of buttons to the Ribbon.

The Copy button copies the SQL to the clipboard, ready for me to paste into SSMS.

Public Sub CopySql()
   
    Dim doClip As MSForms.DataObject
    Dim qt As QueryTable
   
    On Error Resume Next
        Set qt = ActiveCell.ListObject.QueryTable
    On Error GoTo 0
   
    If Not qt Is Nothing Then
        Set doClip = New DataObject
        doClip.SetText qt.CommandText
        doClip.PutInClipboard
    End If
   
End Sub

I leave the button enabled and check to make sure a QueryTable exists in the procedure. If I wanted to enable/disable the button, I would need to run a SelectionChange event constantly. I didn’t test it, but it seems like too much overhead. The Paste button looks like this

Public Sub PasteSql()
   
    Dim doClip As MSForms.DataObject
    Dim qt As QueryTable
    Dim sOld As String
   
    On Error Resume Next
        Set qt = ActiveCell.ListObject.QueryTable
    On Error GoTo 0
   
    If Not qt Is Nothing Then
        sOld = qt.CommandText
        Set doClip = New DataObject
        doClip.GetFromClipboard
        qt.CommandText = doClip.GetText
        doClip.SetText sOld
        doClip.PutInClipboard
    End If
   
End Sub

I added one little safety step in here because I know how I am. I take what’s in the clipboard and insert it into the CommandText property. But I put the previous CommandText in the Clipboard when I’m done. That way, when I get distracted and accidentally put something else in the Clipboard before I paste, I can (relatively) easily revert back to what it was.

I’ll give this a try and see how it goes.

One unsolicited plug: I use Red Gate’s SQL Prompt in SSMS. I can’t imaging having to work in SSMS without it. It’s pricey, but if you’re spending any time in SSMS, you should give it a try.