• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag
 

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: Converted VBA macros
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Converted VBA macros


  • Subject: Re: Converted VBA macros
  • From: Shane Stanley <email@hidden>
  • Date: Tue, 06 May 2008 09:29:18 +1000
  • Thread-topic: Converted VBA macros

On 6/5/08 6:19 AM, "Raymond P Reedy" <email@hidden> wrote:

> I have converted a few of my VBA macros to AppleScript and have
> tested them in Leopard using Office 2004 (Excel) and Office 2008
> (Excel). An observation..
>
> The scripts run extremely slow in Office 2008.

You're not the first to make that observation.
>
> This particular snippet takes 13 seconds in (2004) and takes 1 minute
> 19 seconds in (2008), processing only 238 records.
>
>
> set lastRowNumSorter to first row index of
> ¬                                       --Count records
> (get end (cell 1 of row (count rows)) direction toward the top)
>
> repeat with i from lastRowNumSorter to 2 by
> -1                                   --Loop while deleting empty
>
> if value of (cell 3 of row i) = "" then
> delete entire row of (cell 3 of row i)
> end if
>
> end repeat

You could probably speed that up considerably by rewriting it to to get the
values of a range ("C2:C" & lastRowNumSorter?), and looping through the list
in AS, rather than firing off repeated requests to Excel.

You could then use "delete range" followed by a list to do all the deletions
in one event.
>
> I also noted while testing that any user action on the worksheet
> while the script is running (scrolling to observe) produces
> unexpected results.
> How can I "Lock out" the user while the script is running?

Turn off screen updating, and set visible of window 1 to false for the
duration.

--
Shane Stanley <email@hidden>


 _______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users

This email sent to email@hidden

References: 
 >Converted VBA macros (From: Raymond P Reedy <email@hidden>)

  • Prev by Date: known applescript prevent sleep issues?
  • Next by Date: Re: InDesign Set Opacity Gone In CS3?
  • Previous by thread: Converted VBA macros
  • Next by thread: known applescript prevent sleep issues?
  • Index(es):
    • Date
    • Thread