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



        cell i of someRange

so that's what we do, and everything is fine. The VBA Offset Method is reproduced by get offset: we have to spell out the parameter names row offset and column offset of course, but the default for each is 0 so we can omit column offset.

The union command, which for some reason we find in the Microsoft Excel Suite instead of in the Table Suite where it ought to be since it acts only on ranges, uses parameters range1, range2, etc. I am happy to report that delete entire row works fine on a non-contiguous range.

However, there is yet one other way we could do this in AppleScript, not available to VBA, since delete can act all at once on a list of ranges:

tell application "Microsoft Excel"

     tell active sheet

          set lastRowNum to first row index of ¬

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

          set rDelete to {}

          repeat with i from 1 to (count cells of range ("A2:A" & lastRowNum))

              --that's column A from 2nd cell to bottom used cell                                   

              set rCell to (cell i of range ("A2:A" & lastRowNum))

              if value of rCell = value of (get offset rCell row offset -1) then

                   set end of rDelete to (entire row of rCell)

              end if

          end repeat

          if rDelete ­ {} then delete rDelete

     end tell

end tell

Isn't that simple in comparison? We initialize rDelete to an empty list {}; if we find a duplicate cell we set the end of the list rDelete to the entire row of that cell without having to check every time to see if the list is empty or not, then at the end we delete the list and that's that. Accustomed as you are to VBA you may prefer to mirror it exactly as you start converting your macros, but in time as you get accustomed to AppleScript you will, and should, find more native and natural ways to do things in your new language.

Sorting a Range

The above example, deleting duplicate rows, assumed that the data was sorted first. (Otherwise there might still have been duplicates that were not in adjacent rows.) This can be accomplished in VBA by the Sort method, and in AppleScript by the identical sort command. If a single cell is specified, the current region will be sorted (the area around the cell bounded by both blank rows and columns. This snippet sorts the current region around cell A1 based first on the values in column A, then on the values in column B:

With ActiveSheet

   Range("A1").Sort _

      Order1:=xlAscending, _

      Key1:=.Columns("A"), _



< 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