initializing an array

[Edward]

Hi Everybody,
Does anyone know why initializing an array using a range is much faster
than iterating through the cells in the same range( what's the technical
explanation for this).

Dim A
A=Range(“A1:A100â€)

Compare with

Dim A(100) as integers
For i=1 to 100
A(i)= ……
next

As far as I know this method of assignment to a range in Excel specific ,
but I just want to know how Excel uses a range to initilize an array which is
so efficent and fast compare with noraml method of iterating through all the
elemnts of any array.

 

[Dave Peterson]

Just a layman's view...

I would think you could compare it to going to the store and getting all your
groceries at once. Compare that with going to the store, getting the milk,
going home; then back to the store, get the bread, back home; then back to the
store, get the butter, back home; etc....

There's a "get-started" cost of going to the store (or going into the Excel
application). But it's better to get everything in as few trips as you can.

 

[Edward]

I understand the concept but I was hoping someone can give a technical
explanation of how Excel VBA uses this range abject to initialize an array.
My assumption is since Excel already has the every cell in the memory it just
need to refrence to that specific location , something like
Array1=Array2