• MacTech Network:
  • Tech Support
  • |
  • MacForge.net
  • |
  • Apple News
  • |
  • Register Domains
  • |
  • SSL Certificates
  • |
  • iPod Deals
  • |
  • Mac Deals
  • |
  • Mac Book Shelf

MAC TECH

  • Home
  • Magazine
    • About MacTech in Print
    • Issue Table of Contents
    • Subscribe
    • Risk Free Sample
    • Back Issues
    • MacTech DVD
  • Archives
    • MacTech Print Archives
    • MacMod
    • MacTutor
    • FrameWorks
    • develop
  • Forums
  • News
    • MacTech News
    • MacTech Blog
    • MacTech Reviews and KoolTools
    • Whitepapers, Screencasts, Videos and Books
    • News Scanner
    • Rumors Scanner
    • Documentation Scanner
    • Submit News or PR
    • MacTech News List
  • Store
  • Apple Expo
    • by Category
    • by Company
    • by Product
  • Job Board
  • Editorial
    • Submit News or PR
    • Writer's Kit
    • Editorial Staff
    • Editorial Calendar
  • Advertising
    • Benefits of MacTech
    • Mechanicals and Submission
    • Dates and Deadlines
    • Submit Apple Expo Entry
  • User
    • Register for Ongoing Raffles
    • Register new user
    • Edit User Settings
    • Logout
  • Contact
    • Customer Service
    • Webmaster Feedback
    • Submit News or PR
    • Suggest an article
  • Connect Tools
    • MacTech Live Podcast
    • RSS Feeds
    • Twitter

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.

Would you like a hard copy
or PDF of this Guide?

You can get a hard copy sent to you
AND download a PDF now ($19.95)
, or

... just download a PDF ($9.95).

Either way, you get a complimentary
MacTech Magazine Subscription

courtesy of the
Microsoft Mac Business Unit


 

  Magazine Reg. Price:  $50.00 value  
  Guide Reg. Price:  $40.00 value  
  You Save:  over $80.00!  

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 Format

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.

Borders

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>
 
MacTech Only Search:
Community Search:

 
 
 

 
 
 
 
 
  • SPREAD THE WORD:
  • Slashdot
  • Digg
  • Del.icio.us
  • Reddit
  • Newsvine
  • Generate a short URL for this page:



MacTech Magazine. www.mactech.com
Toll Free 877-MACTECH, Outside US/Canada: 805-494-9797
MacTech is a registered trademark of Xplain Corporation. Xplain, "The journal of Apple technology", Apple Expo, Explain It, MacDev, MacDev-1, THINK Reference, NetProfessional, Apple Expo, MacTech Central, MacTech Domains, MacNews, MacForge, and the MacTutorMan are trademarks or service marks of Xplain Corporation. Sprocket is a registered trademark of eSprocket Corporation. Other trademarks and copyrights appearing in this printing or software remain the property of their respective holders.
All contents are Copyright 1984-2010 by Xplain Corporation. All rights reserved. Theme designed by Icreon.
 
Nov. 20: Take Control of Syncing Data in Sow Leopard' released
Nov. 19: Cocktail 4.5 (Leopard Edition) released
Nov. 19: macProVideo offers new Cubase tutorials
Nov. 18: S Stardom anounces Safe Capsule, a companion piece for Apple's
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live