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.
|
1 2 3 4 5 6 |
#IfWinNotActive ahk_class XLMAIN ^;:: 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.
|
1 2 3 4 5 6 7 8 9 10 |
#IfWinActive ahk_class XLMAIN 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
|
1 |
Send #2 |
is because Firefox is pinned to my Windows 7 taskbar in the second location (# is the Windows key in AHK).
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
#IfWinActive ahk_class wndclass_desked_gsk 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
|
1 |
Send %clipboard% site:dailydoseofexcel.com |
, you won’t hear me complain.
Some VBE Sugar
|
1 |
::ThisPath::ThisWorkbook.Path & Application.PathSeparator |
Type ThisPath and it expands to
|
1 |
ThisWorkbook.Path & Application.PathSeparator |
|
1 |
::ppg::Public Property Get |
Type ppg and it expands to
|
1 |
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 Accounting\Restricted\ 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.
Inspiring idea! I just disabled F1 for Excel’s main window and enabled shooting the active word in the VBE into IE prepended with “Excel VBA”: