What is Implicit Intersection
As far as I know there is no official documentation on this and blogs on it are sparse.
The phrase Implicit Intersection is generally used to explain when a formula does what might not initially be expected, ( in most cases this means that it surprisingly “works” in certain cells in an Excel spreadsheet! ) .
What is Excel VBA Interception
I made that title up myself.
It is a theory or idea I have about how Excel works which explains the results in an Excel Spreadsheet which are covered often by the phrase Implicit Interception. It also helps explain and predict some other interesting things in VBA. My suggestion is that what is meant by Implicit Intersection is a by product of Excel VBA Interception. Excel VBA Interception is an integral part of the basic way that Excel works. The by product of Implicit Intersection results from an attempt to “hold” cell values in a way to help speed up calculations in Excel and VBA.
I tend to view in my theory Excel spreadsheet operation and VBA as the same. That view point is based on the idea of that much of what happens in a spreadsheet is based on pre written VBA type coding.
_This post offers some theory, or a model, that can explain the Interception Idea.
_The following post shows how this can explain what is typically referred to as Implicit Intersection, ( I might argue this is a specific Implicit Intersection type)
_The third and final post attempts to show a working example
Excel is a cell, mostly. ( What I see and how).
Of course that is an over simplification, but most often that is what anyone would think about when talking about Excel. Most VBA codes are designed to change things in cells in a spreadsheet. Using them and their contents is fundamental then to Excel and VBA. I am thinking that Excel only has a model of one cell that includes all the stuff of a cell. That information is stored in a massive thing ( http://www.excelforum.com/showthread...11#post4551080 ) called a Range object.
A spreadsheet is made to look like lots of cells on our screen by the software holding similar information as in the one cell at a specific offsets in memory locations. Those offsets are somehow synchronised to the rate at which the information is put on the screen. So we are fooled into thinking we have a sheet with cells spread out across our screen. This last point is important. In my explanations I will often talk about extra help matrix holding values. In reality they will not actually be that. They will be some mathematical formula that actually optimises how the information is held. The same is done with the formulas used to produce the spreadsheet that we see: They are somehow optimised, for example, such that empty cells do not exist. Otherwise our Excel File would always be enormous even with nothing in it Just as we “visualise” the spreadsheet, I am visualising other matrixes that I discuss. I think my imaginary matrixes would be sort of technically where you effectively get to by the “dependency tree”, which again in the final “thing” is a formula based on certain “offset type coordinates”
Excel has been written such that when a “change” is made “anywhere” in the spreadsheet, then all cells are “updated”. ( That is one reason you often here that VBA codes that “do things” to a worksheet can be slow as every interaction “slams the brakes on” whilst the whole worksheet is updated. Often in a code you will see Screen Updating turned off until the end of the code when all the updating is then done in one go rather than at every interaction. All the “VBA Array stuff” is about is like, for example, making an internal copy matrix of cell values, doing any processing you want to on them internally, then “pasting them out” to the worksheet in one go. You can do that all in one go stuff efficiently as you just like use the same single offset value for all the values to put them in the place for those things in the referenced Range object ). With this in mind, I expect some attempt at making Excel efficient in doing that may have been made. I think the effect observed and attributed to “Explicit intersection” could be a by product from that.
Referring to cells and groups of cells areas. Offsets and Resize to “full area”
Excel’s way of organising and “getting at” the cells and constructing Range objects
Cells can be organised into groups of cells. These are organised in exactly the same form as a single cell. In other words a Range object can be made up of groups of cells or Range objects. Whether one imagines one “sees” one or more cells is all based on some offset or rather sets of offsets.
Imagine in the screenshot below that the yellow is the entire spreadsheet. That itself could be a Range object, ( indeed in VBA we have a Property, .Cells , of a worksheet, which when applied to a Worksheet object returns a Range object containing all the cells in a worksheet ).
“Getting to”, “seeing”, “returning some Property, (value” ).. etc.. is all based on a “offset referencing coordinate system”. This has two basic forms.
_ a simple ( Up/down , left/right ) to “get to any point going in any direction within the Worksheet”, and
_ a similar but restricted to from top left, to bottom right to define a full rectangular area
Using such a system it is possible to “get to” or “construct to see” any combination of cells in a worksheet. So in the screenshot below the Red and Blue areas could be organised into a single Range object. In other words a Range object is organised into groups of contiguous cells. ( These are called Areas ). It can of course be just one Area or even a single cell. ( http://www.excelforum.com/showthread...11#post4551484 http://www.excelforum.com/showthread...154829&page=13 )
Based on the last section we have then a system based on going +ve and –ve in two directions to “get there” , then once there we go from a top left to a bottom right to make a “seen rectangular area of contiguous (contiguous means here all there, no spaces ) cells”. ( It comes then maybe as no surprise that in VBA Programming one finds the parallel concepts of a Offset and Resize Property. Furthermore one finds these a very efficient way to do things directly rather than adding an ““in between step” for simplicity of understanding” such as including different variable for different Range objects. )
Row\Col
See here http://www.excelforum.com/showthread...154829&page=13 for the parallel VBA concepts_..
_ going +ve and –ve in two directions –--- Range.Item.Property , and
_ from a top left to a bottom right to make an area ---- Range(“ “) Property.
Intercepts. ( after Enter in a cell with a formula )
Everything that we visualise is going to be realised by a Offset to the “one Excel cell”. We see a cell as a result of some synchronisation to a continuum whose “refresh” or “update” is based on going from top left to right, then next row, from left to right, etc. finally ending up at bottom right . It makes sense to use this “positional information” in some efficient way. My idea, which in the reality is realised by a complex mathematical calculation, can be thought of in a similar way to as we imagine a spreadsheet as a matrix. Any single cell reference used in any formula, that is to say appearing in a string after a = , will present a matrix which is “revealed” as the value “seen” by the window which in this case is the single cell.
The screen shot below is intended to represent 2 different update runs in a worksheet, ( not necessarily the same worksheet ). Imagine that yellows are the cell which we might see in a snapshot in time as a spreadsheet has its displayed values updated. Imagine the Values are in my imaginary help matrix which I am suggesting is created for any single cell reference.My idea of how Excel works is to display what is at the intercept of the Yellow and the help matrix. If you type in =A1 or =A2 in any cell it will ”work”. I expect this is very efficient. I think implicit intersection is also a good phrase for what is going on.
A1 A1 A1 A2 A2 A2 A1 A1 A1 A2 A2 A2 A1 A1 A1 A2 A2 A2
I am thinking that if I have a reference to any 2 dimensional range area, such as A1:B2, the help matrix is created differently ( or in the reality a different mathematical process is going on which can be visualised as such a matrix)My idea of how Excel works is to display what is at the intercept of the Yellow and the help matrix. I would expect this intercept idea to cause some sort of error in this case as there is nothing to intercept. Indeed if you try anything of the form =A1:B2 in any cell then it will error.
A1 B1 A2 B2
Controlled Shift before Enter in a cell with a formula
To still get my basic idea to work you would somehow need to add some additional action to effectively do some effective Shifting of the yellow so that the correct cell in the help matrix is Covered before doing the same intercepting process. This would need to be repeated for as many effective single cell references in the are represented by the 2 dimensional range area referenced. We can now understand possibly why our cells and Range objects are organised such that a Range object construction is similar for single and multi cell areas. The same process used in updating an entire worksheet can be utilised to carry out this multi cell reference evaluation. In doing so it is probably a good guess to say that the returned values will start in origin which in this case is the yellow cell and will extend in to the right and down directions from that origin. This is available to us. As it is somewhat inefficient, ( doing a sort of 2 step Shift along and vertical ), this is not the default that would be implied and applied up until now after the use of Enter. The keys chosen as an alternative are correspondingly Control ( Ctrl ) + Shift + Enter. Such an Entry is known as
CSE type 1 Entry.
You will find writing in a references such as = A1:B2 as follows “works” ( does not error) if you do this
_a)_ Select any cell in the worksheet ( other than in the range ( A1:B2 )
_b)_Hit F2 or click in the formula bar ( or don’t bother )
_c)_Type in =A1:B2
_d)_ Hold down Ctrl + Shift , then hit Enter ( or hit simultaneously Ctrl+Shift+Enter)
CSE type 2 Entry
If you try the CSE type 1 Entry with a range of values in cells A1:B2 you will notice that only the value for top left ( A1 ) is displayed. As noted the values are effectively there offset to the right and down. The yellow cell displaced as a snapshot in the Worksheet updating process will correspondingly be at the top left of the range returned by the evaluation. It is a reasonable assumption that the intercept “partner” of the defined cell where the formula is needs to be extended to cover that range. So simply repeat the CSE type 1 entry procedure, but at step _a)_ select any 2x2 range in the worksheet ( other than one containing any cell in A1:B2 ) . This is known as CSE type 2 Entry.
After carrying this out, if you select any of the cells in which you CS Entered the reference, the following will be shown in the formula bar
={A1:B2}
The { } is just the spreadsheet convention for an Array of values.
You can repeat the exercises above with ={1, 2; 3, 4} in a cell in place of a reference. The results are similar but will not error without a CS Entry. This is because no Controlled Shift is required to obtain that Array of values. The process here is the multi evaluation which Excel is set to do in general when an Array is in any formula. The CS Entry was previously required to return that Array. The CS Entry is required here only for display of all values.
As a last note of interest: Excel and VBA basically use the same processes: In VBA there is Function
Evaluate(strEval)
This basically allows you to put a string, strEval, such as this form “=A1:B2”, into the argument and basically what happens is that done by for that =A1:A2 in the cell. There is no requirement for any CSE action: The Function returns the Array directly. As it is not an entire spreadsheet updating action, the intercept efficiency thing is not needed
This basic process can also be used from within the worksheet as follows:
Type any reference such as =A1:B2 in any cell. Select ( Highlight ) the display it in the formula bar. (The reference can be entered by Enter or by CS Entry ) . Hit F9. This calls into play the basic Excel evaluation procedure and the results are shown. Important: Hit Esc or Ctrl+z to return to formula view.
Reply With Quote
Bookmarks