Re: best way to do fast arithmetic operations on lists?
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