The Array Type

Topic 'About array formulas and array constants' in help seems pretty adeqaute to me.

xld said:

Topic 'About array formulas and array constants' in help seems pretty adeqaute to me.

Click to expand...

Thanks for your reply but I did say 'excluding the Excel helpfile'.

And I am hoping for something that drills into the topic a little bit deeper.

Colin_L said:

Thanks for your reply but I did say 'excluding the Excel helpfile'.

And I am hoping for something that drills into the topic a little bit deeper.

Click to expand...

Just to explain a bit more.
I'm pretty familiar with array formulas - I'm no Aladin, but I'm not too shabby either. I've noticed a number of oddities which all seem to route back to this 'array type'. These observations would take quite a bit of explaining, so I was hoping to try to find out a bit more background information before taking up everyone's time by asking lots of questions.

Thanks
Colin

try out this:

http://www.cpearson.com/excel/ArrayFormulas.aspx

Colin_L said:

Thanks for your reply but I did say 'excluding the Excel helpfile'.

And I am hoping for something that drills into the topic a little bit deeper.

Click to expand...

You did, but as I said, that info seems more than adequate to me, it explains it fine, and there was always the possibilty that you had searched on some other phrase and sdidn't even see it (did you even read the page or did you allow your prejudice to rule?).

If you are having issues you need to talk about them rather than have us trying to second guess what the problem is and steer you towards an answer.

Array formulae are generally lacking in much good write-up on the web, which you must know if you are as tasty as you say, but array constants are so simple I fail to see what more can be said about them that is not in the helpfile.

Hi,

Thanks for your replies. Yeah, xld, that's a fair enought point - I'll start being a little bit more specific, but rather than jumping in the deep end I'll try to build up.

In the introduction paragraph in this primer on array constants, J-Walk states that array constants are stored within the memory rather than within the workbook. This surprises me. Do we know if this is fact (and how do we know this!?), do we know the type of structure that is used in the memory to store them, and do we know if named array constants are stored differently to [non-named] array constants?

Cheers,
Colin

Colin

My understanding from reading that article is that array constants must be stored in memory because they are not making reference to actual cells. Efficiency gains presumably thus come from not having to interrogate a given range for the values to use at each calculation.

RichardSchollar said:

Colin

My understanding from reading that article is that array constants must be stored in memory because they are not making reference to actual cells. Efficiency gains presumably thus come from not having to interrogate a given range for the values to use at each calculation.

Click to expand...

Hi Richard,

Maybe I'm just getting myself confused about what 'stored in the memory' actually means. I think a simple example will help me with this. My own gut feeling is that named array constants and [non-named] array constants need to be considered separately. So first let's take a [non-named] array constant as an example....

Say I type a formula with a [non-named] array constant into a cell, for example:
={3,5}

Now, I think it's fair to say that the expression "={3,5}" is stored within the cell and thus within the workbook. What is actually displayed in the cell is 3, and the way this is exactly displayed will depend on the formatting of the cell - so it could be 3 or 3.0 etc...

So, in this example, what information exactly do we think is being 'stored in the memory'?

Cheers,
Colin

In Excel you have the Screen, its Cells and the page buffer for Sheet operations. You can by-pass the Cells directly, by setting the ScreenUpdating property to False, this does all Sheet work in the buffer, aka: memory. Without using ScreenUpdating set to False, and ScreenUpdating is active then Cell Range Work is moved from the page buffer as soon as it happens to the Sheet and other things may happen, like re-calculations, formatting... But, even with ScreenUpdating On, an array is stored in its own memory buffer independent of the Sheet buffer, so many of the "House-keeping" work that happens in the Sheet and Page buffers is by-passed untill code reads that memory into a range and then into the page, sheet buffer.