CODEWITHSUNDEEP
vbaexcelexcel-2010autohotkey
enderland
enderland

Reputation: 14135

How to call VBA macro from AHK in Excel when editing a cell?

I have an autohotkey script which calls an Excel macro. Basically I have a universal shortcut to add a task to my custom GTD application in Excel

My AHK code is as follows:

RunMSExcelMacro(MacroName, args) {      ; for AHK_L
    oExcel := ComObjActive("Excel.Application")
   ; oExcel.Workbooks("gtd_active.xlsm").Run(MacroName,args)
    oExcel.Run("'gtd_active.xlsm'!"+MacroName,args)
    ;oExcel.Run(MacroName,args)
}

#Space::
InputBox, newTask, Add New Task to GTD, What is the new task?, , 380, 130 
if (newtask = "" or ErrorLevel){ ;if blank or "cancel" pressed (ErrorLevel = 1) don't do anything
return
}
else
{RunMSExcelMacro("addFromAHK", newTask) 
}
return

The method in Excel is as follows:

'this script allows AutoHotKey to add items to the GTD list
'without having to even access Excel - wtf hax :-)
Sub addFromAHK(ByRef newTask As String)

    Dim myCell As Range
    'I don't like hardcoding this but I cant be bothered to
    'make this reference the constant right now
    Set myCell = MasterList.Range("C50000").End(xlUp)

    'ugh such a hack. No easy way to get last cell without defiltering
    Do Until IsEmpty(myCell) = True
        Set myCell = myCell.Offset(1, 0)
    Loop

    'save the task
    myCell.value = newTask


End Sub

Unfortunately, if I am currently editing a cell in Excel I cannot call a macro, so the global shortcut fails. Note this fails before I am able to call the Excel macro.

Is there an easy way to change the AHK code to basically "exit cell editing" if I'm doing that? I don't want to write a huge amount of code to do this but I'm really not sure the best approach here.

Upvotes: 0

Views: 3056

Answers (1)

MCL
MCL

Reputation: 4065

The first time I made use of AHK_L's try/catch (yay!). Check out this code example:

RunMyMacro() {
    oExcel := ComObjActive("Excel.Application")
    try {
        oExcel.Run("'MyFile.xlsm'!TestSub")
    } catch e {
        ControlSend, , {Enter}, ahk_class XLMAIN
        Sleep, 200
        oExcel.Run("'MyFile.xlsm'!TestSub")
    }
}

It's pretty basic, so you'll have to do the fine tuning yourself.
If there's an error calling the macro, we outright assume that that's because we are editing a cell. If you are rather meticulous, you could check the exception's message (e.Message) for an error code. On my machine the message was 0x80010001 Some German BlaBla.
To exit cell editing, we simply send Enter to the Excel Window. If there's multiple Excel Windows, you have to restrict the window title like MyFile.xlsm ahk_class XLMAIN.
After the Sleep (you may want to play around with the exact timeout), we call our macro again.

Upvotes: 2

Related Questions