

Moving from Microsoft Office VBA to AppleScript:
MacTech's Guide to Making the Transition
Introduction
|
Table of Contents
Page Prev and Page Next buttons at bottom of the page.
|
April, 2007
Page 69
In VBA, to change a color on the color palette (which applies only to the workbook)
ActiveWorkbook.Colors(33) = RGB(6, 8, 242)
ActiveSheet.Range("A1").Font.ColorIndex = 33 'Assign the new color
Once you set the color of a particular spot on the palette (33) to the best of your ability to the RGB color of your choice {6 ,8, 242}, you can now set the color of any text or fill to that color by accessing its no. 33 color index. This tends to work better than trying to set the color of any font or other object.
But it is not possible to do in AppleScript unfortunately: this is a gap, or bug, in the Model. When you see the Colors property, you know you have to find another way, or an approximation. There is no equivalent of the Colors Collection Object, and colors cannot be elements because you can't make or delete any colors: the number of colors is fixed at 56. It should probably have been implemented as some sort of color palette property for workbook.
What you can do is call the command reset colors, which restores the color palette to the defaults. And you can try setting the color property by (RGB) color, but most likely that just chooses one the 56 colors, so you might as well do it by color index so that at least you know which color you'll be getting reliably. Call reset colors first if you want to be sure of it.
It is also possible to blend colors on occasion. See the next for an example of blending two colors in anything that has a pattern or gradient, like a cell's interior, and a chart's and drawing object's fill gradient colors.
Background colors and patterns are set using the cell‘s interior object, much as the font format:
With ActiveSheet.Range("A1").Interior
.ColorIndex = 3
.Pattern = xlGray50
.PatternColorIndex = 13
End With
In AppleScript:
tell application "Microsoft Excel"
tell interior object of range "A1" of active sheet
set color index to 3
set pattern to pattern gray 50
set pattern color index to 13
end tell
end tell
This allows you to superimpose a fine-grained pattern (if you wish) with its own color over the basic interior color, to get a very wide variety of blended colors.
Well, we were going to get here at some point. And now we're here. There's some bad news – not very bad news: you can get every border in AppleScript, I can assure you. But it's more tedious work than in VBA if you need to get all of them at once.
In VBA, borders are integral part of the Object Model, and are set using the cell‘s Borders Collection and Border objects. You can set all the (outside) borders at once using the collection properties:
With ActiveSheet.Range("D10").Borders
.LineStyle = xlDouble
.ColorIndex = 3
End With
Or you can set borders individually, which we'll come to later – that is much more analogous in AppleScript.
Unfortunately, borders could not be implemented as elements of range or other objects – which they certainly should be in the sense that they have a many-to-one relationship with the object they're bordering. But they cannot be elements because they are sort of read-only in that you cannot make new borders or delete existing ones. You can change their properties, so they are "writable" in that sense. But for anything defined as an element you ought to be able to 'make new border at range' and you clearly can't do that, nor delete any. Borders are just there.
They also can't be a single 'borders' property with enumerated constants for the different types: they need to be fully-fledged objects whose properties we can modify: Collection Object types, not to put too fine a point on it. And AppleScript does not have those.
(Mind you, that doesn't stop Entourage from having read-only recipients as elements of incoming message class – in fact you can't even make a new recipient at an outgoing message once the message has been created. And since elements can only be made at existing objects, that creates something of a problem, which Entourage gets around by a clever technique that doesn't quite "follow the rules" either – you can read about it in the Entourage Chapter 6.)
< Previous Page Next Page>

- SPREAD THE WORD:
- Slashdot
- Digg
- Del.icio.us
- Newsvine