Some Things I Learned Lately

Some Things I Learned Lately

Seems like every day I pick up something new. So I thought I’d make a quick list of stuff I’ve learned lately.

1) XMLAGG in Teradata
Teradata’s handy XMLAGG function lets lets you flatten a column of data into a delimited list, thereby avoiding recursion. Oracle, to which I’m migrating, also has an XMLAGG function but the closer, and better-named, equivalent seems to be LISTAGG. The Teradata documentation is consistently terrible, so instead I’ll link to this Stack Overflow answer from the worthy dnoeth.

2) 64-bit Office Text File Connection Strings
While updating a fancy data-sucking addin I got an error message that my data connection was broke. Turns out MS changed the ODBC connection string for 64-bit ever so slightly from:

Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

to

Driver=Microsoft Access Text Driver (*.txt, *.csv);Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

There’s two differences. The addition of the word “Access” was quickly apparent when looking at this site. The second one took me some time to spot. Can you see it? Yup, they changed the semicolon after “*.txt” to a comma. I think it looks a lot better.

3) Format vs WorksheetFunction.Text in VBA to Mimic Cell Formats
I’ve done a couple of posts that attempt to give you a sneak preview of how different formats will look when applied to cells. I was using my ActiveCell Viewer to preview different date formats for a cell. The Viewer used the VBA Format function. I noticed that in some cases it returned text that isn’t what you get in a cell with that formatting.

For instance, below I’ve applied a formatting of an increasing number of “d”s to a date. With formatting of two to four “d”s the two outputs match:

format differences 1

However with 5 or 6 “d”s the VBA function begins to return a weird result that doesn’t match what you’d see in a cell with that formatting:

format differences 2

You can see below that a cell formatting of “dddddd” still returns “Friday,” just like WorksheetFunction.Text. In fact if you close the Format Cells dialog and re-open it, you’ll see that the formatting has been reset to “dddd”.

format differences 3

I’ve since fixed my Activecell Viewer and added some features. I’ll try to remember to post the improved version sometime.

4) You Can Undo a Pivot Table Refresh
Who knew? All these years I assumed you couldn’t. And then I tried it.

pivot table unrefresh

5) Pivot Table Grouped Number Ranges, Once Sorted, Can’t Be Forced Back to Numeric Order
At least I can’t figure out how.

pivot number ranges grouped and sorted

Can you?

Filtering Pivot Table Value Fields

Filtering Pivot Table Value Fields

In the old days, before I knew better, when I needed to filter a pivot table Value field I’d do it by using by throwing an autofilter from the Data menu on it:

Pivot with Autofilter

It made me feel dirty but I didn’t know any other way to filter pivot table value fields. Clearly pivot tables provided no filter in the Values columns:

Pivot with no filter

And then one day I read this SuperUser answer by mtone and never looked back. If you don’t know how to apply a Value Filter to a non-Value pivot field I recommend reading it right now:

SuperUser Value Filter answer

Good wasn’t it? I started to write a post about this and realized this was the best explanation I’ve seen. I especially like that mtone explains how choosing different fields to apply the filter to will result in different levels of aggregation, and will change your results.

One Value Field Filtering Oddity

I was prompted to write this post after answering Bijan’s question on Stack Overflow. I quickly helped solve his problem but was perplexed because his original issue was that he was applying a value filter to the value field itself. As I said at the beginning, this isn’t possible. Turns out that’s not quite true. After an extended chat he showed me how he did it.

Before you can do this, you first need to use the field as a Row, Column or Report field. Simply dragging it to the Row area of the Show Filter dialog and then to the Values area will do the trick. Once you do so, you can click the down arrow next to the field name in the pivot’s Show Fields dialog:

show fields with dropdown

At that point you can pick the various Value filters.

value field Value Filters

As Bijan discovered though this doesn’t actually do anything. No filtering occurs (and if it did, it wouldn’t be an aggregate filter anyways). And when you drag the field back to a non-value position it, the filter that is, goes away. Weird and bug-like.

Editing Shape Points for No Good Reason

Editing Shape Points for No Good Reason

Over at Bacon Bits, Mike Alexander has a nice post out yesterday on editing shape points to create custom graphics. It shows how to use a shape’s Edit Points command to create interesting dashboard icons. I haven’t used Edit Points for years, but his post inspired me to fool around with them. I ended up with a bit of code for Editing Shape Points for No Good Reason.

In Mike’s post he shows how you can edit points to modify a half-circle into a more interesting shape. His edit of a half-circle looks something like this (only better):

semicircle to wavy

I thought that was pretty cool, so then I dragged the top below the bottom:

semicircle to wavy

That was fun. Now how about some facial reconstruction for Smiley?

smiley to pointy

At this point of course I had to learn how to program these edits in VBA. The result is code that randomly messes with the edit points, along with colors:

Pointless Point Editing Code

Sub EditPointMadness()
Dim shp As Shape
Dim shpNodes As ShapeNodes
Dim CenterX As Long
Dim CenterY As Long
Dim CurrXValue As Long
Dim CurrYValue As Long
Dim ws As Excel.Worksheet
Dim pointsArray As Variant
Const PointOffset As Long = 200

Set ws = ActiveSheet
If ws.Shapes.Count = 0 Then
    ws.Shapes.AddShape msoShapeSmileyFace, 300, 300, PointOffset, PointOffset
    Exit Sub
End If
Set shp = ws.Shapes(1)
CenterX = shp.Left + (shp.Width / 2)
CenterY = shp.Top + (shp.Height / 2)
Set shpNodes = shp.Nodes
With shpNodes
    .Insert WorksheetFunction.RandBetween(1, .Count), msoSegmentCurve, msoEditingAuto, _
        WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
        WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset), _
        WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
        WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset), _
        WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
        WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset)
    If Timer Mod 2 = 0 Then
        pointsArray = .Item(WorksheetFunction.RandBetween(1, .Count)).Points
        CurrXValue = pointsArray(1, 1)
        CurrYValue = pointsArray(1, 2)
        .SetPosition WorksheetFunction.RandBetween(1, .Count), _
            CurrXValue + WorksheetFunction.RandBetween(-PointOffset, PointOffset), _
            CurrYValue + WorksheetFunction.RandBetween(-PointOffset, PointOffset)
        shp.Fill.ForeColor.RGB = WorksheetFunction.RandBetween(1, 10000000)
        shp.Line.ForeColor.RGB = WorksheetFunction.RandBetween(1, 10000000)
    End If
    If Timer Mod 5 = 0 Then
        .Delete WorksheetFunction.RandBetween(1, .Count)
    End If
End With
End Sub

Every time you run the code above it adds, deletes and/or modifies another point. After a couple of times you get what I like to call “Picasso Smiley”:

smiley picasso

A few more and Smiley is getting blown into the next dimension:

smiley in the next dimension

Hopefully the code above is pretty straightforward. It leaves a few of the settings unrandomized, chiefly whether the new node is straight, shaped or a corner.

One question. How to refresh Excel between shape format changes?
One version of this code had a loop that edited the points every half second. But try as I might I couldn’t get the screen to update and show those changes. The changes would only appear after the code was finished, making a loop pointless. If anybody knows how to do this, please let us know (thereby adding some useful content to this post).

Download
Here’s a workbook with the the code and a couple of buttons to run it.

smiley edit points

Filter by Selected Cell’s Value – Bug With Blanks

Filter by Selected Cell’s Value – Bug With Blanks

I’ve found a bug in Excel 2010 that I don’t find described at all when I search the internet. It involves the Filter by Selected Cell’s Value command and blank cells.

The Issue

In Excel 2010 when I select a blank cell in a table, or a non-table and then choose “Filter by Selected Cell’s Value” from the right-click menu, the column is filtered by what look like Chinese and non-printable characters. It occurs for me every time in a Table. In an informal list it sometimes only occurs after filters are applied.

Testing

I’ve seen the issue on all five Excel 2010 computers I’ve tried, all with Windows 7. I tried it on one computer with Office 2013 and couldn’t replicate the bug.

How to Replicate It

Create a table like this one. It can actually be just one column:

table

Right-click in one of the blank cells and choose Filter > Filter by Selected Cell’s Value. (The mouse-averse can do Menu Key, E, V as described in this wmfexcel post).

filter menu

When I do this all rows are filtered away and I can see the odd filter criteria in the little tooltip that appears:

preview

This image show the filter dropdown and the Number Filters dialog (if you do column A you’ll see a Text Filters dialog instead):

