VBA to Keyboard Maestro Macro

skillet · April 6, 2018, 3:03pm

Continuing the discussion from Help with a VERY SIMPLE macro:

I have only used VBA in Excel a little but I have some basic macros that I would like to keep in Keyboard Maestro rather than attached to a single computer or spreadsheet. Is there a way to have VBA code in Excel be saved in Keyboard Maestro to run so I can use this across multiple comuters?

Sub MoveSelectionUp()
Selection.Offset(-1, 0).Select
End Sub

Sub MoveSelectionDown()
Selection.Offset(1, 0).Select
End Sub


Sub MoveSelectionLeft()
Selection.Offset(0, -1).Select
End Sub

Sub MoveSelectionRight()
Selection.Offset(0, 1).Select
End Sub

and

Sub MoveUp()
    Dim i As Long, k As Long
    
    With Selection
        For i = 1 To .Cells.Count
            If Len(.Cells(i).Value) > 0 Then
                k = k + 1
                .Cells(i).Cut Destination:=.Cells(k)
            End If
        Next i
    End With
End Sub

This link tells me I can do something like this but it doesn’t work.
https://wordmvp.com/Mac/Applescript-VBA.html

tell application "Microsoft Word"
            activate
            do Visual Basic "Selection.InsertAfter Format(Date, \"d mmmm, yyyy\")
    Selection.Collapse wdCollapseEnd
            "
end tell

Trying to make sense of all the info at this website.
https://www.rondebruin.nl/mac/applescripttask.htm

JMichaelTX · April 7, 2018, 2:01am

I don't think do Visual Basic has been supported since Word 2004.

However, looking at Word 2011, there are at least 384 commands supported in AppleScript. So AppleScript provides a very rich scripting environment for Word 2011. (can't speak to latest ver of Word 2016).

Also there is a run VB macro macro name text command that will let you run any existing VBA macro in the Word document or template.

So, the choices I see are:

Convert all VBA macros to AppleScript
Make sure that the VBA macros of interest are in the document or template on the target machines.

You decide which is easiest. Could be distribution of a standard Word Normal template with the VBA macros is a very competitive choice.

skillet · April 9, 2018, 8:43pm

Thank you Michael.

Word and Excel might behave different on how they respond to macros but both are very helpful to me to do.

Good to know.

Ideally I would like to do number one (convert all VBA to AppleScript since it seems to run faster) but there are things I have not been able to do or find online that are in AppleScript so I am left to trigger the VBA submacro from Keyboard Maestro. Comtion (Command+Option (⌥)) + alpha keys seem to be the only option to assign a shortcut which is not good and several of them are already used.

Any ideas why this doesn't trigger the macro in Excel?

tell application "Microsoft Excel"
   do Visual Basic "MoveSelectedContentsUp"
end tell

I pulled this from here though for Word seems like it should work for Excel too.
https://wordmvp.com/Mac/Applescript-VBA.html

JMichaelTX · April 9, 2018, 8:54pm

Because of the reason I stated above:

skillet · April 9, 2018, 9:09pm

Sorry, step two made me hopeful that Keyboard Maestro could invoke a macro somehow. Keyboard Maestro has no way to invoke a macro correct?

JMichaelTX · April 9, 2018, 9:40pm

You can use a Execute AppleScript Action.

skillet · April 9, 2018, 9:45pm

I am sorry I’ll keep searching the Internet one this one, I’m a bit slow and don’t follow how to use an AppleScript to trigger a VBA macro but I think you have already said you can’t, or maybe you just stated you can’t use “run do Visual Basic” but there might be another way to trigger a VBA submacro that is in a spreadsheet or document.

JMichaelTX · April 9, 2018, 11:26pm

Easy:

tell application "Microsoft Excel"
  run VB macro macro name "YourVBAMacroName"
end tell

This is totally different from

tell application "Microsoft Excel"
  do Visual Basic "MoveSelectedContentsUp"
end tell

Which uses the old, deprecated, "do Visual Basic" command.

ccstone · April 11, 2018, 3:41am

Hey @skillet,

This is another reason that all AppleScripting should be done in an AppleScript editor.

That code won't even compile.

If you drop the Excel app onto Script Editor.app or Script Debugger you'll get a searchable AppleScript dictionary.

If you search for “do Visual Basic” you won't find anything, because Micro$loth in their infinite wisdom removed that ability from Office versions later than 2004.

So – your question is something you can answer for yourself with a minimal amount of work.

Applescript Editor.app is what comes stock with macOS.

Script Debugger (a commercial AppleScript editor) is incomprehensibly better but costs $99.99 U.S. (and is worth every penny in my opinion).

Script Debugger's demo is full-featured for a fixed time, but not NOW reverts to Lite mode when the demo period expires. Even in Lite mode it is far superior to the Applescript Editor.app.

You did note that document is from 2004 – yes?

Things change...

-Chris

skillet · April 11, 2018, 7:10pm

JMichaelTX:

Easy:
tell application "Microsoft Excel"
  run VB macro macro name "YourVBAMacroName"
end tell
This is totally different from
tell application "Microsoft Excel"
  do Visual Basic "MoveSelectedContentsUp"
end tell
Which uses the old, deprecated, “do Visual Basic” command.

Wow I had no idea, I haven't really used Visual Basic other than copying others people's code when I couldn't find an alternative way to do something in AppleScript.

It took me a minute to figure out why it wasn't running, I didn't realize I needed to take out "Macro Name" duh!

tell application "Microsoft Excel"
  run VB macro "MoveSelectedContentsUp"
end tell

Thank you for this, that makes things so much simpler. I was having to assign keyboard shortcuts to several macros and running out of the few choices that I have since you can only use Comtion (Command+Option (⌥)) + alphabet letter only. Now this works across machines with the same workbook.

I actually had and knew it wouldn't compile I just couldn't figure out with the dictionary search the right syntax to make it compile.

Not that it matters much anymore but I hadn't started any scripting until after this. So I understand the difference would you just copy Visual Basic code after you enter "do Visual basic" into AppleScript Editor and it would work? You didn't need to have it attached to a spreadsheet or Word document?

So something like this?

tell application "Microsoft Excel"
  do Visual Basic "MoveSelectedContentsUp"
  
  Sub MoveSelectedContentsUp()
    Dim Rng As Range, UnusedRow As Long
    UnusedRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious, , , False).Row + 1
    If Selection.Row > 1 Then
      Selection(1).Offset(-1).Resize(, Selection.Columns.Count).Copy Cells(UnusedRow, "A")
      Selection.Copy Selection(1).Offset(-1)
      Cells(UnusedRow, "A").Resize(, Selection.Columns.Count).Copy Selection.Offset(Selection.Rows.Count - 1)(1)
      Selection.Offset(-1).Resize(, Selection.Columns.Count).Select
      Cells(UnusedRow, "A").Resize(, Selection.Columns.Count).Clear
    End If
    Selection.Offset(0, 0).Select
  End Sub
  
