VBA to Keyboard Maestro Macro

[skillet]

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

@ccstone

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

That code won’t even compile.

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.

@ccstone

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.

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

Clipboard Formatting - MS Excel

Hey Skillet, Okay, the picture makes it easier to understand the problem. This task is scriptable. Here are some hints: tell application "Microsoft Excel" tell active window # Just tests. set rangeSelection to range selection set _color to color of font object of selection set _coler to reverse of _color # Reverse selection. set v1 to value of selection set newValue to reverse of v1 set value of selection to newValue set color of font object of sele…

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

@ccstone

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

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.

@ccstone

You did note that document is from 2004 – yes?

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