• 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 61



The thought of stuffing that complex and long-winded (in AppleScript) evaluation of the row number right into the repeat loop statement, as VBA does with its shorter terms and dot syntax, is too much to bear contemplation. It's better to work it out as a variable. Recalling the problems we found with columns and rows (check the Alert in Working with Columns and Rows), it is essential to use the application terms directly without variables for a row, but it always works to set a variable to a range or, as here, to a number (or string, date or other basic type).

With long trails of dots in VBA, it is natural when translating into AppleScript to expect to start at the end and work backwards from there, except that you actually need to start off with the "front" VBA object in order to know where to look for the properties that follow. Don't forget that whereas in VBA a Method name might occur somewhere along the way in the same dot syntax and parent-child relation as for a Property, in AppleScript the equivalent command does not use the same 'of' syntax as a property or element, and has to be sought in the Commands section of the appropriate dictionary Suite.

What in VBA is a Property might be an element, or a property of an element, or even a command, in AppleScript. So it can take a bit of time to find everything and work it all out.

We recall that the Cells(r, c) syntax translates to

      cell c of row r

in AppleScript, and the result is a range. So we look in range class in the Table Suite for something analogous to the End property – it might be end of range or something like that. But there's nothing at all like that to be seen in range class.

So we should look now in the Commands section of the Table Suite for something that might get the end of a range. There's nothing listed under "end of" there. So check the "get" neighborhood – always a good idea. When they need to come up with a proprietary command when a property won't work, the developers often start the command term with "get".

And there it is – get end. It takes a range as direct object or parameter ("direct" because no specific parameter keyword is needed) and its direction parameter takes an enumeration that is just what we are looking for, specifying toward the top for VBA's xlTop. (In this case it really means "last one, or end, starting at the top", i.e., oriented from the top's point of view. We're actually looking for the bottom row, of course.)

We know that although we can't get rows or every row of a range or sheet, we can, by some strange magic, get count rows (or count every row). (Check Working with Columns and Rows again.) Finally the Row Property of Range (it's a good thing, and to be expected, that AppleScript does not call this property "row": it would conflict with the class row) is evidently first row index property in AppleScript – a nice descriptive term that returns an integer, just what we are looking for.

So now we have everything needed to construct the statement that should return the row number of a cell:

     set lastRowNum to first row index of ¬

              (get end (cell 1 of row (count rows)) direction toward the top)

The one remaining thing would be to check whether AppleScript lets you use get end on the sheet itself (the target of the tell block) without producing 65535 as the last row of the sheet instead of the last used row, or would require us to explicitly target used range of the sheet instead, But no, AppleScript allows us to take the same shortcut (or coercion) as Sheet.End does in VBA: we get the last used row number, not 65535.

The rest of the script is easy, using cell c of row i a few more times, and getting the value property of each range (cell). delete entire row works just as it should, as long as you remember to do your repeat loop backwards "by -1" equivalent to VBA's Step -1.

A more efficient way to do this is to collect all the duplicates in a range variable and delete them all at the same time. Note that the Union method in VBA requires that both Range objects are set, and the same will be true in AppleScript with the union command.

Dim rCell As Range

Dim rDelete As Range

With ActiveSheet

   For Each rCell In .Range("A2:A" & _

             .Range("A" & .Rows.Count).End(xlUp).Row)

      If rCell.Value = rCell.Offset(-1, 0).Value Then

          If rDelete Is Nothing Then

             Set rDelete = rCell

          Else

             Set rDelete = Application.Union(rDelete, rCell)

          End If

      End If

   Next rCell

   If Not rDelete Is Nothing Then _

          rDelete.EntireRow.Delete

End With



< 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