Updating the For Next AutoHotkey in the VBE

Last month I posted about some AHK scripts I was starting to use to make the VBE a little less gross every day. There were some awesome comments. I took Hubisan’s comment and ran with it through a few iterations. First, let’s go to the video.

I use a program called CamStudio. For some reason it’s blurry for the first 30 seconds. I really need to get Techsmith’s Camtasia. But it gets the point across for now.

I’m seriously digging the AHK stuff. Here’s the script:

:*:for ::
;when you type for{space}, replace it with caps so you know you're in AHK mode
SendInput FOR{Space}
;wait for the next word and store it in counter
Input, counter,I V T10,{Space}{Escape}
;finish with ESC and you thwart AHK
;but finish with a space and more stuff happens
if (ErrorLevel = "EndKey:Space")
    {
    ;if the next word is each, it's a for each loop
    if (counter = "each")
        {
        ;wait for the next word and store it in eachctr
        Input, eachctr, I V T10,{Space}{Escape}
        if (ErrorLevel = "EndKey:Space")
            {
            ;Once you know eachctr, fill in the Next line and go back up to the For line
            SendInput +{HOME}{DELETE}{Enter}Next %eachctr%{Up}For Each %eachctr%{Space}
            }
        }
    ;if the next word is one of these, you're opening a text file
    else if (counter = "Append" or counter = "Binary" or counter = "Input" or counter = "Output" or counter = "Random")
        {
        ;get the next word - it really should only be 'As'
        Input, askeyword, I V T10,{Space}{Escape}
        if (ErrorLevel = "EndKey:Space")
            {
            if (askeyword = "As")
                {
                ;the word after 'As' is the file number
                Input, filenum, I V T10,{Enter}{Escape}
                if (ErrorLevel = "EndKey:Enter")
                    {
                    ;complete the close statement, because I always forget that.
                    SendInput {Enter}Close{Space}
                    ;you got to send this part raw because there may be a # in there and that's special
                    SendRaw %filenum%
                    SendInput {Up}
                    }
                }
            }
        }
    else
        {
        ;and finally if it's not all that special stuff, it's just a for next
        SendInput +{HOME}{DELETE}{Enter}Next %counter%{Up}For %counter%{Space}
        }
    }
Return

I put in comments so hopefully you can follow along. All I’ve done is copy Hubisan’s code, so if I took something nice and made it total crap it’s because I don’t know what I’m doing.

Bob Phillips made a good point in the last post about how he doesn’t prefer the automation. The automation gets in the way sometimes and typing the code slows things down so you can use your brain a little more. Good points, I thought, but I still like the automation. I can relate to the point that it gets in the way sometimes. In a previous iteration, I would type For i and it would put Next i, plus a blank line, plus a tab. That means when I’m done with the For statement, I have to arrow down. I don’t want to arrow down. I want to hit enter, then tab. So I made the automation fit the way I want to work and now I’m very happy with it.

