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:

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.

14 thoughts on “Updating the For Next AutoHotkey in the VBE

  1. 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:

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

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

  4. “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

    and turn it into


    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.

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

    It looks for you to type

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


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


    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.

    Now if I type

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

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

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

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

  9. I tried with all my might to suspend this when writing a comment, but I couldn’t do it. It will probably take someone better at AHK than me to get that done.

    It happens a lot to me and I’m trying to train myself to hit ESC before it does. But I write With, For, and Then in my comments a lot and I get bit a lot.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.