You all know that I love keyboard shortcuts. There is a limit, though. Some of my custom shortcuts clear the Undo stack, which can be a real pain, so I have to balance the productivity gains of the shortcut against the side effects. Back in January, I wanted to see which shortcuts I’d been using, so I created some code to keep track. The code is below, but first the results.

No surprise to me, pasting special – values tops the list. I paste values by default unless I need to paste something else. I should really just hijack Ctrl+V. If you’re wondering why the count for this one is so low in the first two-week period, it’s because I have severely ingrained muscle memory with Alt+E+S+V to show the Paste Special dialog and select the Values option. I wasn’t really using Ctrl+Shift+V, the shortcut assigned to this macro. I made an effort to use in early February. I’m not sure if I’ve ever posted this code before, so it’s high time.

You can see that I like to paste the values with the number formats.

If you want to see the code for the other macros in the list, see MakeComma, SelectAdjacentCol, FrozenHome, Wrap Sheets, Formatting Taskpane, Increment Date, ChangeSign, FillSeries.

I can’t find where I ever posted my FillVirtualScreen, CopySum, or GetMappedAddress code, so I guess I should do that in a future post. I was surprised that ShowFormatting wasn’t higher. Also, I thought DecrementDate would have been more used that IncrementDate. And FillSeries only seven times in two months?

To keep track of all this, I created a CLog class and a CLogs class. CLog has LogID, DateTime, Keys, and ProcName getter/setter properties. CLogs is a typical parent class with one exception I’ll show in a bit. In my App class, I added an AddLog and WriteLog procedure.

I had to go to every procedure I wanted to track and add a call to AddLog. Then whenever my App class goes out of scope, the log is written.

In CLogs, I return all the log lines as a big string to write out to the file.

That calls CLog.LogFileLine

This file has a bunch of other stuff in it including half-finished ideas, but if you like…

You can download UIHelpers.zip

You probably already know the trick for changing the signs on a bunch of cells. But if not, here it is:

1. Type -1 into an unused cell on your worksheet
2. Copy that cell
3. Select the cells whose sign you want to change
4. Paste Special – Multiply

I do this quite a bit. Recently when I had to enter a pretty long list of numbers, most of which were negative, I decided to enter them with their sign reversed (for ease of entry) and employ this technique. Then I thought that it was just too much work. So I wrote a macro. It doesn’t do exactly what the Copy – Paste Special – Multiply trick does, but arguably does it better.

Private Const msFORMADD As String = ")*-1"
Private Const msFORMST As String = "=("

Sub ChangeSign()

Dim rCell As Range

gclsAppEvents.AddLog "^+n", "ChangeSign"

If TypeName(Selection) = "Range" Then
For Each rCell In Selection.Cells
If CellCanChangeSign(rCell) Then
If rCell.HasFormula Then
If CellFormulaHasSignChange(rCell) Then
rCell.Formula = RemoveFormulaSignChange(rCell.Formula)
Else
rCell.Formula = Replace(rCell.Formula, "=", msFORMST, 1, 1) & msFORMADD
End If
ElseIf IsNumeric(rCell.Value) Then
rCell.Value = -rCell.Value
End If
End If
Next rCell
End If

End Sub

Function CellCanChangeSign(rCell As Range) As Boolean

CellCanChangeSign = rCell.Address = rCell.MergeArea.Cells(1).Address And Not IsEmpty(rCell.Value)

End Function

Function CellFormulaHasSignChange(rCell As Range) As Boolean

CellFormulaHasSignChange = Left$(rCell.Formula, Len(msFORMST)) = msFORMST _
And _
Right$(rCell.Formula, Len(msFORMADD)) = msFORMADD

End Function

Function RemoveFormulaSignChange(ByVal sFormula As String) As String

Dim sReturn As String

sReturn = Left$(sFormula, Len(sFormula) - Len(msFORMADD)) 'remove last characters
sReturn = Replace$(sReturn, msFORMST, "=", 1, 1) 'remove first paren

RemoveFormulaSignChange = sReturn

End Function

is the entry point procedure and is called with Ctrl+Shift+n (more on what AddLog is in a later post). After it determines that there is a range selected (and not a shape, for example), it calls

to make sure it’s OK to move forward. In

I try to deal with merged cells. I don’t use merged cells a ton, so I can never remember how to avoid problems in VBA when dealing with them. I decided that if the cell was the first cell in the merge area, that was good enough. If the cell isn’t merged, its MergeArea is equal to itself, so this would still return True. Also, I don’t want to do anything to empty cells so I make sure that’s not the case.

Once I verify that the cell is good to go, I treat cells with formulas different than cells with values. When you use Paste Special – Multiply, Excel takes whatever formula you have and changes the formula to multiply by -1. For example, =SUM(F6:F14) becomes =(SUM(F6:F14))*-1. I’m not sure why it doesn’t negate the formula with -(formula), but it doesn’t. I made mine match what Excel does.

One problem with changing the signs of formulas is that if you do it twice, then =SUM(F6:F14) becomes =((SUM(F6:F14))*-1)*-1. Of course what else could it do. It’s a multiplying operation, not a toggle. I tried to make mine a toggle at least where I could. If you monkey with the formula between operations, you may get multiple multiplications, but if you just run the code twice it will remove what it did. I’ve tested this nearly a half dozen times, so I’m sure it’s rock solid.

checks that

is at the start of the formula and

is at the end. If they are then

RemoveFormulaSignChange

 removes them and sets the formula back to its original.

For cells with numbers and not formula, the Value is simply negated.
			

Hey, remember back in 2010 when I had that bright idea about a repurposing Ctrl+PgUp and Ctrl+PgDn? Sure you do. The idea was that when I’m at the end of a workbook with a lot of worksheets, it would be easier to hit Ctrl+PgDn and wrap around to the first sheet rather than hold down Ctrl+PgUp until I got to the first sheet. I gave that shortcut the ol’ college try, but in the end I abandoned it. It turns out that I like holding down Ctrl+PgUp to get to the first page. But when I do that and this utility is active, it blows right by the first page and wraps around.

There’s this thing called Fitts’ Law and it indicates some things about infinity

Fitts’ law indicates that the most quickly accessed targets on any computer display are the four corners of the screen, because of their pinning action, and yet, for years, they seemed to be avoided at all costs by designers.

Fitts’ Law deals with pointing devices and targets, but the same principle applies to hotkeys. In Excel, if you hold down Ctrl+PgDn for infinity, you will end up on the last visible sheet of your workbook. That’s powerful because it removes all of your precision responsibility. I don’t care how imprecise you are, you can certainly hit an infinitely wide barn. Contrast that with the File menu in Excel 2010. Press Alt+F and then hold the down arrow for infinity. If the UI designers had been paying attention you would end up on “Exit” (the last item on the list), but you don’t. The cursor just keeps looping through the items on the list.

Even though I abandoned the hotkey, I can’t seem to get it out of my head that it’s a good idea. I’m stubborn like that. What if I could have the best of both worlds? What if I could race to the end of a workbook by holding down Ctrl+PgDn, but still wrap around to the first sheet when I wanted to? I changed the code to add a little delay. I started with 1 second, but determined that 1/2 second works better. Now, if the time between the last time I pressed Ctrl+PgDn and now is greater that 0.5 seconds, sheet activation will wrap around to the first sheet. If it’s less than 0.5 seconds, it’s assumed that I’m looking for infinity and remains on the last worksheet. And it all works the same for Ctrl+PgUp, just in reverse. I start with a module level variable and a module level constant.

Private msnLastWrap As Single
Private Const msnWRAPBUFFER As Single = 0.05

The Single msnLastWrap will keep track of the last time I pressed the hotkey. The rest of the code is the same from the prior post except that I added a couple of If statements to check the time differential and of course to set msnLastWrap.

In WrapSheetsDown when it gets to the last sheet (ActiveSheet.Index = LastVisibleSheetIndex) it checks to see how much time has elapsed (Timer – msnLastWrap). Timer is a VBA function that returns the number of seconds since midnight. Regardless of the results of that test, I assign Timer to my module level variable.

The rest of the code is unchanged, but for completeness, here’s what I have in my Auto_Open and Auto_Close procedures respectively.

And the rest of the procedures needed.

I have a half-dozen things in my UIHelpers.xlam add-in that I really miss when I’m using someone else’s machine. One of them is this little gem from 2009: Entering Dates. When I have to actually type a date in a cell, my fingers get very angry with me.

I don’t work with times that often, but I’m doing a technical proofread of a book and it means I’m entering stop and start times several times a day (to keep track of how little I’m getting paid on an hourly basis). I enter times like 17:00 and that works well enough. But it’s not keyboard-sexy like my date entry is. So I set about fixing that.

My first problem was determining if the cell contained a time. The internet says IsDate works equally well for dates and times, and that’s probably true for VB6, but it’s not the case for VBA in Excel.

I read a post somewhere by Andy Pope that said IsDate will return True on the Text property of cell. If I was going to make an IsTime function, it would look like this:

I haven’t tested that extensively but I think it works. I generally don’t like passing ranges into functions that should only require a value. But since I need both Text and Value, it can’t be helped. In the code above, I made a special purpose function that takes a String because I already test for IsDate(.Value) in the calling procedure. It’s a cop out and I don’t like it, but here we are.

In the end, I’m able to control times similarly to how I control dates. Here’s how it works.

	Cell is blank	Cell is not a date	Cell is a time	Cell is a date
Ctrl+;	Enter today	Replace contents with today	Increase time by 1 minute	Increase date by one day
Ctrl+Shft+;	Enter current time	Replace contents with current time	Descrease time by 1 minute	Decreate date by one day