AutoHotkey in the VBE

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}
:*:then`n::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.

29 Comments

  1. Pete says:

    Cool. Very clever Dick.

  2. Didn’t know you could do stuff like that with AHK!

  3. Rich says:

    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?

  4. bjacobowski says:

    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/

  5. Dick Kusleika says:

    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:

    ;
    ; AutoHotkey Version: 1.x
    ; Language:       English
    ; Platform:       Win9x/NT
    ; Author:         A.N.Other <myemail@nowhere.com>
    ;
    ; Script Function:
    ;   Template script (you can customize this template by editing "ShellNew\Template.ahk" in your Windows folder)
    ;

    #NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
    SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
    SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

    I don’t think I changed anything from the default.

  6. Ken Puls says:

    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.

  7. Ken Puls says:

    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}`t

  8. Ken Puls says:

    Some 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?)

  9. Dick Kusleika says:

    Here’s two I added today

    ::for i =::For i ={Enter}{Enter}Next i{up}`t{up}{end}
    ::forarray::For i = LBound(arr) to UBound(arr){Enter}{Enter}Next i{up}`t

    I don’t need so may Enters as you, but I don’t know why.

  10. Dick Kusleika says:

    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

  11. Ken Puls says:

    Aha! Yes, that’s exactly it. I changed the Auto Syntax Check to False and it works nicely.

    I wish you could take this:

    ::for i =::For i ={Enter}{Enter}Next i{up}`t{up}{end}

    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.

  12. Dick Kusleika says:

    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.

  13. David Miller says:

    How many times you have written this little guy?

    Thanks for the time-saver Dick!

    :o:adorst::On Error GoTo dbErr: 'function = False 'Default function to False{Enter}{Enter}Dim rst  As New ADODB.Recordset, _{Enter}{Tab}sSQL As String{Enter}{Enter}+{tab}sSQL = "SELECT * FROM "{Enter}{Enter}With rst{Enter}{tab}.Open sSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText{Enter}{Enter}Do While Not .EOF{Enter}{tab}'Do Something Here{Enter}.MoveNext{Enter}+{tab}Loop{Enter}{Enter}.Close{Enter}+{tab}End With{Enter}{Enter}dbErr:{Enter}If Not rst Is Nothing Then{Enter}{tab}If rst.State = adStateOpen Then rst.Close{Enter}Set rst = Nothing{Enter}+{tab}End If

    works out to be this…

        On Error GoTo dbErr: 'function = False 'Default function to False
       
        Dim 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
  14. Dick Kusleika says:

    That’s worth just for all the times I forget to put .MoveNext in the loop.

  15. Pete says:

    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!

  16. Hubisan says:

    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::
    SendInput {Enter}{Enter}End With{Up}`t{Up}With `
    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
    ;
    ;===========================
    :*:then`n::
    SendInput Then{Enter}{Enter}End If{Enter}{Up 2}`t
    return

  17. Rich says:

    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.

  18. Hubisan says:

    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

  19. Hubisan says:

    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

  20. Jeff Weir says:

    I went with ā€œiā€ because I think 50% of my For loops use it.
    Egocentric!
    ;-)

  21. Bob Phillips says:

    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.

  22. Dick Kusleika says:

    Well, Bob won’t have any use for this then. I modified @Hubisan’s ‘for’ script to handle For Each

    :*:for ::
    SendInput FOR{Space}
    Input, counter,I V T10,{Space}{Escape}
    if (ErrorLevel = "EndKey:Space")
        {
        if (counter = "each")
            {
            Input, eachctr, I V T10,{Space}{Escape}
            if (ErrorLevel = "EndKey:Space")
                {
                SendInput +{HOME}{DELETE}{Enter}Next %eachctr%{Up}For Each %eachctr%{Space}
                }
            }
        else
            {
            SendInput +{HOME}{DELETE}{Enter}Next %counter%{Up}For %counter%{Space}
            }
        }
    Return

    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.

  23. Dick Kusleika says:

    Another update to avoid problems when opening text files

    :*:for ::
    SendInput FOR{Space}
    Input, counter,I V T10,{Space}{Escape}
    if (ErrorLevel = "EndKey:Space")
        {
        if (counter = "each")
            {
            Input, eachctr, I V T10,{Space}{Escape}
            if (ErrorLevel = "EndKey:Space")
                {
                SendInput +{HOME}{DELETE}{Enter}Next %eachctr%{Up}For Each %eachctr%{Space}
                }
            }
        else if (counter = "Append" or counter = "Binary" or counter = "Input" or counter = "Output" or counter = "Random")
            {
            ;do nothing
            }
        else
            {
            SendInput +{HOME}{DELETE}{Enter}Next %counter%{Up}For %counter%{Space}
            }
        }
    Return
  24. Frank says:

    I would appreciate if someone could add code for automatically adding a closing bracket and moving the cursor between the two brackets.

  25. Dick Kusleika says:

    @Frank: Very lightly tested

    :*:(::(){left 1}
  26. Frank says:

    @Dick: unfortunately this only works for a standalone opening bracket, but not for the opening bracket of a function.

  27. Dick Kusleika says:

    @Frank: Just when I think I understand AHK, I realize I don’t.

    :*?:(::(){left 1}
  28. Dick Kusleika says:

    And to get intellisense to pop back up

    :*?:(::(){left 1}{Space}
  29. Chris Spicer says:

    @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.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: