Here is another AppleScript version of a
prior vba script. This one creates a back of the current
workbook. It copies the last saved version of the current workbook to
a Backup subdirectory below the directory in which that file was saved.
It adds a counter (ie, .001) before the .xls(x) extension.
Rather than merging cells to center headers, I prefer to have text centered
across selection. This avoids problems with deleting and filling columns that
are cosed by merged cells. The following sets up a command to toggle centering
across columns.
Open up the AppleScript Editor, paste the following code and save it as
/Users/<your-username>/Documents/Microsoft User Data/Excel Script Menu
Items/AlignCentered\sca.scpt. The \sca in the filename creates a
keyboard shortcut control-shift-a.
-- Align selected cells across selection
-- Copyright under GPL by Mark Grimes
-- Saving with '\sca' in the filename creates Shortcut: Crtl+Shift+a
tell application "Microsoft Excel"
--activate
tell range (get address selection) of active sheet
if (get count columns) > 1 or (get count rows) > 1 then
if (get horizontal alignment) is horizontal align center across selection then
set horizontal alignment to horizontal align general
else
set horizontal alignment to horizontal align center across selection
end if
else
if (get horizontal alignment) is horizontal align center then
set horizontal alignment to horizontal align general
else
set horizontal alignment to horizontal align center
end if
end if
end tell
end tellI recently switched to a Mac and really miss my auto color cells VBA script for Excel. After quite a bit of digging (and trial-and-error) I managed to recreate the functionality using AppleScript. It is pretty slow, but it works!
Just like the VBA version, this automatically color codes cells to help
identify inputs, formulas, etc. For example, cells that contain only numbers
are colored blue, all formulas black, references to other workbooks are green
and cells that include the =OFFSET() function (what I use for setting up
different scenarios) are rust.
Open up the AppleScript Editor, paste the following code and save it as
/Users/<your-username>/Documents/Microsoft User Data/Excel Script Menu
Items/AutoColorCells\scc.scpt. The \scc in the filename creates a
keyboard shortcut control-shift-c.
I finally created an add-in for Excel that includes many of the tools that I use all the time and have outlined on this site. The add-in will create a new menu in Excel and setup a number of shortcuts. Here are some of the more useful ones:
When I feel like getting fancy, it can be nice to include a bulleted list in an Excel sheet to describe assumptions, etc. This is actually pretty easy to do, but requires adding some odd characters. This macro will add a character and change the font of a cell to create a bullet. If you run this macro on a cell which already contains a bullet, an arrow shaped sub-bullet is inserted instead.
' Toggles a bullet and an arrow
' Copyright under GPL by Mark Grimes
' Keyboard Shortcut: Crtl+Shift+B
'
Sub mgBullet()
If ActiveCell.Formula = "l" Then
Selection.Font.Name = "Wingdings"
ActiveCell.FormulaR1C1 = "bullet"
' Replace the text bullet with the bullet symbole from Wingdings
' Found that others don't have wingdings 3, it's sub-bullet was better
' Selection.Font.Name = "Wingdings 3"
' ActiveCell.FormulaR1C1 = "}"
Else
Selection.Font.Name = "Wingdings"
ActiveCell.FormulaR1C1 = "l"
End If
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = xlHorizontal
End With
End SubThis is one of my favorites. It saves a copy of the current file in the ‘Backup’ directory if one exists under the directory in which the file is currently saved. It saves the files with an incrementing two digit number after the filename (before the .xls extension). A cap of 50 backups is imposed just to keep from taking up too much disk space (my models tend to be BIG).
' Save a copy of the current file.
' Copyright under GPL by Mark Grimes
' Keyboard Shortcut: Crtl+Shift+S
' Will save in the "Backup" subdirectory if it exists.
' Will attempt to add an index number upto 50.
'
Sub mgSaveBackup()
p0$ = ActiveWorkbook.Path
If Dir(p0$ & "\Backup", vbDirectory) <> "" Then
p$ = p0$ & "\Backup"
End If
n0$ = ActiveWorkbook.Name
If Right(n0$, 4) <> ".xls" And Right(n0$, 4) <> ".XLS" Then
MsgBox "File must be a previously saved '.xls' file."
End
End If
n$ = Left(n0$, Len(n0$) - 4)
i = 0
Do
i = i + 1
Loop Until (Dir(p$ & "\" & n$ & "." & Application.Text(i, "00") & ".xls") = "") Or (i > 50)
If i > 50 Then
MsgBox "No more than 50 backup's can be made."
End
End If
response = MsgBox("File to be backed-up as:" & Chr(10) _
& p$ & "\" & n$ & "." & Application.Text(i, "00") & ".xls", vbOKCancel)
If response = vbOK Then
'FileCopy p0$ & "\" & n0$, p$ & "\" & n$ & "." & i & ".xls"
ActiveWorkbook.SaveCopyAs p$ & "\" & n$ & "." & Application.Text(i, "00") & ".xls"
Else
MsgBox "Backup aborted!"
End If
End SubWhen formatting a Excel sheet underlining or overlining (which appears as if you underlined the cell above) a cell often looks much better than just underlining the contents of the cell (ctrl-u). This macro will toggle the under/overlines for all the selected sells.
' Toggles Underlines
' [% coypright %]
' Keyboard Shortcur: Crtl+Shift+U
'
Sub mgSetUnderline()
If Selection.Borders(xlBottom).LineStyle = xlNone Then
With Selection.Borders(xlBottom)
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
Selection.Borders(xlBottom).LineStyle = xlNone
End If
End Sub
' Toggles Overlines
' Copyright under GPL by Mark Grimes
' Keyboard Shortcur: Crtl+Shift+O
'
Sub mgSetAnOverline()
If Selection.Borders(xlTop).LineStyle = xlNone Then
With Selection.Borders(xlTop)
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
Selection.Borders(xlTop).LineStyle = xlNone
End If
End Sub
I often like to have narrow empty columns between data columns just to make things look nice (cell underlining looks better that way in my opinion). This macro will prompt you for a number of columns per group and then it selects one column per group for the currently selected range (i.e. selecting A5:G5, running the macro and entering 2 would result in columns B, D, and F being selected). Then you can quickly resize those columns to make everything look real pretty.
' Select every other column
' Copyright under GPL by Mark Grimes
'
Sub mgSelectEOther()
Dim i, mult As Integer
Dim r, cst As String
mult = Application.InputBox(prompt:="Select every x columns:", default:=2, Type:=1)
r = ""
i = 0
For Each c In Selection
i = i + 1
If i Mod mult = 0 Then
If (c.Column > 26) Then
' tx = c.Column & ": A=" & Asc("A") & ", " & Int(c.Column / 26) & ", " & (c.Column Mod 26)
' MsgBox tx
cst = Chr(Asc("A") - 1 + Int(c.Column / 26)) & Chr(Asc("A") + (c.Column Mod 26) - 1)
Else
cst = Chr(Asc("A") + c.Column - 1)
End If
r = r & "," & cst & ":" & cst
End If
Next
r = Right(r, Len(r) - 1)
' MsgBox r
ActiveSheet.Range(r).Select
End SubHere are two routines that pull the formula from a note and put the formula in a note. I had a very specific need for this, but I can’t recall why now.
' Creates a formula from the Note
' Copyright under GPL by Mark Grimes
Sub mgNote2Formula()
For Each c In Selection.Cells
c.Formula = c.NoteText
Next
End Sub
'
' Put the formula in the note
' Copyright under GPL by Mark Grimes
Sub mgFormulaToNote()
For Each c In Selection.Cells
c.NoteText (c.Formula)
Next
End SubThis routine combines the selected cells into one long string in the current cell.
' Combine cells
' Copyright under GPL by Mark Grimes
Sub mgCombineCells()
t = ""
For Each c In Selection.Cells
t = t & Trim(c.Formula) & " "
Next
t = Left(t, Len(t) - 1)
ActiveCell.Formula = t
End Sub