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
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.
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 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.
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.
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).
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.
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.