Charles:

Well thanks once again. You have now not only helped me completely
solve my original dilemma, but on the side revealed some wonderful
additional solutions and insights, some of which I had long been
seeking.

I was already operating under the large truck analogy, but did not
know what truck to use for one direction of large array transfers:
Predimensioned arrays can be sent to excel ranges in one assignment
statement, but the other direction just does not work. Until I saw
your use of an undimensioned Variant for that purpose, I could only
loop through all Range Cells to get their values into a VBA Array, i.e
using the smallest truck possible.

Sorry I won't be in London for your conference, but your help for me
here in Seattle will be long remembered and passed on wherever I can.

Nick

On Thu, 11 Dec 2008 08:53:00 -0000, "Charles Williams"
wrote:

Hi Nick,

To answer your questions:

There is a significant overhead associated with initiating transferring
stuff between Excel and VBA.
I always think of VBA and Excel being separated by a very large hill.

See Variant Benchmark on my downloads page to measure the relative size of
the overhead on your system, and also
http://www.decisionmodels.com/VersionCompare.htm

So the larger the amount you transfer in each read or write (use the largest
truck possible for the transport over the hill) the more efficient it is.

There is also a small tunnel through the hill through which VBA can reach
and manipulate things on the Excel side (tunnel=the object model). For
instance if you want to use a worksheet function in your VBA (MATCH for
example) its more efficient for VBA to reach through the tunnel and tell
Excel what to do on the Excel side of the hill and only bring back the
result of the MATCH, rather than transporting all the data over the hill.

Using .VALUE2 is more efficient than using .VALUE (and usually safer as
well) because it does not do the additional implicit conversion for currency
(which may lose precision) and date formatted data.
Unfortunately the default is .VALUE

Why does Excel evaluate Cells containing references to uncalculated cells?
I have not done an analysis, but I suspect that in general its more
efficient when recalculating because the calculation chain will be well
ordered so its not worth doing the extra work of checking.
And there are probably cases where it needs to do this anyway (for instance
a formula containing multiple components some of which reference calculated
cells and some of which reference uncalculated cells, and maybe finding
circular references).


Charles
___________________________________
London Excel Users Conference April 1-2: book now not many places left!
The Excel Calculation Site
http://www.decisionmodels.com