Updating the For Next AutoHotkey in the VBE

Posted on July 14, 2014January 19, 2017 by Dick Kusleika

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

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

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

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

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) :-)

Dick Kusleika says:

July 15, 2014 at 10:03 am

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
Jeff Weir says:

July 15, 2014 at 7:55 pm
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/
Doug Glancy says:

July 15, 2014 at 8:45 pm

Jeff, here’s germane and nearby: http://dailydoseofexcel.com/archives/2011/12/16/procedure-flow-and-raising-errors/
Jeff Weir says:

July 15, 2014 at 11:41 pm

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.

“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

If pf.DataRange Then

If pf.VisibleItems Then

If pf.Application Then

'do stuff

End If

and turn it into

If CanModify(pf) Then
    'do stuff
End If

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

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.

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

:*: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

1	If x = 1 Then

into

If x = 1 Then

End If 'x = 1

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

1	If Application.ScreenUpdating Then

into

If Application.ScreenUpdating Then
    
End If 'application.scre

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

:*: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

If x = 10

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

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

:*: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

Dick Kusleika says:

July 16, 2014 at 2:14 pm

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

July 22, 2014 at 4:48 pm

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

July 22, 2014 at 5:23 pm

@snb: I’ve moved your comment to the previous post, so we don’t hijack Dick’s thread.
Pingback: Daily Dose of Excel » Blog Archive » Broken Arrow
Eric P says:

January 18, 2017 at 12:33 pm

Can you make it so that it doesn’t trigger if I am just writing a comment?
Dick Kusleika says:

January 19, 2017 at 10:12 am

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.