Excel VBA Interception and Implicit Intersection and single Breadth Arrays
If the last post is understood, then what is often considered as Implicit Intersection can be explained as a by product of what Excel does in terms of my imaginary help matrix, when we consider a similar idea for the case of a reference of a particular form. It is the sort of "middle case":
To refresh: In the last post we looked at the two "extreme cases" : That of
_ a multiple 2 dimensional range, like = A1:G5, or
_ a single cell reference, like =C1.
We now consider the case of a single "breadth" range. By single "breadth", I am talking about a range like A1:C1 or A1:A2 etc.
We considered already for the case of a 2 Dimension range that we could visualise a full range help matix
We said that effectively a Shift was needed in "horizontal" and vertical directions before the effective "revealing" of the cell "snap shotted in time" could be done. The imaginary help matrix, I suggested, was empty elsewhere, so nothing was revealed by a simple = in a cell. We were imagining that the = "made visible" the help matrix at the same point as that of the cell when "seen" by a screen update for that cell.
The idea for the single reference case was that the help matrix had the single value from the referenced cell in all its "cells".
This made the intersect very fast.
My guess is that the corresponding imaginary help matrix for a single "breadth" range such as =A1:A2 could look like somewhere between the two, like this: | Help Row\Help Col |
A |
B |
C |
D |
|---|
1 |
A1 |
A1 |
A1 |
A1 |
2 |
A2 |
A2 |
A2 |
A2 |
3 |
|
|
|
|
It may be obvious the situation here. The CS Entry will work just as in the case of the 2 dimensional Array case, and Excel will with the returned array, follow its normal multiple evaluation , ( but going just along or just down to select the values and place in a return array for output. ) So we effectively only have to Control a Shift in one direction
Possibly at this stage, as an aside, stepping back and adding some clarity to what is going on with a simple example is helpful here:
Any range reference in a cell ( we are just considering one here, =A1:A2 ) , will as a result of a CS Entry have an array returned mirroring in dimensions and size the range referred to, but having the values from that range in it. The complete formula will then be evaluated talking in turn the single values from the corresponding position on any array ( only one this case of the simple formula of a single reference, =A1:A2 ) , and the total results collected in a similar size and dimensioned array.
For example, consider the array {1, 2 } and { 3, 4 } as those which are returned from two references in a formula in a cell of = B1:C1 * C2:D2
Excel will return finally { 3, 8 }
Graphically consider some values in the first 2 rows of a spreadsheet, then if I do a type 2 CSE entry of that formula in, say in the range A3:B3, this is the sort of results I will have in the spreadsheet:
Values: | Row\Col |
A |
B |
C |
D |
|---|
1 |
|
1 |
2 |
|
2 |
|
|
3 |
4 |
3 |
3 |
8 |
|
|
| Row\Col |
A |
B |
|---|
3 |
{=B1:C1*C2:D2} |
{=B1:C1*C2:D2} |
______ 1x3 __ , __ 2x4 ( columns then next row, - in this case just 1 row )
Coming back to the example in the original screenshot:
If we do no CS Entry , the default process comes in which effectively imagineatary "reveals" the help matrix.
It will not give us a value in every cell in the worksheet. But as can be seen in the first screen shot we will get in the yellow cell the value from cell A2. ( remember the formula under consideration in that yellow cell would be =A1:A2 written in the spreadsheet a t the corresponding position in the imaginary spreadsheet on out screen ).
If the formula were CS Entered, the value would be A1 in that cell, ( or in any cell other than A1 or A2 anywhere in the spreadsheet).
In a cell in row 1 we would get A1 with or without CSE.
For any row other than row 1 and row 2 we would get an error without CSE
Edit later ref: https://support.microsoft.com/en-us/...rs=en-us&ad=us
Reply With Quote
Bookmarks