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 82

                   end tell

              end tell

          end tell

     end tell

end tell

There are two things the script cannot do that the VBA macro can. There is no transparency property of border in AppleScript, so you can't set it. As it happens, the macro sets the transparency to 0, i.e., opaque, which is the default anyway, so you won't see any difference.

The fore color property of the chart fill format class, which is the class of the chart fill format object property of both the plot area and the legend of the chart (i.e., the fill color for the cart and the legend box to the right of it), is read-only in AppleScript. You cannot set it directly: and for once there is no alternate color index property that can be set instead either.

However there is a workaround: you can set the foreground color scheme index. There is no table of indexed colors for these things (and I can't find any relation between the color scheme dropdown in the UI's Formatting Palette to the numbers I tested, although there may be one.) By trial and error, I discovered that foreground color scheme 23 is the same color, and setting its transparency to 0.8 instead of 0.5 matches the color and shade made by the macro – RGB(150, 200, 255) – exactly.

I don't know how many of these scheme indices there are – I made it to 100 without erroring, so you will, with some effort, be able to find a match for any color Excel has. And then there are 17 other color schemes that can be set for the chart group property of the chart, so there's no end of colors if you have the time to track down one you want.

You have to omit the line trying to set the fore color, although it compiles, because otherwise the script will error. I have commented it out both times, and substituted setting the foreground color scheme instead. It would be nice if a later version of Excel fixed fore color and back color, although it is not a major issue. The problem derives from the fact that the Excel AppleScript developers made this fore color property a simple color type (an {r, g, b} list of three integers – see the PowerPoint chapter for details). In VBA, it is a complex ColorFormat Object that needs a further RGB Property to set the color.

Some other matters: there are two places in the script that will error if you follow the macro syntax exactly: that's the

          Set rData = .Range("A2:A" & .Range("A" & _

             .Rows.Count).End(xlUp).Row).Resize(, rHeaders.Count)

line right near the beginning, and the last line of the

            With .SeriesCollection.NewSeries

                .Values = rData.Columns(i)

                .XValues = rData.Columns(1)

                .Name = rHeaders.Cells(i)

             End With

block adding the series a few lines below it. If you translate those directly as:

             set rData to get resize range ("A2:A" & first row index of (get end ¬

              range ("A" & (count rows)) direction toward the top)) ¬

              column size (count rHeaders)

and

                   set newSeries to make new series at end with properties ¬

                        {series values:(column i of rData), xvalues:¬

                            (column 1 of rData), name:(cell i of rHeaders)}

you will get a fatal error in the first case, and you won't get the names of the series (Y1, Y2, Y3, Y4) in the Legend box in the second case. (It will show the default names "Series1, Series2, Series3, Series4" instead).

This is because in VBA there are Default Properties of many Objects, which do not have to be spelled out in code. The default property of a row, or of any range, is the Cells property. Since rHeaders is a range that's actually a one-dimensional row, to Count the range is to count the cells, as if written as rHeaders.Cells.Count.