Moving from Microsoft Office VBA to AppleScript:
MacTech's Guide to Making the Transition

Page Prev and Page Next buttons at bottom of the page.

April, 2007
Page 68

Font Format

Font formats are set using the cell‘s Font object. You can apply multiple properties at once:

   With ActiveSheet.Range("A1").Font

      .Name = "Verdana"

      .FontStyle = "Bold"

      .Size = 12

      .Strikethrough = False

      .Superscript = False

      .Subscript = False

      .OutlineFont = False

      .Shadow = False

      .Underline = xlUnderlineStyleDouble

      .ColorIndex = xlAutomatic

   End With

In AppleScript:

tell application "Microsoft Excel"

     tell font object of range "A1" of active sheet

          set {name, font style, font size, strikethrough, superscript, subscript, ¬

              outline font, shadow, underline, font color index} to {"Verdana", "Bold", ¬

              12, false, false, false, false, false, underline style double, color index automatic}

     end tell

end tell

This time I chose to set all properties at once in a list. It is easier, of course, to keep track of which property is set to which value if you do each on a separate line.

The color index property refers to the 56 colors available in the color palette. That's the same 56 colors as in the palette available in Format/Cells…/Font/Color, rather than the Formatting Palette and Toolbar's reduced palette of 40 colors. But the colors are in a completely different order – the "default palette" used by VBA and AppleScript must date back quite a few years. The correct palette layout, with identifying index numbers, is in the Excel AppleScript Reference under Text Suite/Classes/Font.

In addition to the eight custom colors that you can set using the number format property, you can use the font color index property to set the font color:

      ActiveSheet.Range("A1").Font.ColorIndex = 3   'Default palette red

In AppleScript:

     set font color index of font object of range "A1" of active sheet to 3

Take a good look at the color index enumeration for the font color index property of font class (Text Suite). It gives three so-called constants as available: color index automatic (that's generally black), color index none, and something called 'a color index integer' which will compile and run without error, but turns any existing color black, so it must be defaulting to automatic.

This is "covering" for what ought really to be another 56 constants – namely the numbers 1 through 56. In VBA, if you check the Object Browser you'll see only xlAutomatic and xlNone listed, which have (as all xl and vb constants do) their own numeric version (large negative numbers). But numbers 1-56 have also been reserved. Only the VBA Help tells you that. In AppleScript, some sort of reference to the numbers needed to be given but that "a color index integer" entry is just a dummy, or bogus, constant. It's a description masquerading as a constant. I suggest a reason for this at the end of this Excel chapter. An actual short description, if not "1-56" ought to be listed instead. But the Excel AppleScript Reference, to its credit, does give a full explanation.

You can set a "Microsoft" RGB value for the color, but Excel will choose the “closest” color available in the palette. It's really quite inadequate, but at least gets pure red, green and blue all right:

      ActiveSheet.Range("A1").Font.Color = RGB(255, 0, 0)

becomes:

     set color of font object of range "A1" of active sheet to {255, 0, 0}

See the PowerPoint chapter for a full discussion of Microsoft's RGB vs. AppleScript RGB.

The Color Palette