• 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: best way to do fast arithmetic operations on lists?
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: best way to do fast arithmetic operations on lists?


  • Subject: Re: best way to do fast arithmetic operations on lists?
  • From: Paul Berkowitz <email@hidden>
  • Date: Thu, 16 Dec 2010 10:24:50 -0800
  • Thread-topic: best way to do fast arithmetic operations on lists?

Title: Re: best way to do fast arithmetic operations on lists?
The best way to script Excel is to take advantage of Excel's own procedures, which are superfast, and script them using the Excel dictionary – with the Excel 2004 AppleScript Reference, or whatever might have replaced it for newer versions, available on the MacTopia )Microsoft Mac) website to help you –  rather than doing basic AppleScript techniques and transfer them to Excel in loops, which can be torturously slow, as you've discovered.

For example, there's surely some Excel formula method to add a constant such as delta to every cell value in a given range.

Another way would be to use the autofill feature in an unused column or row, then set the value of the original column or row to that of the new one, and then clear the old one.

For example, say that the values of A1:A6 are 1, 3, 7, 11, 15, 100 and you want to add 100 to them. In Excel UI you'd do that easily by typing "=A1 * 100" (without quotes of course) into B1, then position the cursor at bottom right corner of B1 (which changes the cursor to a + sign) and drag down through B6, which autofills by adding 100 to every cell value of column A.  You can do the same thing in script by:

set delta to 100
tell application "Microsoft Excel"
   set formula of cell "B1" to ("=A1 + " & delta)
   autofill range "B1" destination range "B1:B6"
end tell


That results in B1:B6 being filled by 101, 103, 107, 111, 200

To replace column A by these new values (original value + 100) just add those steps:

set delta to 100
tell application "Microsoft Excel"
   set formula of cell "B1" to ("=A1 + " & delta)
   autofill range "B1" destination range "B1:B6"
   set value of range "A1:A6" to (get value of range "B1:B6")
   clear range range "B1:B6"
end tell


This takes just an instant, even with enormous ranges. As I said there may be another way using just a formula, but you can't actually script excel formulas without making use of an available (empty) range to put your values into anyway, so it would come down to the same technique as the above method (transferring the result to the original range and clearing the one you used as a scratch pad) anyway, I think. The main thing is that you completely avoid setting huge numbers of individual cells via lengthy list loops, and do It in one fell swoop.

--
Paul Berkowitz



From: Christian Prinoth <email@hidden>
Date: Thu, 16 Dec 2010 16:25:38 +0100
To: AppleScript-Users <email@hidden>
Subject: best way to do fast arithmetic operations on lists?

Hi, I am trying to read a list of numbers from excel, then add a number to each element of the list, and write back the result, as follows:

tell application "Microsoft Excel"

if date 1904 of active workbook is true then

set delta to 693960 + 1462

else

set delta to 693960

end if

set mldates to value of selection

repeat with i from 1 to length of mldates

repeat with j from 1 to length of item i of mldates

set item j of item i of mldates to (item j of item i of mldates) - delta

end repeat

end repeat

set value of selection to mldates

set number format of selection to "dd/mm/yyyy"

end tell



Unfortunately this becomes very slow for large arrays, I guess the script sticks inside the loops. Is there any way to make this faster, eg. something like



set mldates=mldates+1?



Thanks

Christian


_______________________________________________
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
 _______________________________________________
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: 
 >best way to do fast arithmetic operations on lists? (From: Christian Prinoth <email@hidden>)

  • Prev by Date: Re: What Use Autofill
  • Next by Date: Re: best way to do fast arithmetic operations on lists?
  • Previous by thread: best way to do fast arithmetic operations on lists?
  • Next by thread: Re: best way to do fast arithmetic operations on lists?
  • Index(es):
    • Date
    • Thread