I downloaded AutoHotkey recently for something not Excel related. Well, it was kind of Excel related so I guess I’ll tell the story. I’ve been using the Save As feature of my Kwik Open add-in and really enjoying the lack of folder navigation. But it has sewn the seeds of discontent. When I’m doing a Save As in other programs, like a PDF viewer, I don’t get the keyboard love. I got AHK so I could quickly jump to some of my favorite folders without leaving the keyboard.
But then I got to thinking about other ways I could use this tool. One of my pet peeves about the Visual Basic Editor (and there are many) is that typing “endif” will auto-expand to “End If”, but typing “endwith” just sits there like an insolent child. I thought maybe I could fix that with AHK. And I could. But that wasn’t enough. Check out these two AHK scripts I wrote.
#IfWinActive ahk_class wndclass_desked_gsk
::with::With{Enter}End With{Enter}{up 2}{end}
:*:thenn::Then{Enter}{Enter}End If{Enter}{up 2}t
The first line tells it to only work in the VBE window and it comes with a windows spy utility to find out the ahk_class of whatever window you like. The second line monitors for when you type the word “with”. When you do that, it replaces it with With and End With – even better than expanding endwith. Then it moves the cursor back up to the With line so you can continue coding. If you type “with” inside a comment, it’s trouble. So that’s something to work on.
The third line looks for when you type “then” and press Enter. It replaces it with an If block and puts the cursor in the middle, ready for more code.
Watch the video to see it in action. I’m not sure why the video is blurry at the start nor do I know how to fix it.
As you might guess, I’m going to love this.
Cool. Very clever Dick.
Didn’t know you could do stuff like that with AHK!
Absolute genius!! The If AHK works perfectly, but my ‘with’ is having some trouble, if i type ‘with’ or ‘With’ all that I get returned is ‘WithWith’.
I tried manipulating manually and putting in a variety of {Down} and `n but with no luck. Am I doing something wrong?
Been using AHK for years. Autocorrect (link below) is an AHK script that works like a less aggressive T9, fixing text as you type. In several years of use, I’ve only disagreed w/ a handful of changes. Since it picks up text anywhere in your system, you can use it to create shortcuts for things like your name, address, email, etc.
:o:me@::myemail@gmail.com
:o:1234n::1234 N Reallylong Blvd.
:o: tells AHK to wait for an ending character, but to put the cursor at the end of the text after making a replacement.
http://www.howtogeek.com/howto/45068/how-to-get-spelling-autocorrect-across-all-applications-on-your-system/
My ‘with’ script didn’t work this morning, but the ‘if’ script did. I changed the ‘with’ script, restarted ahk, changed it back, and restarted ahk, and now it works. Odd.
I don’t know enough about AHK yet to tell you what might be wrong. Here’s what the top of my file looks like:
I don’t think I changed anything from the default.
This is very cool, Dick, and it works in 64 bit Excel too. Woohoo!
I am having the same issue as Rich though… With is being replaced by WithWith. Tried to figure out how to replace “With “, so it triggers on pressing the space key, but no luck there yet.
No idea why I needed these, but some extra {Enter} commands seem to do the trick for me:
#IfWinActive ahk_class wndclass_desked_gsk
::with::With{Enter}{Enter}{Enter}End With{Enter}{up 2}{end}
:*:then
n::Then{Enter}{Enter}End If{Enter}{up 2}tSome slight modifications here tot he above. Rather than replace the first part of With, I just triggered inserting the End With portion after. Also added a couple of others. I wish I could figure out how to kick it off after say Select Case * then fire it off so that I could insert the first Case Is = portion, but no luck there.
#IfWinActive ahk_class wndclass_desked_gsk
:b0:with::{Enter}{Enter}{Enter}End With{Enter}{up 2}{end}
:*:then
n::Then{Enter}{Enter}End If{Enter}{up 2}t:b0:select case::{Enter}{Enter}{Enter}End Select{Enter}{up 2}{end}
:b0:for::{Enter}{Enter}{Enter}Next{Enter}{up 2}{end}
:b0:do::{Enter}Loop{Enter}{up 2}{end}
It’s also interesting to me that the Do Loop only needs a single line feed where the others all need more. Not sure what that is, unless it’s keywords that are messing it up (End, Next?)
Here’s two I added today
I don’t need so may Enters as you, but I don’t know why.
I wonder if it’s something to do with the VBE options. Here’s what I have
Code Settings:
Auto Syntax Check = False, Everything else = True. Tab Width = 4
Window Settings: All true
Aha! Yes, that’s exactly it. I changed the Auto Syntax Check to False and it works nicely.
I wish you could take this:
And make the i a variable piece of text. I don’t always use the same variables, and often use variable names. Would sure be nice if it could figure that so it could take something like “For each rcd” and append the “Next rcd”, no matter what I type. My attempts to use * failed though, probably because it doesn’t know that space, tab or Enter are triggers.
Yes, I agree that would be nice. There is a regex hack that may allow you to do that, but it all seems like more work than it’s worth. I went with “i” because I think 50% of my For loops use it.
How many times you have written this little guy?
Thanks for the time-saver Dick!
works out to be this…
On Error GoTo dbErr: 'function = False 'Default function to FalseDim rst As New ADODB.Recordset, _
sSQL As String
sSQL = "SELECT * FROM "
With rst
.Open sSQL, conn, adOpenStatic, adLockOptimistic, adCmdText
Do While Not .EOF
'Do Something Here
.MoveNext
Loop
.Close
End With
dbErr:
If Not rst Is Nothing Then
If rst.State = adStateOpen Then rst.Close
Set rst = Nothing
End If
That’s worth just for all the times I forget to put .MoveNext in the loop.
David, that is beautiful. Great stuff.
Dick, you’re not alone in forgetting the .movenext
Makes me think there is no end to great hot key ideas.
Could even play some jokes on colleagues!
The macro was not working for me because I have the auto syntax checking turned on. I found a workaround by inserting the End With or End If first and then move back up and insert the With or If … then. Also by using SendInput it seems more reliable, had strange spaces and/or tabs without.
#IfWinActive, Microsoft Visual Basic
;WITH statement
;===========================
;@trigger: with+tab
;@inserts:
;With
;
;End With
;===========================
:
t:with::t{Up}WithSendInput {Enter}{Enter}End With{Up}
return
;IF 1 (after if+tab)
;===========================
;@trigger: if+tab
;@inserts:
;If Then
;
;End If
;
;===========================
:t:if::
SendInput {Enter}{Enter}End If{Enter}{Up 2}
t{Up}If{Space}{Space}Then{Left 5}return
;IF 2 (after then+enter)
;===========================
;@trigger: then+enter
;@inserts:
;... Then
;
;End If
;
;===========================
:*:thenn::
SendInput Then{Enter}{Enter}End If{Enter}{Up 2}`t
return
Ken, thank you for your work-around.
Dick, I never knew about this option, this may just save me from that mental breakdown I get with those stupid error messages.
And here is a hotstring for the “For i = … Next i” using a variable. It capitalizes FOR temporarly so you know that you are in “hotstring mode”.
#IfWinActive, Microsoft Visual Basic
;For
;===========================
;@description: start with for+space, then enter the counter+space
;@trigger: for+space > counter+space
;@abort: after timeout 10s or with Esc
;@inserts:
;For %counter% =
;Next %counter%
;
;===========================
:*:for ::
SendInput FOR{Space}
Input, counter,I V T10,{Space}{Escape}
if (ErrorLevel = “EndKey:Space”)
{
SendInput +{HOME}{DELETE}{Enter}Next %counter%{Enter}{Up 2}For %counter% ={Space}
}
Return
And here is a hotstring for the “For i = … Next i” using a variable. It capitalizes FOR temporarly so you know that you are in “hotstring mode”.
#IfWinActive, Microsoft Visual Basic
;For
;===========================
;@description: start with for+space, then enter the counter(e.g. i)+space
;@trigger: for+space > counter+space
;@abort: after timeout 10s or with Esc
;@inserts:
;For %counter% =
;Next %counter%
;
;===========================
:*:for ::
SendInput FOR{Space}
Input, counter,I V T10,{Space}{Escape}
if (ErrorLevel = “EndKey:Space”)
{
SendInput +{HOME}{DELETE}{Enter}Next %counter%{Enter}{Up 2}For %counter% ={Space}
}
Return
I went with “i” because I think 50% of my For loops use it.
Egocentric!
;-)
It’s very odd, but I really don’t mind the time it takes to add all those bits in VBA, I don’t want something to do it for me (it will always fail sometimes, as in my procedure calls I use … If Not proc(parameters) Then Err.raise apperror.Generic … I don’t want it to put End If on a separate line here), plus it gives me thinking time.
A guy called Chris Spicer has come up with a tool called VBAPrime that does this sort of thing and many more, https://www.linkedin.com/redirect?url=http%3A%2F%2Fwww%2Evbaprime%2Ecom%2FTrialVbaPrime%2Easpx&urlhash=f6Js&_t=tracking_anet. I tried it but I couldn’t get on with it as it kept doing things I didn’t want that I had to then go and undo, so it was really helping me, but lots of others seem to find it very useful.
Well, Bob won’t have any use for this then. I modified @Hubisan’s ‘for’ script to handle For Each
I also took out automatically entering the equal sign. I do that by muscle memory already and end up with two instead of one more often than not.
And speaking of failing, I entered several comments yesterday with the word ‘with’ in them and AHK wreaked havoc on me. But for me, I like the autocompletion more than I dislike the failures.
Another update to avoid problems when opening text files
I would appreciate if someone could add code for automatically adding a closing bracket and moving the cursor between the two brackets.
@Frank: Very lightly tested
@Dick: unfortunately this only works for a standalone opening bracket, but not for the opening bracket of a function.
@Frank: Just when I think I understand AHK, I realize I don’t.
And to get intellisense to pop back up
@Bob: thanks for the mention. I’ve made a lot of improvements and fixes to VbaPrime, as well as replicated some of the MZ Tools functionality that’s not available on 64-bit Office, so hopefully I can tempt you back soon.