Categories
Miscellanea

Edit Excel Formulas with AutoHotKey

At work I’m stuck using a Windows PC, and I often need Excel.

I really dislike the automation landscape on Windows (nothing comes even close to what we have on the Mac, there’s no Alfred, Hazel, Keyboard Maestro, Better Touch Tool, just to name a few), and the only thing I found that is useful is AutoHotKey. It is much harder to use than Keyboard Maestro, but it is quite powerful.

Today I had to change the formulas in a many Excel cells to add an “outer” IFERROR, so I created an AutoHotKey macro to help:

#<::
	Send {F2}
	Send {Home}
	Send {Del}
	SendInput, =IFERROR(
	Send {End}
	Send, ,"")
	Send {Enter}

	Return

The problem is that at the beginning of time Microsoft decided it was best to localize function names, so the English IFERROR becomes the Italian SE.ERRORE, notice the period between the two words.

If I just replaced SendInput, =IFERROR( with SendInput, =SE.ERRORE( I would get SE,ERRORE in my cell (notice the comma), because of regional seettings: we use commas as decimal separators, and periods as thoustand separators (that also brings the funny thing of using semicolons as arguement separator in Excel functions).

The only way I could find to coerce Excel to accept a period and not to translate it to a comma, was to send a unicode string (really). So the final script, for the Italian locale, becomes:

#<::
	Send {F2}
	Send {Home}
	Send {Del}
	SendInput, =SE{U+002E}ERRORE(
	Send {End}
	Send, `;"")
	Send {Enter}

	Return