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 76

     end tell

end tell

This turns the background red and font bold white in B2, when there's a number from 1 to 9 in A1. It works perfectly in AppleScript.

Format conditions can also format borders, as well as fonts and interiors. In VBA, the Borders Collection is a property of the FormatCondition Object and work the same way as fonts and interiors do. In AppleScript you have to use the same get border command as we used in the previous subsection, here targeted at the format condition element, as found in the Microsoft Excel Suite. (It's listed as a different get border command in a different suite, so as to be able to specify the type of object it works on – format condition rather than range. But actually it's the same old get border command and works identically to the one we used with range.) It returns a border of the type you specify with the which border parameter, and you then apply the formatting to each border you retrieve.

Working with Charts

Charts, partly because they‘re so flexible, are complicated objects. chart object objects exist either as chart sheets or as embedded chart objects on a worksheet. The chart object has a chart property, which is a chart (class) that has an enormous number of properties. (I know – that's a ridiculous-sounding sentence, but really and truly that's how the various chart-related classes are organized and named.)

The following example embeds an XY Scatter chart on the active worksheet. It assumes that the data is in columns, with the first row being a header. It will automatically determine the number of series from the number of headers, and will determine the number of data points from the number of X-values in column A.

At the end of this section I have included a table of numbers making up the data John McGimpsey provided for this example. If you are reading this online, you might be able to copy and paste them into an Excel worksheet and try the script. The precise numbers don't matter much: you can see that columns A, B, C were just filled from 1 to 40 (in rows 2 to 41). Column D uses a formula: =MAX(A:A)/2+(1+SIN(A2)*2*PI()), also filled from row 2 down to 41. Column E is a random distribution of the same numbers 1-40 (making Y4's graph plot quite exciting as the numbers flip all over the place).

This is a much longer macro and script than any others in the chapter. Everything in it is quite straightforward, and no more difficult than anything else in the chapter – some of it easier. Due to its length, I will not walk through every line of it, but just point out a few issues, problems, and workarounds where there are any, after the script version.

Public Sub CreateEmbeddedChart()

   Dim oChartObj As ChartObject

   Dim rHeaders As Range

   Dim rData As Range

   Dim i As Long

   ' Dynamically grab the data in columns

   With ActiveSheet

      'Find the headers in the first row

      Set rHeaders = .Range("A1").Resize(1, _

          .Cells(1, .Columns.Count).End(xlToLeft).Column)

      'Now find the number of data rows.

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

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

      If rHeaders.Columns.Count = 1 Or _

             rData.Rows.Count = 1 Then Exit Sub 'no data

      'Create chart object

      Set oChartObj = .ChartObjects.Add( _

                   Left:=400, _

                   Top:=100, _

                   Width:=500, _

                   Height:=400)

      'Now build the Chart within the ChartObject

      With oChartObj.Chart

          .ChartType = xlXYScatterSmooth   'define chart type

          'Add each series

          For i = 2 To rHeaders.Count

             With .SeriesCollection.NewSeries

                .Values = rData.Columns(i)

                .XValues = rData.Columns(1)

                .Name = rHeaders.Cells(i)