Excel for Mac, VBA OnKey macros, and MacOS Monterey

You know that feeling when a manufacturer pulls the rug from under you? When a feature (OK, a workaround, even a hack) you’ve been relying on for years suddenly stops working because of an “upgrade”? Welcome to the world of the VBA developer in an Apple environment.

Excel VBA macros can be launched by keystroke shortcuts such as Ctrl+q, Ctrl+Shift+q, Ctrl+Alt+q. For example, Application.OnKey “^%q”, “MyMacro” is launched by Ctrl+Alt+q.

That works fine in Windows. But Excel for Apple Mac VBA does not detect the Command or Option (same as Alt) keys. There used to be a workaround, posted by Ron de Bruin on macexcel.com which used the Cocoa library in Python to return the modifier keys pressed. My version is

 ScriptToRun = "do shell script ""/usr/bin/python -c 'import Cocoa; print Cocoa.NSEvent.modifierFlags()' "" "
 
 flags = MacScript( ScriptToRun  ) 

'https://eastmanreference.com/complete-list-of-applescript-key-codes
'Key      bit value
'CapsLock 16 65536
'Shift    17 131072
'Ctrl     18 262144
'Option/Alt 19 524288
'Command   20 1048576

BUT in the latest MacOS upgrade, Monterey, Python is no longer built in. Apple has officially deprecated Python 2.7 in macOS Monterey 12.3. “The company is advising its developers to use an alternative programming language instead, such as Python 3, which, however, does not come preinstalled on macOS.”

I posted a question “Is there a newer/better way to detect what modifier keys are being pressed?” at https://macscripter.net/viewtopic.php?pid=209786 and got a very helpful reply from Nigel Garvey.

Here’s the MacScript version. Multiple lines are separated by both return & linefeed characters.

scpt = "use framework ""AppKit""" & vbCrLf
scpt = scpt & "return current application's class ""NSEvent""'s modifierFlags()"
Debug.Print MacScript(scpt)

However, in the future we may have to use the more awkward AppleScript function. The MacScript function has been deprecated, therefore it is no longer supported (although at the time of writing it is still present in Office for Mac VBA). For more information, see this Stack Overflow article.

The script needs to be put in a .scpt file in a hidden folder in the user’s home folder: /Users/username/Library/Application Scripts/com.microsoft.Excel. The file looks like the following; from testing I found that of the “use” statements, only the AppKit line is essential.

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use framework "AppKit"
use scripting additions

on modifierKeysPressed()
   return current application's class "NSEvent"'s modifierFlags()
end modifierKeysPressed

It is invoked by the AppleScriptTask function, eg AppleScriptTask("modifierKeysPressed.scpt", "modifierKeysPressed", ""). This is unfortunately quite slow, from 0.7 seconds to several seconds, I found when testing.