end tell

@ccstone you helped me do a similar thing with a single selected row to move up and down

VBA is the only way I have been able to find to do this with multiple rows.

Yes I agree, I purchased Script Debugger 4.5 for $149 and then SD5 for $79 upgrade, never made the leap to 6 and now the recent version 7 though I need to at least support the program development. I am not a power user but wish I were but even at my basic level it has helped a lot over AppleScript Editor.

Yes, I did know it was old but unfortunately, I didn't know what exactly to search for and that was the closest thing I could find after looking all over on forums and internet posts. Like searching "make things go faster on my Mac" and hoping to find Keyboard Maestro (though hopefully I was a little closer than that in my search).

JMichaelTX · April 11, 2018, 10:44pm

Well, actually it depends on if you are calling a Microsoft Word or Excel VBA macro. I made a mistake in assuming they were the some. Foolish me, expecting Microsoft to be consistent.

Like Chris says, we all need to use the scripting dictionary (SDEF) to verify stuff. So, from SD7:

MS Excel

MS Word

So, to satisfy my own curiosity, I just created and tested two VBA Macros:

MS Excel


tell application "Microsoft Excel"
  activate

  --- FORMAT:
  -- Do NOT use "macro name" as part of the command
  -- But, if the macro is in your Personal Macro Workbook (available to all documents,
  -- then you must use it as a prefix in single quotes:

  run VB macro "'Personal Macro Workbook'!Test_Macro"
end tell

MS Word

tell application "Microsoft Word"
  activate
  --- FORMAT:
  -- You must use "macro name" followed by the name in quotes
  -- You must prefix the name with the Module name, "KM" in this case
  
  run VB macro macro name "KM.Test_Macro"
end tell

If you are going to do any AppleScript work, then you should immediately get Script Debugger. It's free for 30 days, then the SD Lite is free forever.

ccstone · April 13, 2018, 12:31pm

Hey @skillet,

You've understood that there is no longer a do Visual Basic command in either Word or Excel – yes?

You can NO longer place Visual Basic code in an AppleScript and run it.

You can only CALL existing VB macros in Word or Excel from AppleScript.

It's rubbish, but that's what Microsoft did – and I'm unaware of any substantive change in Office 2016.

-Chris

skillet · April 13, 2018, 10:30pm

That's a bit crazy the syntax isn't the same, I guess they have a lot to keep straight and that wasn't one of them or it slipped through the cracks.

Yes, thank you.

Okay that completely cleared it up for me, that is neat that you could place VB code right in AppleScript and run it with do Visual Basic. At that point the AppleScript is not necessary other than the initial "do Visual Basic"

That would be so helpful if that were still possible! Thanks for confirming and clearing things up for me.

VBA to Keyboard Maestro Macro

MS Excel

MS Word

MS Excel

MS Word

Options