Run Excel VBA macro using keyboard shortcut

[layo]

I'm trying to run a macro called 'CopyVisibleRanges' in Excel but there is a problem. Anyone see what error is in attached?

Run copy visible ranges macro in Excel.kmmacros (3.0 KB)

[ComplexPoint]

There's a connection between VBScript and VBA ?

There's no obvious sign of an interface to VBA in the AppleScript scripting dictionary for Excel. Not sure how you derived that incantation.

( Possibly more fruitful to experiment with launching an Excel VBA macro by automating menu path selections from Keyboard Maestro )

[roosterboy]

There is no command run VBScript in Excel's dictionary.

The command is run VB macro.

So your code should be:

tell application "Microsoft Excel"
    activate
    run VB macro "CopyVisibleRanges"
end tell

Depending on what exactly it is that the CopyVisibleRanges macro does, you could possibly do the same thing in AppleScript alone, without having to invoke a VB macro.

[layo]

I cannot make the macro work, using above Keyboard Maestro macro.

To run the macro, I have to go to Tools > Macro > Macros... in Excel. This brings up attached box and I have to click "PERSONAL.XLSB!Sheet1.CopyVisibleRanges".

Know how to make macro work using Keyboard Maestro macro?

[NaOH]

I'm not in front of a Mac with Office to check this, but have you clicked the Options button as shown in your picture? Microsoft software is often good at allowing users to customize things like keyboard shortcuts within their applications, and that's where such an option is likely located.

[MartinPacker]

My automation of Excel - which is quite extensive - has been either AppleScript direct manipulation or, less reliably, keystroke / menu automation.

I've not yet managed to run VBA macros; It does occur to me that injection into a spreadsheet cell might have some value here.

[roosterboy]

Did you include PERSONAL.XLSB!Sheet1. as part of the name of your macro to run?

So:

@roosterboy

tell application "Microsoft Excel"
    activate
    run VB macro "PERSONAL.XLSB!Sheet1.CopyVisibleRanges"
end tell

Worked for me from an AppleScript in Script Debugger and from a KM Execute AppleScript action.

[layo]

@roosterboy

tell application "Microsoft Excel"
    activate
    run VB macro "PERSONAL.XLSB!Sheet1.CopyVisibleRanges"
end tell

That worked thanks

[MartinPacker]

Is this .xslb file necessarily the one being operated on? Or is it a helper spreadsheet with generally useful macros in?

I can see the latter being really useful - and worth developing code in. The former not so much.

(I'm highly motivated to create a helper .xslb file for my "production" life.)

[roosterboy]

PERSONAL.XLSB is the default save location when you create macros. It's a global file whose macros are available to all Excel files.

[MartinPacker]

Thank you. I wouldn't want to try to inject VBA code into every spreadsheet I wanted to use it with. This is much better.

[skillet]

@roosterboy

There is no command run VBScript in Excel's dictionary.

The command is run VB macro.

So your code should be:

tell application "Microsoft Excel"
    activate
    run VB macro "CopyVisibleRanges"
end tell

Thank you I have been at this for a while now trying to figure out how to call this and my workaround was awful compared to this.