Re: Scripting in Excel
Re: Scripting in Excel
- Subject: Re: Scripting in Excel
- From: Kai Edwards <email@hidden>
- Date: Sun, 03 Nov 2002 23:11:57 +0000
on 28/10/02 12:05 am, Kai Edwards at email@hidden wrote:
>
on Sun, 27 Oct 2002 08:15:17 -0800, Paul Berkowitz <email@hidden>
>
wrote:
>
>
> On 10/27/02 7:18 AM, "Kai Edwards" <email@hidden> wrote:
>
>
>
>> So it was only when there were no cells (or a negligible number of them) to
>
>> be found, that this approach lagged a little behind 'find-in-Excel'.
>
>
>
> I think you might be able to make it even quicker by... (snip) ...checking to
>
> see if the resulting string contains any of the values you are seeking. if it
>
> doesn't, end of script. if it does, check each rowVal... (snip) ...it's
>
> worth a try.
>
...I shall revisit this one as soon as I have another spare moment!
At last, a spare moment!
As you'll see from the comparisons below, the 'all-cell' check has the
effect of moving the entire performance curve - so that, with the exception
of the 100% 'found' cells, the times are just slightly longer across the
range of tests. However, that's compensated for by the time saved if _no_
cells meet the search criteria.
On reflection, I may have been a tad too dismissive of the 'row-by-row'
checks. While there is a performance overhead - it's only when the
proportion of 'found' cells is greater 50%. (Thanks again for both
suggestions, Paul.)
I guess at the end of the day, the 'best' method will depend on the type of
results one might normally expect in a given situation.
=======================================================
Also, on Mon, 28 Oct 2002 19:07:53 +0800, bill <email@hidden>
wrote:
>
> ...to avoid the risk of skipping rows in a longer column (or of needlessly
>
> checking empty ones in a shorter one), it might be useful to include some
>
> form of automatic 'last-row' adjustment.
>
>
Surely, some form of automatic last row adjustment is better than fixed
>
range.
Agreed. (Actually, that's what I was trying to suggest.)
>
> ...the main bottlenecks in both cases were associated with frequent calls to
>
> Excel - firstly to get each cell's value (or to find each relevant cell), and
>
> then to format each 'found' cell.
>
>
Maybe there9re two many apple events between applescript & Excel, so the
>
speed is... Slow.
Agreed. (That's really what I was trying to suggest, too.) ;-)
>
Well, would you mind to test this one? :)
(snip: script)
Not at all, Bill (- and sorry for the delayed response). As you'll see
below, your modified version definitely improves (across the entire range of
comparisons) on your original.
=======================================================
The results are labelled like this:
a: Bill's original 'cell-by-cell' routine (24 Oct 2002 16:46:23 +0800)
b: Bill's modified version (28 Oct 2002 19:07:53 +0800)
c: My original 'find-in-Excel' routine (24 Oct 2002 01:40:04 +0000)
d: My subsequent 'reduced event' routine (27 Oct 2002 15:18:36 +0000)
e: d with Paul B's 'all-cell' check (27 Oct 2002 08:15:17 -0800)
f: e with Paul B's 'row-by-row' check (27 Oct 2002 08:15:17 -0800)
The test were carried out on 500 cells (100 rows x 5 columns). The '%'
column shown below gives the percentage of those cells matching the search
criteria.
Since execution times are relative to the machine on which the tests are
carried out, I've used an index: 1 = fastest time:
-------------------------------------------------------
% a b c d e f
---- --- --- --- -- -- --
100% 194 189 359 20 21 32
50% 127 113 184 15 16 15
25% 107 75 93 12 13 11
20% 82 64 73 11 12 8
15% 77 58 55 9 10 7
10% 65 49 37 7 8 5
5% 57 39 19 6 7 4
0% 50 32 1 5 1 1
-------------------------------------------------------
I hope these figures might be of some interest to those scripting in Excel.
Kai
--
email@hidden
email@hidden
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.