filter dialog

What I’d expect, and see in Excel 2013, is that the (Blanks) item in the dropdown would be checked.

You can see that there are a mix of non-printing characters and what Google Translate sometimes recognizes as Chinese characters. I see different character combinations on different computers. Here’s one of my home computers:

translation

Workarounds

The obvious one is to select “(Blanks)” in the filter dropdown. Another is to upgrade to Excel 2013.

A partial one is to use add the “Autofilter” control to the Ribbon. This only works for non-Table lists though. With a Table it only turns the filters on and off. I think this control goes back to Excel 2003 – you can add it to a right-click menu by using MenuRighter and choosing Add Command > Data > Autofilter.

So, Did Everybody Else Already Know This?

I’m always hesitant to cry “bug,” but this seems pretty clear. My other fear is looking silly for announcing something everybody already knew. Feel free to comfort me, or not.

I haven’t tried it in Excel 2007 yet. If anybody wants to that would be great.

Missing Addin User Warning

Missing Addin User Warning

I develop workbooks that require an addin for some of their functionality. For example, a set of school staffing models whose addin allows for selecting data sources, building pivot tables and saving timestamped copies. I like this structure because it’s very maintainable – it’s much easier to fix a bug or add a feature in one addin than it is to try to track down a bunch of workbooks and update their VBA. Easier on me and better for my users. Of course, people’s computers get re-imaged or replaced and IT departments are unlikely, to say the least, to re-install a VBA addin. Folks often don’t realize their addins are gone and that’s why their model is broken. Yesterday I got an idea about how to warn users about a missing addin. I’m still tinkering with it, but the idea is quite simple.

Use a UDF in the Addin

What I did was create the world’s simplest UDF and put it in the addin:

Public Function IsAddinLoaded() As Boolean
IsAddinLoaded = True
End Function

It doesn’t have to be boolean. It could be a string and return “Cult of the Flying Spaghetti.” That’s because it’s only purpose is to fail when it’s not there.

Missing Addin = Missing UDF = opportunity for IsError formula. So, for example, in cell A1 of the addin-dependent workbook put something like:

="Ye Olde Spreadsheet " & IF(ISERROR(isaddinloaded()*TODAY()),"requires the Green Eyeshade Addin!
Please install it.","is Good to Go")

Then add a little conditional formatting based on the missing addin condition and you get something like this. (I tried referring to the UDF directly in the conditional formatting, but it looks like that’s not allowed.)

Green Eyeshade required

The formula contains a volatile function: TODAY. That’s so if the addin is uninstalled the error is activated with the next change to the worksheet. I’d like to avoid the volatile function and I’ve tried to do this in the VBA itself, but haven’t figured out anything yet.

The message does change to the non-warning one as soon as the addin is installed.

Green Eyeshade installed

The addin a very simple one made up just for this post. It’s called Green Eyeshade and it’s for modernizing old spreadsheets like this one. Its ribbon tab is shown when a workbook meeting certain criteria is opened. Here’s the ribbon tab:

Green Eyeshade tab

And here’s the modernization button in action (wonderful what you can find by googling “Peltier pie charts”).

Green Eyeshade modernized

This idea could also be applied to the problem of warning users to enable VBA when opening xlsm files. I’ve never been a big fan of the “hide everything in the workbook except a warning page” although I do like Mike Alexander’s clear explanation. Anyways, something like this might work as an alternative in that case too.

Download

Here’s a zip file with the xlam addin and the target “olde spreadsheet” xlsx file.

MenuRighter Update!

MenuRighter Update!

MenuRighter is my addin for modifying right-click menus, also known as context menus. It’s been around for three years and downloaded a few thousand times. It’s one of the cooler things I’ve ever made. I’ve just finished making some changes to it.

With MenuRighter you can turn this…

table context menu

into this…

better table context menu

With four clicks I removed the Cut, Copy, Paste and Clear Contents buttons from the List Range Popup (Table) menu. With a few more I added my entire Personal Macro workbook (the cleverly named “myBar”) and its Tables sub-menu. Using MenuRighter’s new search functionality I quickly found and added two filter options along with the “Select Visible Cells” button.

Here’s what MenuRighter looks like. It’s divided into the Source menus and controls on the left and Target ones on the right. The Target menu is the one you’re adding controls to:

MenuRighter form

Removing and Adding Controls
And here’s a couple of quick videos showing how I did the deleting from, and adding to, in the Table context menu. This first one shows how to delete the controls – just select one and click Remove. For even faster removal you can double-click the control in the list:

removing controls

Here I’m selecting the spot to insert the “Clear Selected Filter” and “Filter by Selected Cell’s Value” controls in the Target listbox. Then I filter in the Source combobox, click the Add button, and “hey Presto” they’re added. For even faster addition you can double-click the control to be added.

adding controls

In both examples above, I clicked the “Apply Changes” button to actually modify the context menu. Up until then the “Reset to Current” button will revert the Target listbox to the context menu’s current setup. And at any time you can use the “Reset to Default” button to reset the menu back to Excel’s default.

Moving Controls

You can see in the clip above that controls are added above the selected control in the target menu. (If no control is selected the new control is added at the top. You can also use the Up, Down, Top and Bottom buttons to rearrange and controls in the target menu.

Settings

“But wait” I hear you say, “how did you know to choose to the “List Range Popup” menu to modify the context menu for a Table. The answer is MenuRighter’s handy “Show Labels on Menus” checkbox. It temporarily adds a control with the name and index to the bottom of each right-click menu.

show labels on menus

There’s two other setting checkboxes. “Double-type dots” has to with MenuRighter convention for showing the Menu..Sub-menu..Control hierarchy. For instance, the Open control on the File is shown as Add Command..File..Open. If your search filter involves one of those “double-dots” I didn’t want you to have to type both, so, with that option checked, typing one dot gets you two.

The other Setting “Match Source Names” has to do with typing in the Source dropdown list at the top. That list contains all the toolbars that you can filter through in your search for controls. With the option checked, the combobox autofills the dropdown with the closest-matching item in the list. With it unchecked, it just filters based on whatever you type. It’s hard to describe, so just try it out.

The “Execute” Button
One other helpful new feature is the “Execute” button. When you’ve selected a control in the source list, and if that control is enabled, click Execute to run that control. For example, if you’ve selected Add Command..File..New and you click Execute, you’ll get a new workbook. (Pro Tip – choose the New command without three dots).

Search Tips

If you’ve used MenuRighter before, you’ll see that the main changes involve finding controls. The old version uses more of an Explorer type model of drilling down to controls. This new one lets you filter, with two big advantages: it’s faster and it’s more helpful if you have no idea where to start looking.

For example, the “filter by Selected Cell’s” value is a new control since 2003, so only shows in three context menus. Start typing “Filter by” into the Source combobox and you’ll see your choices instantly. On the other hand the Camera tool has been around (and hidden) forever. Type in “camera” and you’ve found it! “Freeze Panes” is another one that’s easy to find this way.

If you just want to scan through menus you can do it by scrolling through the seventy-or-so menus in the Source combobox. Or, you can choose menus in the Target combobox and click the “Show Current” button. Here’s an example of showing the Add Command controls. (Add Commands represents all the controls that were available in Excel 2003’s menu modification dialog):

Add Command menus

Looking for A Few Testers

I’ll be posting this new version soon, but in the meantime it would be great to have a few folks try it out. If you’re interested please let me know in the comments section or through the contact form.

Tweaking View Side by Side

Tweaking View Side by Side

I use View Side by Side a lot. That means I spend a bit of time fiddling with the two windows because I don’t always want each to take up half the space. I often make one smaller and then adjust the second to have it fill the rest of the screen. This weekend I set about tweaking View Side by Side to adjust one window when the other is resized.

Along the way I learned a few interesting things. The most important to this code was figuring out how tell if View Side by Side is activated. I also noticed some issues with View Side by Side in Excel 2013.

Below is a screenshot of what happens when you resize a window in View Side by Side. You can see that it needs to be followed up with some more fiddling to snug the lower window up against the upper one:

regular View Side by Side

The code I came up with is in an application-level event. It uses a function to test whether View Side by Side is active. It’s designed to work in Excel 2010 (and 2007, although untested) as well as 2013. The 2013 tweaks were a little trickier, because Application.Height returned the same thing as Window.Height in 2013. This means the code needs to store the lower windows top location before resizing it.

You’ll also note that I used On Error Resume Next more freely than normally. After all we’re just resizing windows here, and it’s easier than testing for odd things like heights less than zero:

Public WithEvents app As Excel.Application

'application-level event in a class module
Private Sub app_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
Dim UpperWindow As Excel.Window
Dim LowerWindow As Excel.Window
Dim OldLowerWindowTop As Double
Dim LowerWindowChange As Double

'See function below
If SideBySideOn Then
    With Application
        .EnableEvents = False
        'assign the two windows,
       'based on which is uppermost
       If .Windows(1).Top <= .Windows(2).Top Then
            Set UpperWindow = .Windows(1)
            Set LowerWindow = .Windows(2)
        Else
            Set UpperWindow = .Windows(2)
            Set LowerWindow = .Windows(1)
        End If

        'easy way to deal with heights less than zero, etc.
       On Error Resume Next
        'for Excel 2010 could just use Application.Height
       'but in Excel 2013 each window is its own Application
       If ActiveWindow.Caption = UpperWindow.Caption Then
            OldLowerWindowTop = LowerWindow.Top
            LowerWindow.Top = UpperWindow.Top + UpperWindow.Height
            LowerWindowChange = LowerWindow.Top - OldLowerWindowTop
            LowerWindow.Width = UpperWindow.Width
            LowerWindow.Height = LowerWindow.Height - LowerWindowChange
        Else
            UpperWindow.Height = LowerWindow.Top
            UpperWindow.Width = LowerWindow.Width
        End If
        On Error GoTo 0
        .EnableEvents = True
    End With
End If
End Sub

This code only responds to resizes involving the two horizontal borders in the middle, as well as the right-hand borders. So, if you drag the top border of the upper window nothing adjusts.

Application-Level Event

The sample workbook sets an application-level class in its Workbook_Open event. I’ve posted on this before and there’s lots of good info out there, like this Chip Pearson page.

How Do You Tell if View Side by Side is Active?

That’s an interesting question! Here’s what I tried:

  1. First, I looked for some type of setting, like Application.ViewSideBySideEnabled. I can’t find one. Instead there’s two methods: CompareSideBySideWith and the rather drastic-sounding BreakSideBySide.
  2. It would be nice if you could just get the state of the Ribbon’s View Side by Side button. That would tell you whether it’s active or not. But I don’t know of any way to do this.
  3. You can, however, check the state of Excel 2003-style command buttons. So I tried looking at Application.CommandBars(1).Controls("Window").Controls("Compare Side By Side With").State to check whether it’s pressed. It turns out that control – ID 7698 – has no state. Instead it switches captions from “Compare Side by Side With” (followed by a workbook name if there’s only one choice) to “Close Side By Side.”
  4. Based on #3 I considered getting the state by checking whether the first five letters are “Close” or “Compa.” But of course that wouldn’t work for folks with non-English Excel versions, and I generally shy away from using labels when I can use ID’s.
  5. So, finally I decided to use the state of the “Reset Window Position” button. This button is only enabled when View Side by Side is active.

This leads to a one-line function that checks that button’s status using its ID of 7874. There is one final wrinkle, which is that this button doesn’t appear by default in the Excel 2003 “Window” toolbar. By default it lives only in the “Add Command” toolbar which was the one that appeared in 2003 when you modified a toolbar. No problem. We can find number 7874 wherever it lives by searching through all the commandbars using FindControl. Here’s the function:

Function SideBySideOn() As Boolean
'Reset Window Position
SideBySideOn = CommandBars.FindControl(ID:=7874).Enabled
End Function

Here’s how it looks with the code running:

tweaked View Side by Side

Other Stuff Learned Along the Way

  • If you click the Reset Window Position button with the lower window selected, it moves to the top. I’d never noticed that.
  • There is no Window.Move event
  • View Side by Side in Excel 2013 is harder to use. This makes sense, since one of the biggest changes in Excel 2013 is the change to a Single-Document-Interface, making it easier to separate workbooks to multiple monitors. However it’s hard to grab the right edge of a workbook inside View Side by Side. And, as noted above, Application.Height and Window.Height now seem to be the same thing. This makes sense most of the time, but not when you are in View Side by Side mode and trying to compare the height of one of the workbooks/windows to the overall height.

Download!

Here you are my friend.

Undo Selections With SelecTracker!

Undo Selections With SelecTracker!

On one of the DDOE posts about International Keyboard Shortcut Day, a commenter known only as VvM asked for ways to undo selections in Excel. You know how it goes. You’ve selected various parts of a huge table to turn them bold or purple or something and you grab one more cell than you wanted. Wouldn’t it be great if you could just unselect it by clicking again, the way you can in just about any other #$;@#%! situation you can imagine. So, you keep clicking and it kind of looks like it worked because the active cell is a different color than the rest of the selection, but no. You’re doomed! You’ve got to start over from scratch. Aargh!

Anyways, the answer is there’s no way to undo selections in the Excel interface proper. You need VBA. And VBA is what you get in the handy form of my newest utility, SelecTracker.

Imagine you’re entered in a contest where you only have to click three cells with high dollar amounts to walk away with fistfuls of cash. Once you start you can’t start over, but you are allowed to use free utilities with goofy names that you downloaded from Excel websites (also with goofy names). In that case SelecTracker could really save your bacon:

That’s right. Just uncheck the offending selection(s) in the handy list and it’s like it never happened.

Top three gifts

Other lovely SelecTracker features:

– Start it after your selections are made and you realize you need to unselect
– Doesn’t affect Excel’s Undo stack
– Gives you a better understanding of how Excel selections work

Weird Things I Learned About Selecting in Excel

Try this:

Hold down the Ctrl key and click A1 five times.

Now, in the Immediate window type:
? Selection.Areas.Count and hit Enter

Now enter:
? Selection.Address

immediate window #1

So, all those times you tried to unselect by clicking again, you were really just selecting it even more. If only you’d had SelecTracker back then:

A1 many times

If you hold down the Ctrl key and click in cells A1 to A5 one at a time, you’ll get similar, slightly less weird, results. And if you do it and then start up SelecTracker (or vice-versa), you’ll see a different representation of the same results:

SelecTracker_1

So, each selection (with a small “s”) in a Selection is a VBA Area. On the linked MSDN page the Areas definition reads:

The Areas collection contains one Range object for each discrete, contiguous range of cells within the selection.

But clearly that’s within the VBA world where Range("A1:A5") has one Area. But within a Selection (with a capital “S”) it has one Area for each selection you make while holding down the Ctrl key. “Contiguous” doesn’t really enter into it.

This makes the VBA pretty simple. To populate the form’s listbox, I just cycle through the Selection’s Areas and add each one:

With Me.lstSelections
    .Clear
    For Each SelArea In Selection.Areas
        .AddItem SelArea.Address
        .Selected(.ListCount - 1) = True
    Next SelArea
End With

And to re-select the areas when a listbox item is checked or unchecked:

With Me.lstSelections
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            NewSelection = NewSelection & .List(i) & ","
        End If
    Next i
    NewSelection = Left(NewSelection, Len(NewSelection) - 1)
    ws.Activate
    ws.Range(NewSelection).Select
End With

I thought I’d do the above by Unioning the various Areas represented in the listbox. But once you Union them they’re not Areas any more. At least not if there contiguous. So instead I glued the text from the list items together.

(I don’t think other countries use other delimiters than commas, but can’t remember for sure and had no luck searching for it. Please tell me if you know otherwise.)

The rest of the code uses some concepts from UserForm Application-Level Events, Chip Pearson’s code for making forms resizable, and other neat stuff.

Download it and see for yourself.

Thanks VvM, whoever you are!

Happy International Keyboard Shortcut Day

Happy International Keyboard Shortcut Day

Over at DDOE Dick has decreed the first Wednesday of November as International Keyboard Shortcut Day. In a comment to his post I said I’m about 50-50 in my mouse-to-shortcut ratio.

A literal case in point is my use of Alt-F11, which VBA folk will recognize as the key combo that whisks you from Excel to the Visual Basic Editor. I use it all the time. Oddly, I rarely use it in the other direction.

Speaking of the VBE… it still has old-style toolbar menus, the kind you can easily modify. Whenever I get a new computer there’s a few VBE setup things I always do, like install M-Z Tools and Smart Indenter. I also get rid of many of the buttons, like Cut, Copy and Paste. Ctrl-X, Ctrl-C and Ctrl-V were among the first shortcuts I used regularly and it still gives me a sense of competence and tidiness to trash them.

Speaking of the VBE… some of my favorite shortcuts these days are those that combine the Windows key and arrows for moving windows around on your monitor(s). I use them a lot on my laptop, usually to push the VBE to the right and Excel to the left.

Extreme Keyboard Shortcuts

Poking around for pictures to pad this post, one caught my eye with its double meaning:

keyboard shortcuts 1

This one is just whacky. I’ve read the explanation, but I still don’t know what it does. I think the creator may be as fanatical as Dick:

keyboard shortcuts 1

Tables’ Edit Query Dialog

Tables’ Edit Query Dialog

One of my favorite Excel features is the Edit OLE DB Query dialog, where you can edit a table’s data connection and SQL. If you’re not familiar with table data connections, I’m talking about tables created by clicking something like “From Access” in the ribbon’s Data menu. Besides Access, you can connect to other databases, Excel files, the web, and who knows what else. Here’s a nice example of connecting to Access.

To get to the Edit OLE DB Query dialog, right-click in a table and choose “Table” then “Edit…”

Each time I do so I’m filled with child-like wonder at the ability to throw in a new connection string, switching, for example, from Access to SQL Server.

Edit Query dialog 2

In addition you can change from the default Command Type of “Table” – which returns all the contents of a table, query or view – to “SQL,” which allows you to enter SQL directly into the Command Text box.

As far as I can tell this SQL can be as complex as what you would use directly in that type of database. For example, you can use With statements with a SQL Server connection.

That being said, and depending on my access privilegees, I try to limit the amount of SQL on the Excel side. If possible, I connect to an existing database view and then maybe filter it in the Command Text box. So with a SQL Server view called vwScoresAllYears, I might narrow the results with SQL like “SELECT * FROM vwScoresAllYears WHERE vwScoresAllYears.year IN (2012,2013).”

As cool as that is – and it is – it pales next to the ability to switch connections to a completely different type of database. One time we were porting a project from Access to SQL Server. The front end was a big old workbook with a data table pointing at an Access query. The table had lots of calculated columns and several pivot tables pointing at it. To do the switch, we could have created a new table pointing at the SQL Server view, rebuilt the calculated columns and pointed the pivot tables at it. Instead, I just switched the connection string so that it pointed at the SQL Server view. Since the output columns were exactly the same, the transition was barely noticeable. I just hit refresh and the SQL Server data poured into the table.

The following two pics show what I mean. The first connection is to an Access database…
Access connection

The second connection is to a SQL Server database:SQL Server connection

If you’re wondering, the connection shown in the first picture in this post is to another Excel workbook. In that case there are three worksheets serving as tables. The SQL looks a bit different, because when referring to Excel sheets or ranges, the names are surrounded in brackets, and followed by dollar signs in the case of sheets. If the idea of using SQL on Excel workbooks is new to you, this MSDN page is a good start.

I often find myself copying query settings from one table to another. For a while I’d open the Edit OLE DB Query dialog for a table, copy the connection data, open the dialog for the second one and paste the data. That got kind of boring, so guess what? That’s right, I wrote a tool to do what I want:

Copy Properties tool

The way it works is you select a Source table – by clicking into a table and clicking the form button – and then do the same for the Target. You then select which parts of the query – Connection, Type, or Text – to copy over. You can also just edit the text in the Connection or Command Text boxes. Clicking the Copy button just copies the selected properties from the source side of the form to the target side – it doesn’t change the query properties themselves. You can make further edits in the target textboxes as needed. Clicking the Set Properties button copies applies the properties to the target table and attempts to refresh the table. If the refresh fails you get an error message. At that point you can tinker with the properties some more or click the Restore Last Good button, which will revert the table properties to the last working query.

This is different than the built-in dialog, which just reverts to the last working query. I find this ability to tweak a query until it works quite handy. Another advantage is that it opens up queries created by MS Query without the dialog telling you that you won’t be able to edit it.

To make it user-friendly for Dick and other shortcut-only types, the checkboxes can be reached by clicking the Alt Key combo for that property twice.

I uploaded an empty workbook that has a button to display this modeless form. If you download it be sure to save your work before trying this tool. I’ve used it for a few months now with no big issues, but better safe than sorry. Here’s the link.