12 Comments

  1. Jeff Weir says:

    Great video, but can you speak up next time…I could hardly hear you and in fact mostly filled in the audio blanks with my imagination. (Why you would sound a little like Penelope Cruz was a little puzzling, but it did bring two of my favourite passions together in the one blog post.)

    Hey, this looks really cool. I’ve got into the habit of appending my closing IF statements with an apostrophe and then adding the text of the matching opening IF statement, so I can keep track of exactly where each block ends, like so:

                                    End If 'If Not bIdentified Then
                                End If 'If lngFields = 1 Then
                            End If 'If Not bIdentified Then
                        End If 'If InStr(.Summary, "|") = 0 Then

    Needless to say this involves quite a bit of cutting and pasting. You think this app could help me with this? (Read: Man, wouldn’t it be cool if Dick had nothing better to do than spend a couple of hours confirming whether this tool is fit for my consumption) :-)

  2. Dick Kusleika says:

    I don’t have a microphone on my computer, so, while I was narrating the whole time, it didn’t make it into the video.

    First, if you’re IF blocks are that big, you should refactor your code. Now that the lecture is over, I can’t figure out how to do that. But we’ll see if stackoverflow knows.

    http://stackoverflow.com/questions/24761618/autohotkey-input-times-out-because-matchlist-isnt-matched

  3. Jeff Weir says:

    I often end up with lots of nested IFs…particularly when doing things to PivotFields efficiently. E.g. when syncing pivots up, I check if a pivotfield is:

    • Not a Values or Data field
    • Filtered, in which case I do something different
    • A Pagefield, and if so, whether it has EnableMultipleItems set to True or False, in which cases I do something different

    I don’t see why using lots of IF blocks that potentially get quite wide is wrong, and I don’t see any alternative that fundamentally changes things from either a readability or efficiency viewpoint. That said, when it comes to VBA, I’m new around here.

    I don’t know how I would refactor this code in any non-trivial manner, unless I either used GOTO statements or split it into a whole heap of small subs (which I think would detract hugely from readability). But I always love enlightenment, so if you see a way to cut down on IFs I’d certainly appreciate getting my head around alternatives. This code excerpt is from http://dailydoseofexcel.com/archives/2014/07/10/what-caused-that-pivottableupdate-episode-iv/

  4. Jeff Weir says:

    With this particular code, They are all If/Then/Else blocks. I’m trying to find out what PivotField (if any) got filtered. I have three separate tests – each of slightly increasing complexity. The first test doesn’t require much, and picks up what PivotField got filtered in probably 80% of cases. The 2nd test requires a little bit more processing, and probably picks up what PivotField got filtered in half the remaining cases. The last test requires something quite invasive, but will correctly identify those last remaining cases that the other tests missed.

    I could turn each case into a separate function, but it didn’t really make sense to me to do so, as it kind of felt like I’d be flattening the arrowhead pattern purely for the sake of it. Maybe it would be worth it…I think the resulting code would be easier to read. But it would be longer, and effectively I’d just be pushing those IFs into a calling routine.

  5. Dick Kusleika says:

    “split it into a whole heap of small subs”

    Yeah, that’s what I do. I don’t like my procedures to be bigger than one screen (although it happens), but I definitely don’t want my Ifs to be out of sight of my EndIfs. So I would take this

    If pf.DataRange Then
        If pf.VisibleItems Then
            If pf.Application Then
                'do stuff
            End If
        End If
    End If

    and turn it into

    If CanModify(pf) Then
        'do stuff
    End If

    and

    Public Function CanModify(ByRef pf As PivotField) As Boolean
       
        CanModify = pf.DataRange And pf.VisibleItems And pf.Application
       
    End Function

    Obviously that’s a made up, nonworking scenario. If you name CanModify correctly, you build intent into your code and you don’t need any comments – the function name tells you what you meant.

  6. Dick Kusleika says:

    I’m so close on the AHK script you want @Jeff.

    :*:if ::
    SendInput IF{Space}
    Input cond, I V T10 *,,then`n
    if (ErrorLevel = "Match")
        {
        SendInput % "`nEnd If '" SubStr(cond,1,-5)
        SendInput {Up}{Tab}
        }
    Return

    It looks for you to type Then{Enter} then it creates the End If 'condition for you. It turns

    If x = 1 Then

    into

    If x = 1 Then

    End If 'x = 1

    Here’s where it fails – and I don’t see a solution. Autocomplete. It turns

    If Application.ScreenUpdating Then

    into

    If Application.ScreenUpdating Then
       
    End If 'application.scre

    I stopped typing when I got to the first e in ScreenUpdating an hit Tab to autocomplete. But AHK doesn’t know that the VBE auto completed, so it thinks I typed If Application.Scre{Tab}Then{Enter}.

    Maybe it’s better than nothing though. Then I thought “Why am I typing ‘Then’ like a chump?”. I can just look for an Enter.

    :*:if ::
    SendInput IF{Space}
    Input cond, I V T10 *,,`n
    if (ErrorLevel = "Match")
        {
        SendInput {Up}{End} Then{Enter 2}End If{Space}'%cond%{Up 2}{Tab}
        }
    Return

    Now if I type

    If x = 10

    and press enter, I get the same result. It still has the automcomplete problem though.

  7. Ken Puls says:

    Love this. I’ve re-written my Select Case portion based on your latest stuff here:

    :*:select case ::
     ;when you type select case{space}, replace it with caps so you know you're in AHK mode
     SendInput SELECT CASE{Space}

     ;wait for the next word and store it in counter
     Input, eachctr,I V T10,{Enter}{Escape}

     ;finish with ESC and you thwart AHK, but finish with a space or enter and more stuff happens
     if (ErrorLevel = "EndKey:Enter")
        {
            ;Once you know eachctr, fill in the Next line and go back up to the For line
            SendInput {Enter}{Enter}End Select{Up}{Tab}Case Else{Enter}{Up 2}{Tab}Case Is ={Space}
            }
     Return
  8. Dick Kusleika says:

    Hmmm, I think we’ll need a repository of these. Blog comments probably aren’t the best database, but it will do for now.

  9. Jeff Weir says:

    @Dick…Awesome! Am on holiday now, but when I get back I’ll download and take this for a spin.

  10. Jeff Weir says:

    @snb: I’ve moved your comment to the previous post, so we don’t hijack Dick’s thread.

  11. […] et al tell me that my over-use of the arrow anti-patten is […]

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

You must be logged in to post a comment.

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