Excel interface Archives - Page 2 of 2

Undo Selections With SelecTracker!

Posted on November 6, 2014 by Doug Glancy

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.

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

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:

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:

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

Posted on November 4, 2014 by Doug Glancy

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:

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:

Tables’ Edit Query Dialog

Posted on February 15, 2014 by Doug Glancy

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.

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…

The second connection is to a SQL Server database:

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:

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.

Curious Case of the Replaced Date Slash

Posted on November 15, 2013 by Doug Glancy

At my beloved workplace we’ve developed a fondness for slashes in institution names. If we’d been in charge, it would be Bachmann/Turner Overdrive, the Mason/Dixon line, Obsessive/Compulsive Disorder and well, you get the idea. Aesthetics aside, this causes problems when generating workbooks based on these names. Windows doesn’t like slashes in its file handles, because it uses them as path separators (except when it doesn’t, which is why you should use Application.PathSeparator in your code).

I recently wrote a little function to strip slashes and other shady characters out of file names and replace them with dashes, which don’t stick in Windows’ craw. Up until then, my co-worker was forced to do a search and replace on the offending monikers. In doing so she pointed out that the replace dialog mistakenly says it’s replacing all the slashes in the dates on the sheet as well, even though they remain in place.

For example, take a column like this, with one name and 1,048,574 dates:

And here it is with Excel proclaiming to have replaced 2,097,149 “/”s with “-“s, when clearly it only replaced the one. Silly old Excel!

I theorized that Excel was doing a two-step operation: first counting the slashes – including those in the dates – but then only replacing the non-date ones. I can certainly see myself doing something like that. We figured this was because Excel stores dates as numbers and the human/readable dates such like “11/15/2013” are just formats, and the slashes aren’t “really” there.

Turns out that’s wrong. I should have given Excel more credit. It really does replace the slashes if you ask it to. For example, try a find and replace with explanation points, or “bangs” as some folks call them. I get this:

What seems to happen with dashes is Excel recognizes them as date separators, but, at least on a computer with the slash set as the default date separator, it turns them right back into slashes. You can see this in it’s purest form by typing 11-15-2013 into a cell. It immediately converts the dashes, both in the formula bar and in the cell (at least if you’re using my laptop. I haven’t tested this extensively).

So it really is replacing all those millions of slashes with dashes. But then it converts them right back, without so much as a “by your leave.”

What’s confusing to me is that if I use a custom format like “yyyy.mm.dd” and try to replace the periods, Excel says there’s nothing to replace. That makes sense as the periods are just part of a format. But so are the slashes. What gives?

Speaking of dates, happy anniversary to my wonderful wife!

Single Quotes in Worksheet Names

Posted on April 18, 2013 by Doug Glancy

I was working on a function that uses regular expressions to determine whether a potential name for a workbook, worksheet or range contains illegal characters. I started by writing a little routine to determine which characters are illegal for sheet names. Of course, I could have just used one that I knew was prohibited and got the message below. But then I might never have thought about the use of single quotes in worksheet names.

Here’s the code I used:

Sub IllegalWsNameCharacters()
Dim i As Long

With ActiveSheet
.Range("A1").Cells.ClearContents
For i = 0 To 127
On Error Resume Next
.Name = Chr(i)
If Err.Number <> 0 Then
.Range("A1").Value = .Range("A1").Value & Chr(i)
End If
On Error GoTo 0
Next i
End With
End Sub

It cycles through each of the 128 ASCII characters and tries to use it as the ActiveSheet’s name. An error means that character is illegal, at least by itself, so it’s added to the contents of cell A1. I don’t know why I printed to a cell instead of the Immediate window, but it kept me from immediately (ha!) noticing that the first illegal character was a single quote or, as normal people say, an apostrophe.

You can see it doesn’t show up in the cell contents, only in the formula box. That’s because the single quote is the escape character that tells Excel that whatever follows it is text, and part of its duty is to stay quietly out of sight.

At any rate, I saw it and was surprised at it’s illegality. I’m pretty sure I’ve used an apostrophe in a sheet name before. And, sure enough you can:

What you can’t do is use it as the first or last character in a sheet name. And this makes sense, since single quotes are how Excel surrounds worksheet and workbook names that have spaces in them, as in:

='A spaced out name'!A1 * serious_wks_name!A1

All in all, it seems you are best off leaving single quotes out of your sheet names. A quick web search reveals issues with hyperlinks, OLEDB references and Excel Services REST, whatever that is.

Finally, and interestingly, you can name your sheets with some of the “nonprintable” ASCII character codes, i.e., characters 0 to 31, if only through VBA:

ActiveSheet.Name = Chr(8) & " " & Chr(12) & " " & Chr(17) & " " & Chr(15) & " " & Chr(14)

Mind you, I’m not recommending it.