Have you been wondering what’s up my keyboarding sleeve lately? Too bad, I’m telling you anyway.
Insert Date
You know how pressing Ctrl+; inserts the current date in Excel? Now I can do that in any program. It’s particularly useful when I’m updating an item at goodtodo.com where I date stamp notes.
^;::
FormatTime, CurrentDateTime,, M/d/yyyy
SendInput %CurrentDateTime%
Return
I’ve already re-purposed Ctrl+semicolon in Excel, so it’s in the section that only works outside of Excel.
Stop Helping Me
The other day I accidentally pressed F1. In Excel. For the last time. I could have just disabled F1, but I thought I’d try something different. This figures out what’s in the active cell and searches for it in Firefox.
f1::
oExcel := ComObjActive("Excel.Application")
value := oExcel.ActiveCell.Formula
Send #2
Sleep 50
Send ^k
Send %value%
Send {Enter}
Return
Now when I press F1, I no longer get a separate window, stealing the focus, and not being very helpful. Instead I get a Google search that’s slightly more helpful.
I had to upgrade my AHK to use the ComObjActive function. The Send #2
is because Firefox is pinned to my Windows 7 taskbar in the second location (# is the Windows key in AHK). Send ^k
(control+k) moves the focus to the Firefox search textbox. If you are using Chrome, for instance, you’d want Ctrl+d because the address bar and the search bar are the same.
That’s not as nice as if I parsed the function, but it gets the job done. I tried to use the clipboard so that I could edit a cell, select a function, and press F1 to get a Google search for this function. I couldn’t get the clipboard to work reliably, no doubt because I was in edit mode. Hmmm, maybe Ctrl+C, then esc to get out of edit mode? Dangerous.
Seriously, Stop Helping Me
Don’t forget VBA help.
f1::
Send ^c
StringLen, length, clipboard
IfLessOrEqual %length%, 1
{
Send ^{LEFT}^+{RIGHT}
Send ^c
}
Send #2
Sleep 50
Send ^k
Send excel vba %clipboard%
Send {Enter}
Return
This copies the selection. If what it copies is more than a single character, it searches for that. If it’s one character or less, it selects the current word under the cursor and searches for that. In both cases, it puts “excel vba” before the search term. If you want to change that to Send %clipboard% site:dailydoseofexcel.com
, you won’t hear me complain.
Some VBE Sugar
Type ThisPath and it expands to ThisWorkbook.Path & Application.PathSeparator
Type ppg and it expands to Public Property Get
And Some Problems
If I type acd in a Windows Explorer address bar (or File Open or File Save dialog) it expands to the AccountingRestricted folder on our main network share. That shortcut used to be acr until I figured out how common acr is in English words. The combination acd appears almost never, at least in my admittedly low brow circles. It does exist in Access’ DoCmd.OpenForm when you want to open the form as a dialog box.
The other major problem I have is writing comments in the VBE. Thankfully I don’t write a bunch of comments, but I do use the words With, If, and For a lot as I’m sure we all do. When I type them, they expand as if I were typing VBA statements. I tried to code something in AHK that would recognize a single apostrophe and ignore stuff that’s typed after it, but I never got it to work. It’s not simply that it didn’t work, it broke just about everything else in that AHK file. I was clearly over my head and deleted the whole attempt. I wish I had saved it so I could at least shown you what not to do.