I can’t figure out why Excel freezes when calling this particular VBA. I have VBA saved in the Excel Personal Module so I can call code from through AppleScript in any Excel document and most of them work just fine but this particular VBA works perfect when run manually in Excel 14 and 16 (option+F8) but don’t run when called through AppleScript.
On one computer I have tried it on if I quit Excel and then press escape in the save dialog it then runs but not working on my laptop both on the same OS 10.13.4
(*
tell application "Microsoft Excel"
run VB macro "FillEmptyCellsMoveSelectionUp"
end tell
*)
tell application "Microsoft Excel"
run VB macro "'Personal Macro Workbook'!FillEmptyCellsMoveSelectionUp"
end tell
Here’s the VBA code saved in the personal Excel document.
Sub FillEmptyCellsMoveSelectionUp()
Dim i As Long, k As Long
Application.ScreenUpdating = False
With Selection
For i = 1 To .Cells.Count
If Len(.Cells(i).Value) > 0 Then
k = k + 1
If k < i Then
.Cells(i).Copy Destination:=.Cells(k)
.Cells(i).ClearContents
End If
End If
Next i
End With
End Sub
FWIW here is how to save to the personal Excel book
https://support.office.com/en-us/article/Copy-your-macros-to-a-Personal-Macro-Workbook-AA439B90-F836-4381-97F0-6E4C3F5EE566
1 On the Developer tab, click Record Macro.
2 In the Record Macro dialog box, type a meaningful name for the macro in the Macro name box. Make sure you don’t use any spaces in the name.
3 In the Store macro in box, select Personal Macro Workbook.
4 Click OK.
5 Perform the actions that you want to record.
6 On the Developer tab, click Stop Recording.
7 Save the changes, then close the workbook, and finally close Excel. A message appears that prompts you to save the changes that you made to the Personal Macro Workbook.
8 Click Save to save the workbook.
VBA code like this below works just fine being called from AppleScript so I am not sure what is different.
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