+ Reply to Thread
Results 1 to 12 of 12

Excel VBA Interception and Implicit Intersection and VLookUp

  1. #1
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Excel VBA Interception and Implicit Intersection and VLookUp

    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 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 )
    Row\Col
    A
    B
    C
    D
    E
    1
    2
    3
    4
    5
    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. )
    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.
    A1 A1 A1 A2 A2 A2
    A1 A1 A1 A2 A2 A2
    A1 A1 A1 A2 A2 A2
    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.

    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)
    A1 B1
    A2 B2
    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.

    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.
    Last edited by Doc.AElstein; 02-08-2017 at 07:19 PM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  2. #2
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Excel VBA Interception and Implicit Intersection and single Breadth Arrays

    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
    A1 B1
    A2 B2
    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”.
    B2 B2 B2 B2
    B2 B2 B2 B2
    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
    ( A1 is the value in cell A1, A2 is the value in cell A2 from the spreadsheet we “see”. The above screenshot is my imaginary help matrix. Yellow is the position in the spreadsheet cell for a cell containing the reference, in this case =A1:A2)

    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
    Formulas:
    Row\Col
    A
    B
    3
    {=B1:C1*C2:D2}
    {=B1:C1*C2:D2}
    Excel can be thought as initially returning the arrays, {1, 2 } from B1:C1 and { 3, 4 } from C2:D2, as instructed by the CS Entry, and then finally it does these evaluations
    ______ 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

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Example: VLookUp and Intercept and Implicit Intersection for single breadth ranges

    Example: VLookUp and Intercept and Implicit Intersection for single breadth ranges
    .

    I am not too convinced that this is such a good example as I cannot see any advantages. But possibly I am wrong. If it does have, for example, speed advantages over a normal formula then some of my theories are probably out of wach.
    But as this is often given as an example, I will demo it. Any comments or any better examples of using a formula such that it uses Implicit Intersection for single “breadth” ranges would be very welcome.

    I will not go into great detail of the basic VLookUp formula and typical forms in this post. ( I have done that in the next post for revision and reference ) . In this post I will start with three columns holding 3 typical versions of the formula working on the same Look Up values and Look Up Tables, and then add a forth, the often given Implicit Intersection version.

    So in the Window below are the first of a 7 row VLookUp formula in 3 versions in columns B C and D. Column E will then be considered for the Implicit Intersection version.
    Please Login or Register  to view this content.
    _...........................

    The characteristic , or as I would say the By product, of Intercept or Implicit Intersect for a single “breadth” Array that we utilise here is the returned single value in certain cells for a reference such as =A3:A10.
    It might slowly become apparent that I did not randomly pick the rows to be used for the normal formulas. Considering the imaginary help matrix discussed in post 2 for the reference = A3:A10, we will have for that help matrix corresponding to having the formula in any cell ( other than cells A3 through to A10 ) the following:
    Please Login or Register  to view this content.
    So, the outcome of this is that a reference, such as that in the first argument of our VLookUp formula, se to =A3:A10 will return ( without CS Entry ) the Look Up value we require. So this formula
    =VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE) or =VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
    in rows 3 to 10 in any spare column, such as column E will give the same results as in the other columns.

    The argument for using Implicit Intersection in such a way is that it is very quick.

    But I do not see that it is quicker than the formulas in column B

    May be I will.












    https://www.mrexcel.com/forum/excel-questions/806702-visual-basic-applications-evaluate-range-vlookup-3.html#post3951876
    http://www.excelforum.com/showthread.php?t=1154829&page=12#post4562694
    Attached Files Attached Files
    Last edited by Doc.AElstein; 02-03-2017 at 07:16 PM.

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Excel VLookUp. VLookUp Review. Simple formula and CSE

    Excel VLookUp. VLookUp Review. Simple formula and CSE



    This Post is to support the last Post. It is a review of a simple application of VLookUp.
    The VLookUp is reviewed in
    _1) Simple single value first argument form
    _2) Multiple value first argument form

    The example used is the same as that used in the demo of Intercept and Implicit Intersection for single breadth ranges in VLookUp in the last Post

    _1) Simples use of VLookUp ( non CSE )
    _1a) Single formula, like written in cell B3 this: = VLOOKUP(A3,$A$16:$C$33,3,FALSE)
    Consider the final Screenshot below where the first 10 rows are to be used for our VLookUp formulas, and the range A16:C33 is the Look up table used in the VLookUp formula. ( It might help if you copy that to a spare worksheet when following the explanations. Take care to copy to the correct range area. Alternatively the worksheets “XLORX” and “Intercepts” in the uploaded file have a mixture of the various formulas and tables )

    Just to refresh what is going on: VLookUp is:
    https://support.office.com/en-us/art...8-93a18ad188a1
    Simplified VLOOKUP function says,
    _ =VLOOKUP( argument1 , argument2 , argument3 , argumant4 )
    _ =VLOOKUP( argument1 ,
    argument2 ,
    argument3 ,
    argumant4 )
    _ = VLOOKUP(A3 , $A$16:$C$33 , 3 , FALSE)
    _ = VLOOKUP(A3 ,
    $A$16:$C$33,
    3,
    FALSE)
    _ =VLOOKUP(Value you want to look up,
    range where you want to lookup the value,
    the column number in the range containing the return value,
    Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE
    )
    or
    [ Put this cell where the formula is ] equal to VLookUp(What is in a column to the right of the same word as is written here in the first column looking_..... ,
    _..... Look in this Look Up Table ,
    use this column number counting from the left in that Look Up Table ,
    False option here means look for the exact match in the words here in the formula first argument and in the first column in the Look Up Table )
    or
    = VLookUp(What is in a column to the right of the same word that is written here in the first column in.. ,
    Look in A16:C33 ,
    use this column number counting from the left in A16:C33 ,
    False )
    or
    [ I want returned here_.... ] = VLookUp( Use this word ,
    Find it here A16:C33 ,
    _...................return what is in this column number counting to the right ,
    False )

    _..................

    or
    just look at the screen considering the rows shown in red and follow this single formula through with the following explanation:
    ____ = VLOOKUP(A3,$A$16:$C$33,3,FALSE)
    ____ = VLOOKUP(LookUpValue,LookUpTable,ColumnNumber,0)
    _0)_ Following on from the first post we will recognise a single cell reference in the formula, =A3. Alan Interception theory gives for that, very quickly, the word Chocolate-europe aroma
    This first argument, A3 ín the above example, is normally a single value. The whole point of our discussions in this thread is how we can go about using multiple values in that Function.
    A note to the second argument: In general for the VLookUp Function, the second argument is taken as a fixed Array which the coding behind the function gets if it is a reference. It likely does use processes we have discussed, but that is not relevant to our discussions here: As far as our discussions are concerned it is a fixed array looking like the Look Up table, so like the following could be typed in instead of $A$16:$C$33
    {"Haselnuß-Walnuß-aromatisiert","","";"Tiramisu","","2";"Chocolate-colonial blend","","";"Chocolate-europe aroma","","4";"Chocolate-Cookies","","";"Jamaica Sun","","6";"Himbeere-Joghurt","","";"Erdbeere-Quark","","8";"Erdbeere-Mix","","";"Banana-Chocolate-Split","","10";"Waldbeeren","","";"Kirsche","","12";"Kirsche-grüner Apfel","","";"Kirsche-Ananas","","14";"Stracciatella","","";"Limette-Käsekuchen","","16";"grüner Apfel-Quark","","";"Blutorange-Quark","",""}

    Back to the example workthrough:
    _0(i)_ In that cell where the formula is ( B3 ) , I put a number from the Look Up Table
    _0(ii)_ To get the number I
    _0(ii)a)_ Look for the word match of the word from the first VLookUp argument, Chocolate-europe aroma to the words in the LookUpTable, A16:C33.
    _0(ii)b)_ Once found, look to the right in the same row in column 3
    _0(ii)c)_ return the value in that column, 4
    _0(ii)d)_ The returned value of 4 is what the formula returns on hitting Enter

    _....
    1b) Multiple Formula use in typical Table of several rows. ( A3 is a relative reference! )
    Often, as in the final screenshot we are interested in getting a column of results.
    The formula is using the column letter and row number cell notation , like A1, A16:C33. &D3 etc. As noted the second argument is generally taken as the fixed Look Up Table.
    The use of the $ in Excel in a reference like, $A$16:$C$33, fixes the range which is then known as an absolute reference. It would only change in a situation when, for example, you insert a row before the Look Up Table, such as row 11 in the screenshot. In this case Excel would guess that you still want to a use the same Look Up Table, and so would change the range to $A$17:$C$34.
    Although not immediately obvious, a reference like A3 is defined as a relative reference. So A3 is referencing the cell relative to the position of where ( the cell ) the reference is in. Excel is just written that way. It is not always stated at an early stage in learning. So the point is often lost as to what happens if the formula is copied to, for example, one row down. If this is done the reference held by Excel remains relative to the cell it is in That is to say it refers in the case of A3, when place in C3, to the cell in the same row, but two columns offset to the left.
    Hence if the formals are copied , ( or dragged http://www.excelfox.com/forum/showth...t-99#post10024 ) down, then you will get like
    VLOOKUP(A3,$A$16:$C$33,3,FALSE)
    VLOOKUP(A4,$A$16:$C$33,3,FALSE)
    VLOOKUP(A5,$A$16:$C$33,3,FALSE)
    etc: .. you see we preserve the relative column position of 2 to the left. The row was in the same ( so relatively speaking 0 rows up or down from ) row in which the formula is in.
    _1b) is a very typical use of the formula.

    _2) Multiple value first argument form
    The same results can be achieved with the use of multi values in the function in the place of where a single value argument is used. We mentioned that Excel usually lets us do that.
    _2b) Multi range reference argument. ( CSE )
    This was discussed in the first couple of posts as the usual way to get multi values out in such a spreadsheet usage.
    Type 2 CSE Entry is required as follows
    _2b(_a)_ select a 8x1 range in the worksheet, such as C3:10
    _2b(_b)_ Hit F2 or clicked in the formula bar
    _2b(_c)_ Type in this formula VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)
    _2b(_d)_ Hold down Ctrl + Shift , then hit Enter ( or hit simultaneously Ctrl+Shift+Enter)

    _2a) Multi value Array of values.
    We can simply use this formula,
    VLOOKUP({"Chocolate-europe aroma";"Chocolate-Cookies";"Banana-Chocolate-Split";"Limette-Käsekuchen";"Erdbeere-Quark";"Erdbeere-Mix";"Jamaica Sun";"Waldbeeren" }$A$16:$C$33,3,FALSE)
    As discussed in the first couple of posts, the general CS Entry is not now required to get the ( single in this case ) Controlled Shift , as the Array of values is already there, but The type 2 CS Entry is never the less required once again to extend the area effectively “revealed” to get the extended available values


    _..............................

    First Row of formulas
    (__¬___ Formula in B3 is to be dragged down _____ Column C formula is for type 2 CS Entry
    Row\Col B C
    3 =VLOOKUP(A3,$A$16:$C$33,3,FALSE) = VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)


    Example Look Up Values in Column A and returned values from Formula in column B and C
    Please Login or Register  to view this content.
    Look Up Table
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Doc.AElstein; 02-03-2017 at 07:14 PM.

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Using Spreadsheet Functions to return multiple values when they usually are used to return

    Spreadsheet Functions to return multiple values when they usually return just one and VLookUp

    Extending the background work on VLookUp
    I am looking generally at getting multiple values out of a Worksheet formula, in particular for formulas that might not generally be used for or thought of as doing that. I am putting some emphasis finally on the case of VLookUp as it is a bit quirky and does not quite follow the trend of what usually happens.

    In many cases Functions, especially worksheet Functions, which usually take one or more of their arguments as single values will accept in place of those arguments multiple values as range address reference or an Array of values, see here for example:
    http://www.excelforum.com/showthread...95#post4571172

    Often this will result in multiple answers, in place of the single answer for the case of the normal single value arguments case.
    The simplest form and analysis and explanation of this is as follows:
    In most things that Excel does relating to a spreadsheet, or at the occurrence of most events , a process of analysis sets off as a result of which the entire worksheet is updated. It sets off a progression along some effective 2 dimensional grid system , starting at top left, then along the columns, then down a row and repeats again from the left, eventually arriving at bottom right.
    Synchronisation of this to offset memory locations results in what we “see” on our computer screen. http://www.excelfox.com/forum/showth...on-and-VLookUp
    A by Product of this is that when a usually single value argument is given multiple values the evaluation process is effectively perverted such that the evaluation is done at a “time” or point of the progression offset by an amount proportional to the offset in the range or the Array of the member values http://www.eileenslounge.com/viewtopic.php?f=4&t=22534 The end result of that is that Excel has the multiple answers “available” somewhere/ somehow in a matrix or array of dimensions mirroring the maximum dimensions of any Arrays or ranges used in the place of usually single value arguments.

    ==In a Spreadsheet:==
    The process to allow this to happen and “see” the “available” values in a spreadsheet seems fairly well understood involving a mechanism for
    _ somehow doing effectively a Controlled Shift before Enter adjustment adjusting ( CSE Type 1 ) .
    _ In addition, one can effectively place the formula in a range of cells which if chosen appropriately will reveal all the “available” values ( CSE type 2 ). http://www.excelfox.com/forum/showth...alue#post10038 http://www.excelfox.com/forum/showth...0061#post10061

    Without this process it is not always straight forward to get the multi value output results:

    In a famous blog here: https://excelxor.com/2014/09/05/inde...ray-of-values/ a way is shown to get multiple values from a VLookpUp Function without CS Entry

    I am having a look at that a bit here:
    Consider a simple VLookUp Function example use of:
    This could be the “Look Up Table”
    Row\Col
    I
    J
    K
    22
    A
    -
    1
    23
    B
    -
    2
    24
    C
    -
    3
    VLookUp Function is, in the usual single value argument option, then used to return the number 1, 2, or 3, depending on whether you give the function A, B, or C.
    The first single argument then would be, for example A. This is the “Look Up Value”
    The second argument is that Look Up Table ( which is a fixed array always, and not part of our “multi value ( array ) in place of usual single value discussions )
    The third argument simply defines the column number used in the Look Up Table for the output, ( in this case 3 )
    The last argument takes a 0 or False as meaning only accept an exact match to the Look Up value. ( 1 or True allows the nearest match to be accepted )
    ____ = VLOOKUP(LookUpValue,LookUpTable,ColumnNumber,0)
    So this pseudo like thingy would be our example
    ____ 1 = VLookUp(“A”, I22:K24, 3, 0)
    or
    ____ 1 = VLookUp(I22, I22:K24, 3, 0)

    In most cases, as is here, CS Entry of the formula in such a form _..
    VLookUp({“A”; “B”; “C”}, I22:K24, 3, 0) or VLookUp(I22:I24, I22:K24, 3, 0)
    _..will give us an Array of values out in the orientation of the first argument,
    {1; 2; 3}.
    By the way, this, ; , is the Excel convention for “vertical” or “column” orientation so we effectively have this:
    1
    2
    3
    ( Remember I22:K24 is always our fixed Look Up table. This could be replaced with
    { " "A","-","1";"B","-","2";"C","-","3"} , which is effectively “looking” like:
    A - 1
    B - 2
    C - 3 )

    Things are not always so straight forward as they usually are in using CS Entry, and even the best people are not quite sure why.....

    ==“Evaluate” outside the spreadsheet==
    I would argue the distinction between in a spreadsheet and similar things “outside” like in a VBA code is fine as the basic processes are probably the same... But anyway:
    We have 2 other ways of “evaluating” such a formula. They are both, I think, similar in how they work. They both use the same basic processes as in a spreadsheet after hitting Enter, or, I expect, they actually go straight into doing the CS Enter. ( In the spreadsheet you may do the CS Entry in any situation where you would do just Enter. It does no harm. It may involve doing unnecessary extra work. I don’t know )
    _(i) One way - F9:
    If you select a part of a formula or function in the formula bar and hit F9, you will get an “instant” evaluation. – The formula should change to show you a single value or array of values as appropriate for what you “evaluated”
    _(ii) Another way - VBA Evaluate Method:
    In most cases we may use the Evaluate(“ “) function in VBA to do “evaluations” as if we had the thing between the quotes, “ “ in a cell. ( The fact that we have Evaluate(strEval), where strEval is a String is very useful. We can then not only include all the text including formulas and functions that you would type in a cell, but also VBA things. So our formula could be written in a VBA code such
    Dim ColumnIndex As Long
    _Let ColumnIndex = 3
    strEval = "=VLookUp(I22, I22:K24, " & ColumnIndex & ", 0)"

    As mentioned, the VLookUp is a case which is not straight forward. So first it might be wise to go back and review a case that is more well behaved. Then we will come back to the VLookUp case..

    http://www.excelfox.com/forum/showth...0166#post10166


  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Multi value return for simple Formula. Case Typical is Rept(str, how_many_times)

    Spreadsheet Functions to return multiple values when they usually return just one and here is with
    Rept(str, how_many_times)
    ( In the over next post is an accompanying code. Any green numbers given in the text are approximate line numbers of accompanying parts

    _.. This is just to review a fairly usual case, before looking at the VLoookUp case, which we will see is a bit unusual.
    _.. So here a review of a more straight forward example, the Rept(str, how_many_times) Function:
    ( _.. typical normal use to get “AA” __ = Rept(“A”,2) )

    ==In Spreadsheet==
    _ Put this =REPT({"A";"B"},2) or this =REPT(I22:I23,2) in any cell. ( The second will, unless in a cell allowing Implicit Intersection, error. http://www.excelfox.com/forum/showth...on-and-VLookUp http://www.excelforum.com/showthread...95#post4571173 Hold my implicit offset intersexual intercept excepting intersect automagically wonks. – Don’t worry about that for now )

    ==“Evaluate” outside the spreadsheet==
    _(i) F9
    Select either of those formulas above in the formula bar and hit F9, ( You can even use the one which errors).
    You should then see this_.....
    {"AA";"BB"}
    _........instead of the formula , which is like “seeing” this
    AA
    BB
    ( Remember after to hit Esc or Ctrl+z to get back to formula view )
    It appears that F9 does something similar to CS Entry type 1 or 2. ( Doing CS E type 2 is required in the spreadsheet for the second formula =REPT(I22:I23,2) to do the Controlled Shift and get all values seen for the first formula. CS type 2 Entry is also required in the spreadsheet for the first formula , but only to get all the values seen )

    _(ii) Looking again in VBA Evaluate(“ “)
    ( Full codes here: http://www.excelforum.com/developmen...ml#post4596154 ( Post #200 ) )
    It would appear that Evaluate(“ “) will do a “single cell evaluation” usually. Somehow that is basically what it does. However, it would appear that the basic processes are likely carried out somehow such that the Arrays {"A";"B"} and I22:I23 are available, possibly. This is unclear.

    We do not have the need, or so it appears, for Controlling a Shift before an Enter. This is reasonable as we are not “in a cell”, so do not have the Intersection “revealing” going on. So we see that both Evaluate("=REPT({""A"";""B""},2)") and Evaluate("=REPT(I22:I23,2)") do not error.
    However we only get the first value. ( up to code line 100 ). So it is not clear if the multi values are there or not. We do not have an available Controlled Shift process as it is not necessarily needed in the first instance.
    But the basic process is likely available somehow.
    That is to say, within any process be it in VBA or Excel the “along the columns then down to next row “ is available. If such a process were “running”, then returned output multiple values appear in an Array to match the orientations and largest dimensions of Arrays within the formula or function being “run”. In this case a 2 “row” x 1”column” Array would be returned. During the “running”, at each position for the output array being considered, any available position in any other arrays will be used. Therefore in order to get out Function to return that Array, we just need to add something to the complete equation that does not affect the answer but causes that process to take place
    This is all very important stuff, that no one appears to understand fully, or is sure about.
    So we will say the same thing again, just slightly differently:
    Although not a complete 1 to 1 translation, the Evaluate(“ “) is generally intended to do the processes in Excel that would take place in a Spreadsheet. Because we are not physically in a spreadsheet we do not require the “extraction” type process of Controlled Shifting before individual value like Entering. This explains why we have no error as in a non implicit Intersection spreadsheet entry.
    The determination generally of whether multiple values are obtained in Evaluate(“ “) is generally thought to be based on if the formula itself usually returns multiple values. Our Rept( , ) Function isn’t. So it doesn’t.

    So (i) F9 and (ii) Evaluate(“ “) are not quite doing the same thing

    Unfortunately before going further a transgression is necessary so as not to confused in taking the “(i) F9 and (ii) Evaluate(“ “)” story further.
    ' '_- Asside:
    Excel and Range referencing.
    A long standing curiosity in Excel VBA is, ( was ), Why VBA Evaluate does not work on a closed Workbook reference. http://www.eileenslounge.com/viewtop...=25213#p202227
    This can be explained.( I did have I done )
    We need to consider an understand Excel VBA Interception and Implicit Intersection
    http://www.excelfox.com/forum/showth...0061#post10061
    http://www.excelforum.com/tips-and-t...ml#post4575459

    I only introduced the concept recently so it is no surprise the original question of “Why VBA Evaluate does not work on a closed Workbook reference”, was difficult to answer.
    When placed in a spreadsheet processes are under way which speed up the updating of values in the spreadsheet. The references above explain this in some detail to an extent not discussed previously. To function, this process requires evaluation of the cell value. Excel is written to do that when after seeing a = in a cell.
    “Behind” every cell is a Range object from which this information is retrieved.
    http://www.excelforum.com/the-water-...ml#post4586265
    http://www.excelforum.com/developmen...ml#post4551080
    http://www.excelforum.com/developmen...ml#post4563838
    https://powerspreadsheets.com/excel-vba-range-object/

    In the cell this value ( specifically the .Value Property ) is returned, as indeed is returned as the default Property for many uses of the Range object. This last point has possibly some more relevance to Excel Generally.

    Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such, Externally referencing a cell such, Cel, cel as a Range object is External to the cel, sort of Ex cel, or Excel

    I believe that in a cell a string range reference , is recognised as such. I believe when a string address such as M40:M41 is written in a cell in a worksheet “MySheet” in a File, “MyWorkbook.xlsx”, then this defaults to a string reference of such a form
    strRef ='[MyWorkbook.xlsx]MySheet'!A1
    Or for a Worksheet named "XLORX", such a form is “seen” by Excel
    270 strRef ='[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets("XLORX").Name & "'!M40:M41
    There are many ways to demonstrate this.
    '_- Fundamental looking at Range object properties and referring to ranges and spreadsheet cells is one way:
    http://www.excelfox.com/forum/showth...eadsheet-cells
    http://www.excelforum.com/tips-and-t...eet-cells.html

    '_- Index Example way
    ' '_- Index way of looking at it: Code lines 400 - 560
    Another way , I would suggest, is to look more closely at how a cell, cel As Range object, is handled by Externally showing it, for example on a spreadsheet, “in Ex cel” or “Excel” as it were.
    An Excel spreadsheet is arranged by default to show the value, ( .Value ) as indeed is required for Intersexual Interception Alan Theory. It probably helps in the coding to have this as the objects default given property. It appears to be that given in most cases when the range object is “presented” where one might consider a type mismatch error should occur, ( but by virtue of this default does not produce an error ) .
    I would suggest that in fact fundamentally, it is a range Object which Excel is “holding” when such a reference is given ( full or in shortened address may be given, but Excel “sees” the full, guessing the default extra string section ).
    I would suggest that therefore Evaluate(strRef) is fundamentally returning a Range object, despite that we see a value ( .Value ) in the spreadsheet. In the demo code we can see how The Evaluate(“ “) of a spreadsheet function, Index, which clearly in all spreadsheet use, does in fact give us a range Object in Evaluate.
    ( I would finally suggest that in fact Range(“ “) in VBA is using the same or very similar process to Evaluate(“ “) but restricted to, or optimised to just working on the strRef as the Evaluate String, strEval. That is to say it will only accept a string reference as argument. This is in agreement with the general documentation.
    http://www.excelforum.com/excel-prog...ket-for-2.html


    _........................
    That last was a bit of an aside, necessary not to get confused, with some aspects discussed now as we go further with the “(i) F9 and (ii) Evaluate(“ “)” story


    Recap: _(i) F9
    Select either of those formulas above in the formula bar and hit F9, ( Even the one which errors).
    You should then see this_.....
    {"AA";"BB"}
    _........instead of the formula , which is like “seeing” this
    AA
    BB
    ( Remember after to hit Esc or Ctrl+z to get back to formula view )
    It appears that F9 does something similar to CS Entry type 1 or 2. ( Doing CS E type 2 is required for the second formula to do the Controlled Shift and get all values seen for the first formula. CS Entry is also required for the first formula , but only to get all the values seen, for the first value )

    We had no problem to get our multiple output.. but

    ' '_- Evaluate multivalues 1 ( Back To is now )
    _(ii) Looking again in VBA Evaluate(“ “)
    ( Full codes lines from 570 : http://www.excelforum.com/developmen...ml#post4596154 ( Post #200 ) )

    It would appear that Evaluate(“ “) will do a “single cell evaluation” usually. Somehow that is basically what it does. However, it would appear that the basic processes are likely carried out somehow such that the Arrays {"A";"B"} and I22:I23 are available, possibly. This is unclear. _...............
    _.................
    _.......... The determination generally of whether multiple values are obtained in Evaluate(“ “) is generally thought to be based on if the formula itself usually returns multiple values. Our Rept( , ) Function isn’t. So it doesn’t.
    So (i) F9 and (ii) Evaluate(“ “) are not quite doing the same thing


    In general there is no problem with something of this form
    580 strEval = ={1;2}+{3;4;5} '_- Spreadsheet
    Evaluate(={1;2}+{3;4;5}) '_- VBA
    ( The only thing to note for the Spreadsheet case, is that, (whilst no Intersection is done leading to no error), we would still require Controlled Shifting before Entering over a 1 “wide” by 3 “deep” spreadsheet area to get returned
    4
    6
    error
    ( or {4;6;error} )
    In VBA, we get directly {4;6;error} returned in a variant type array of dimensions (1 to 3, 1 To 1)
    We also understand from the last aside that this_..
    Evaluate("=M40:M42")
    _.. is a special case, and that we will get a Range object or an array depending in how we declare the receiving variable.

    Some other following examples are not immediately obvious:
    This _..
    = Evaluate("={1;2}+M40:M42")
    _.. returns {4;6;error}
    Or
    4
    6
    error
    Whereas this:
    = Evaluate("={1}+M40:M42")
    _.. returns {1;1;1}
    Or
    1
    1
    1
    and _..
    = Evaluate("=1+M40:M42")
    _.. returns {1;1;1}
    Or
    1
    1
    1
    and _..

    Evaluate("={1,2}+M40:M42")
    _.. returns {1,2;1,2;1,2}
    Or
    1 _ 2
    1 _ 2
    1 _ 2

    Oh, God, another aside: We are demonstrating here again Interception Theory, which describes that Excel and VBA has a values help matrix for value ( or value from reference in a spreadsheet cell ) which for the case of
    single value http://www.excelfox.com/forum/showth...on-and-VLookUp http://www.excelforum.com/tips-and-t...d-vlookup.html
    and
    single “breadth” multi values http://www.excelfox.com/forum/showth...okUp#post10062 http://www.excelforum.com/tips-and-t...ml#post4575460

    ( Note in the last examples, Excel does not see a cell reference for those M40:M42 occurrences. They appear in a formula. The values are therefore taken. We are not dealing with the special case here

    The above can be summarised into a general Evaluate(“ “) process, which is slightly different to the F9 case which appeared to do some pseudo CSE type 1 or 2 process. We are saying different here, as right at the outset the problem appeared to be that only the first value was obtained.
    Our last experiments are suggesting the following summary can be given:

    A process appears to be going on in Evaluate(“ “), or rather possibly some general calculation in VBA, whereby a given formula will be repeated in a “all columns, then down to next row” sequence for as many times as there are maximum dimensions of “seen” arrays in a calculation protocol ( formula ). Interception Theory is integral to this working, allowing a single value to be taken as many times as there are final dimensions in the maximum dimensions. ( The output is given in an Array equal to those maximum dimensions ).

    We follow in the over over next section with getting our Rept(str, how_many_times) with multiple values to work. I forgot why.

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that.,

    Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such


    ( For here : http://www.eileenslounge.com/viewtopic.php?f=30&t=25213&p=202227#p202227 )


    ' '_- Asside:
    Excel and Range referencing.
    A long standing curiosity in Excel VBA is, ( was ), Why VBA Evaluate does not work on a closed Workbook reference. http://www.eileenslounge.com/viewtopic.php?f=30&t=25213#p202227
    This can be explained.( I did have I done )
    We need to consider an understand Excel VBA Interception and Implicit Intersection
    http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp?p=10061#post10061
    http://www.excelforum.com/tips-and-tutorials/1172587-excel-vba-interception-and-implicit-intersection-and-vlookup.html#post4575459

    I only introduced the concept recently so it is no surprise the original question of “Why VBA Evaluate does not work on a closed Workbook reference”, was difficult to answer.
    When placed in a spreadsheet processes are under way which speed up the updating of values in the spreadsheet. The references above explain this in some detail to an extent not discussed previously. To function, this process requires evaluation of the cell value. Excel is written to do that when after seeing a = in a cell.
    “Behind” every cell is a Range object from which this information is retrieved.
    http://www.excelforum.com/the-water-cooler/1174400-would-like-to-know-about-the-forum-experts-gurus-2.html#post4586265
    http://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-11.html#post4551080
    http://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-13.html#post4563838
    https://powerspreadsheets.com/excel-vba-range-object/

    In the cell this value ( specifically the .Value Property ) is returned, as indeed is returned as the default Property for many uses of the Range object. This last point has possibly some more relevance to Excel Generally.

    Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such, Externally referencing a cell such, Cel, cel as a Range object is External to the cel, sort of Ex cel, or Excel

    I believe that in a cell a string range reference , is recognised as such. I believe when a string address such as M40:M41 is written in a cell in a worksheet “MySheet” in a File, “MyWorkbook.xlsx”, then this defaults to a string reference of such a form
    strRef ='[MyWorkbook.xlsx]MySheet'!A1
    Or for a Worksheet named "XLORX", such a form is “seen” by Excel
    270 strRef ='[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets("XLORX").Name & "'!M40:M41
    There are many ways to demonstrate this.
    '_- Fundamental looking at Range object properties and referring to ranges and spreadsheet cells is one way:
    http://www.excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells
    http://www.excelforum.com/tips-and-tutorials/1172578-understanding-vba-range-object-properties-and-referring-to-ranges-and-spreadsheet-cells.html

    '_- Index Example way
    ' '_- Index way of looking at it: Code lines 400 - 560
    Another way , I would suggest, is to look more closely at how a cell, cel As Range object, is handled by Externally showing it, for example on a spreadsheet, “in Ex cel” or “Excel” as it were.
    An Excel spreadsheet is arranged by default to show the value, ( .Value ) as indeed is required for Intersexual Interception Alan Theory. It probably helps in the coding to have this as the objects default given property. It appears to be that given in most cases when the range object is “presented” where one might consider a type mismatch error should occur, ( but by virtue of this default does not produce an error ) .
    I would suggest that in fact fundamentally, it is a Range object which Excel is “holding” when such a reference is given ( full or in shortened address may be given, but Excel “sees” the full, guessing the default extra string section ).
    I would suggest that therefore Evaluate(strRef) is fundamentally returning a Range object, despite that we see a value ( .Value ) in the spreadsheet. In the demo code we can see how The Evaluate(“ “) of a spreadsheet function, Index, which clearly in all spreadsheet use, does in fact give us a range Object in Evaluate.
    ( I would finally suggest that in fact Range(“ “) in VBA is using the same or very similar process to Evaluate(“ “) but restricted to, or optimised to just working on the strRef as the Evaluate String, strEval. That is to say it will only accept a string reference as argument. This is in agreement with the general documentation.
    http://www.excelforum.com/excel-programming-vba-macros/1141369-evaluate-and-differences-evaluated-array-return-needs-extra-bracket-for-2.html
    )

    Conclusion
    The end result as seen in the code lines in the next Post is that for a single Range object reference , ( noting this can be multi spreadsheet Areas of contiguous cells ) , we are able to get a Range object returned if the receiving variable is declared ( Dimed ) appropriately. This lead to my argument that Excel “holds” fundamentally from a string ( as simple address given!! ) reference in a cell a Range object. ( !!The string reference is increased to include the default Worksheet and Workbook if not given )
    Should the variable be otherwise declared, the .Value default appears to apply. As such the .Value Property applied to the Range object will return for a single cell Range object an appropriately dimensioned variable, or for the case of a Range object of more than one constituent cell, a Field ( Array ) of variant types, ( defaulting to the first Area of the Range object if no Areas item number is given . Once concatenated with anything else, Excel takes the value for the reference.
    We look at all this now in more detail in the over next post.


    https://www.excelforum.com/tips-and-tutorials/1172587-excel-vba-interception-and-implicit-intersection-and-vlookup.html#post4605315

    http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp?p=10167#post10167
    http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp?p=10168#post10168


  8. #8
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Codes for discussions of last few posts: Rept(str, how_many_times) multivalue return want

    Codes for discussions of last few posts: Rept(str, how_many_times) multivalue return want Wonk I do have


    Please Login or Register  to view this content.
    Last edited by Doc.AElstein; 03-14-2017 at 08:36 PM.

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Getting our Rept(str, how_many_times) with multiple values to work. I forgot why.

    Getting our Rept(str, how_many_times) with multiple values to work. I forgot why.

    Killings Interception: Suspending, ( or stopping at the first of ), Multi value return analysis.

    Remove the Excuse for an Abortion

    Using the same screen shots data as the last posts and running through some more_.....
    Row\Col
    I
    J
    K
    22
    A
    -
    1
    23
    B
    -
    2
    24
    C
    -
    3
    Row\Col
    M
    40
    41
    _..... code lines to demo... ( Code in next post, Matey boy ( or Girly )

    Rem 1 Going again to find that we only get one value from Evaluate(“=REPT({"A";"B"},2)”), which is “AA”

    Rem 2 Trying to get both possible values out.
    We have already established , via CSE or F9 in formula bar, that we have two values available. But how do we get them?

    '2a) review Excel VBA multi value analysis
    We discussed in detail in the aside, ( ' '_- Asside: Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such, Externally referencing a cell such, Cel, cel as a Range object is External to the cel, sort of Ex cel, or Excel ' the fundamental idea of a Excel "holding" a cell, through a string reference, as a Range object http://www.excelforum.com/developmen...ml#post4595462 http://www.eileenslounge.com/viewtop...202227#p202227 ) , that we can choose to get the full
    210 Range object
    or
    220 its values held in ( for more than one cell ) a Field ( Array ) of Variant types.

    230-270 Once Excel does not recognise this as a reference a Formula is recognised and simple range references are taken as appropriate dimensioned Arrays holding their cell values
    The final output will be set to a dimension of pseudo “Open Window” to encompass the largest of any of the Arrays.

    '2a(ii) When there is a mismatch in Array sizes,
    _(i) 280 for example when a 3 x 1 ( 1,1 2,1 3,1 ) and a 2 x 1 ( 1,1 2,1 ) are present, then a fairly predictable result is obtained of an error in the 3, 1 output Array as Excel is trying to concatenate to nothing.

    'Interception
    _(ii) 300 ' Single value .
    Results are less obvious for the case of one Array being a single element, or even if that is replaced by
    320 A single value. In these cases we see a duplicated taken copy of that single value to concatenate rather than as we might of expected an error due to nothing there
    _(iii) 340 ' Single breadth .
    Similarly extending the pseudo “Open Window” such that a single breadth Array has a missing counterpart in the extended breadth will duplicate rather than erroring, ( but only up to the depth ) of the single breadth Array.

    The above results, in particular the last two require the understanding of Intercept Theory which I wrote: 'Interception Theory:
    http://www.excelfox.com/forum/showth...on-and-VLookUp
    https://www.excelforum.com/tips-and-...d-vlookup.html

    In brief , 'Interception Theory: Excel appears_..
    _.. (ii) for the case of a Single value, to “hold” a help matrix of size of all excel spreadsheet cells to speed up interception of that with the effective “open window for intercept”
    and
    _..(iii) for the case of a Single “breadth” Array , to hold duplicated values extending outside the single breadth, “across” as it were all the spreadsheet cells , restricted to the “length” or “depth” of the Array. ( The Latter, (iii) , is responsible for Implicit Intersection ).

    '2b) Attempting concatenations of 2 x 1 Arrays with Rept(__;__ , how_many_times) .
    ('2b(ii) Killing Interceptions )
    The last code section demonstrated that Excel is “opening up a window” or “Array space” to encompass the largest dimensions of all seen Arrays. The point of this section is to try to replace one of the two references in the last code lines with the REPT(I22:I23,2) or REPT({"A";"B"},2), with the hope that somehow Excel would somehow find the I22:I23 or {"A";"B"}. Should this work, then we could concatenate to an empty Range or to {"";""} and so have a successful “coercion”
    It didn’t work
    I had a feeling it would not
    Never mind.
    I expect this is telling me that each section in a & is being evaluated separately.. ( and I am somehow Killing Interception **)
    But, and this is quite interesting, we are getting from REPT({"A";"B"},2) just the first value AA, but if I replace REPT({"A";"B"},2) with AA,
    530, Then I get again my result as explained by Interception Theory, {"AAA";"BAA"}
    550 Going back and using REPT("A",2) in place of REPT({"A";"B"},2) and once again I do get my result as explained by Interception Theory, {"AAA";"BAA"}. Very strange ** Somehow REPT({"A";"B"},2) KILLED interception.
    I do not really know what is going on here , with killing interception. Possibly Excel is starting the process to which Controlled Shifting before Entering . This preliminary step is possibly suspending, ( or stopping at the first of ), Multi value return analysis.
    So maybe we have a new idea : “Killings Interception: Suspending, ( or stopping at the first of ), Multi value return analysis. “ or “An excuse for an abortion

    _._____________


    '2c) Multivalve from REPT({"A";"B"},2)
    One thing is clear:.. It is not at all clear the exact processes that are going on.

    I suspect up until now, a lot of published work, workarounds, are based on empirical measurements. As a general rule, it would appear that including the “Excuse for an Abortion Function” in a complete Formula in which has valid returning multi value properties will somehow retrieve those values. But why it should retrieve the appropriate individual values and not then do a Interception evaluation with the first value ( multiple thereof ) , is not clear..

    Standard Wonks
    In any case we have the ( probably empirically derived ) standard wonks that work in most cases.
    '2c)(i), '2c)(ii), '2c)(iii)
    These are just a few possible ways to incorporate the Excuse for an Abortion Function , REPT({"A";"B"},2) into a total formula that will not change the final result, but includes another function which does in general return multiple values.
    '2c)(i)
    We discussed this here: https://www.excelforum.com/developme...ml#post4595462 . We showed that Index can be used to return multi cell ranges or array of values of contents thereof. In an extreme case we simply use it to return the entire first argument grid. When, as in this case , that forst argument is our Excuse for an Abortion Function, then somehow an extra pre evaluation appears to be going on of the F9 type to bring out the multiple values. The trick is that we have embedded it inside another function... ( So why is that? https://www.excelforum.com/excel-for...ml#post4601913

    '2c)(ii) Transpose
    Once again the expected first argument is expected to be, and wired to look for, and possibly evaluate to, multivalves.

    '2c(iii) If( condition, do it then if condition is True )
    This generally is a two argument If statement resulting in the second argument being done if the first argument condition is met. Once again no one really understands this fully.
    A typical fist argument is Row(). A likely explanation:
    _ In a spreadsheet , I believe this does an ( Inverse ) Intercept case 1 single value type process. In a spreadsheet it reveals the row number in which the formula =Row() is written. In evaluate it has lost some connection in the spreadsheet. In any case, we are just using it to get a True. As such I believe it sort of floats or transgresses all , waiting to be tied down. The use of it in If(__ will always cause the second argument expression to be done. The dimension of the second argument will define the final Output window dimension. Possibly indirectly this returns for Row() an Array of Long numbers greater than 1, which as can be seen in '2c(v), will be taken as 1 or True
    _
    '2c(iv)
    A version of the previous If( condition, do it then if condition is True ), with specific row or column in the argument.
    Here, an Array size is determined by the first argument. This will therefore contribute to the final Open window to encompassing maximum dimensions of all contributing Arrays
    The values in the Array are not important, as long as they are greater than 0 then they are taken as 1 or True ( 930, 935, 970 )


    https://www.excelforum.com/tips-and-...ml#post4605320

    http://www.excelfox.com/forum/showth...0169#post10169
    http://www.excelfox.com/forum/showth...0170#post10170

  10. #10
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Code for last Post

    Code for last Post

    Please Login or Register  to view this content.

  11. #11
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Single value output VLookUp: Used to obtain multiple outputs

    Single value output VLookUp: Used to obtain multiple outputs

    An Example of a Function which normally returns a single value, which can be used to produce multiple values , but which does not follow the usual Trends.

    So, about 5 posts back we ( or I at any rate ) drifted away from the main point that was looking at VLookUp...

    As mentioned , it is a bit of a pain in the bottom as it does not follow the usual trend of things.
    I started looking at this from about here:_...
    https://www.excelforum.com/developme...ml#post4562694
    _... then , with no enlightenment from the Masters, I started looking again from about here:. _...
    https://www.excelforum.com/developme...ml#post4589521

    So where were we.._____ Extending the background work on VLookUp
    I am looking generally at getting multiple values out of a Worksheet formula, in particular for formulas that might not generally be used for or thought of as doing that. I am putting some emphasis finally on the case of VLookUp as it is a bit quirky and does not quite follow the trend of what usually happens.

    In many cases Functions, especially worksheet Functions, which usually take one or more of their arguments as single values will accept in place of those arguments multiple values as range address reference or an Array of values, see here for example:
    http://www.excelforum.com/showthread...95#post4571172

    Often this will result in multiple answers, in place of the single answer for the case of the normal single value arguments case.
    The simplest form and analysis and explanation of this is as follows:
    In most things that Excel does relating to a spreadsheet, or at the occurrence of most events , a process of analysis sets off as a result of which the entire worksheet is updated. It sets off a progression along some effective 2 dimensional grid system , starting at top left, then along the columns, then down a row and repeats again from the left, eventually arriving at bottom right.
    Synchronisation of this to offset memory locations results in what we “see” on our computer screen. http://www.excelfox.com/forum/showth...on-and-VLookUp
    A by Product of this is that when a usually single value argument is given multiple values the evaluation process is effectively perverted such that the evaluation is done at a “time” or point of the progression offset by an amount proportional to the offset in the range or the Array of the member values http://www.eileenslounge.com/viewtopic.php?f=4&t=22534 The end result of that is that Excel has the multiple answers “available” somewhere/ somehow in a matrix or array of dimensions mirroring the maximum dimensions of any Arrays or ranges used in the place of usually single value arguments.

    ==In a Spreadsheet:==
    The process to allow this to happen and “see” the “available” values in a spreadsheet seems fairly well understood involving a mechanism for
    _ somehow doing effectively a Controlled Shift before Enter adjustment adjusting ( CSE Type 1 ) .
    _ In addition, one can effectively place the formula in a range of cells which if chosen appropriately will reveal all the “available” values ( CSE type 2 ). http://www.excelfox.com/forum/showth...alue#post10038 http://www.excelfox.com/forum/showth...0061#post10061

    Without this process it is not always straight forward to get the multi value output results:

    In a famous blog here: https://excelxor.com/2014/09/05/inde...ray-of-values/ a way is shown to get multiple values from a VLookpUp Function without CS Entry

    I am having a look at that a bit here:
    Consider a simple VLookUp Function example use of:
    This could be the “Look Up Table”
    Row\Col
    I
    J
    K
    22
    A
    -
    1
    23
    B
    -
    2
    24
    C
    -
    3
    VLookUp Function is, in the usual single value argument option, then used to return the number 1, 2, or 3, depending on whether you give the function A, B, or C.
    The first single argument then would be, for example A. This is the “Look Up Value”
    The second argument is that Look Up Table ( which is a fixed array always, and not part of our “multi value ( array ) in place of usual single value discussions )
    The third argument simply defines the column number used in the Look Up Table for the output, ( in this case 3 )
    The last argument takes a 0 or False as meaning only accept an exact match to the Look Up value. ( 1 or True allows the nearest match to be accepted )
    ____ = VLOOKUP(LookUpValue,LookUpTable,ColumnNumber,0)
    So this pseudo like thingy would be our example
    ____ 1 = VLookUp(“A”, I22:K24, 3, 0)
    or
    ____ 1 = VLookUp(I22, I22:K24, 3, 0)

    In most cases, as is here, CS Entry of the formula in such a form _..
    VLookUp({“A”; “B”; “C”}, I22:K24, 3, 0) or VLookUp(I22:I24, I22:K24, 3, 0)
    _..will give us an Array of values out in the orientation of the first argument,
    {1; 2; 3}.
    By the way, this, ; , is the Excel convention for “vertical” or “column” orientation so we effectively have this:
    1
    2
    3
    ( Remember I22:K24 is always our fixed Look Up table. This could be replaced with
    { " "A","-","1";"B","-","2";"C","-","3"} , which is effectively “looking” like:
    A - 1
    B - 2
    C - 3 )

    Things are not always so straight forward as they usually are in using CS Entry, and even the best people are not quite sure why.....

    ==“Evaluate” outside the spreadsheet==
    I would argue the distinction between in a spreadsheet and similar things “outside” like in a VBA code is fine as the basic processes are probably the same... But anyway:
    We have 2 other ways of “evaluating” such a formula. They are both, I think, similar in how they work. They both use the same basic processes as in a spreadsheet after hitting Enter, or, I expect, they actually go straight into doing the CS Enter. ( In the spreadsheet you may do the CS Entry in any situation where you would do just Enter. It does no harm. It may involve doing unnecessary extra work. I don’t know )
    _(i) One way - F9:
    If you select a part of a formula or function in the formula bar and hit F9, you will get an “instant” evaluation. – The formula should change to show you a single value or array of values as appropriate for what you “evaluated”
    _(ii) Another way - VBA Evaluate Method:
    In most cases we may use the Evaluate(“ “) function in VBA to do “evaluations” as if we had the thing between the quotes, “ “ in a cell. ( The fact that we have Evaluate(strEval), where strEval is a String is very useful. We can then not only include all the text including formulas and functions that you would type in a cell, but also VBA things. So our formula could be written in a VBA code such
    Dim ColumnIndex As Long
    _Let ColumnIndex = 3
    strEval = "=VLookUp(I22, I22:K24, " & ColumnIndex & ", 0)"


    Once again, same screen shots_..
    Row\Col
    I
    J
    K
    22
    A
    -
    1
    23
    B
    -
    2
    24
    C
    -
    3
    Row\Col
    M
    40
    41
    _...My first problems with VLookUp and Range Evaluate, started here: https://www.mrexcel.com/forum/excel-...e-vlookup.html
    The usual Evaluate(“ “) trends discussed in the previous posts ( using Rept Function as example ) did not seem to work in this case.

    _.....
    So , here we go with VLookUp: Our three similar Excel / VBA Processes
    _.. CSE
    _ .. F9 Formula bar
    _ .. Evaluate(“ “)



    _ .. Reviewing CSE ( Type 2 ) ( Line Numbers for code in over next post )
    Using an arbitrary 3 row x 1 column “Window” selected before _..
    F2 ( or click in formula bar )
    Paste in formula
    CSE

    Result: ... All is well
    Row\Col
    I
    J
    K
    L
    M
    N
    22
    A
    -
    1
    1
    1
    1
    23
    B
    -
    2
    2
    24
    C
    -
    3
    3
    Row\Col
    I
    J
    K
    L
    M
    N
    22
    A
    -
    1
    =VLOOKUP({"A","B","C"},I22:K24,3,0)
    =VLOOKUP(I22:I24,I22:K24,3,0)
    =VLOOKUP(I22:I24, I22:K24, 3, 0)
    23
    B
    -
    2
    =VLOOKUP(I22:I24, I22:K24, 3, 0)
    24
    C
    -
    3
    =VLOOKUP(I22:I24, I22:K24, 3, 0)

    ( Note in the above screenshot Implicit Intersection is responsible for the effective Formula in H22 of =VLOOKUP({I22,I22:K24,3,0) , that is to say =VLOOKUP({"A"},I22:K24,3,0) )
    So, so far so good.. In the next two variations of the similar processes _ ..
    _ .. F9 Internal Anomalies.
    _ .. Case VLookUp, ( Evaluate(“ “)

    _ .. we see problems, / anomalies to the trends so far discussed: In the next post we will see that the internal F9 type evaluation is more difficult to bring about. That is to say removal of the Excuse for an abortion in the excepted normal Interception intercourse is not following the accepted practices discussed thus far for normal Fuktions.. ¯\(ツ)/¯ ʅ_(ツ)_ʃ

    http://www.excelfox.com/forum/showth...0172#post10172


  12. #12
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Hidden - Scroll to the right in the Code Window '_-
    MS-Off Ver
    Office 2007 2010 PC but Not mac. XP and Vista mostly
    Posts
    3,388

    Digressions From Excepted Interception Intercourse in removing Excuse for Abortion

    Digressions From Excepted Interception Intercourse in removing Excuse for Abortion
    _ .. “F9 Internal” Anomalies.
    _ .. Case VLookUp, Evaluate(“ “)


    For the case of our simple example, REPT(I22:I23,2), we found that F9 in formula gave us {"AA";"BB"}, even for the case of the formula erroring when in a non Implicit Intersection cell. Appling F9 to the I22:I23 function section returns {"A";"B"}

    For the case of =VLOOKUP(I22:I24,I22:K24,3,0) applying F9 to I22:I24 returns {"A";"B";"C"}, but applying to the entire =VLOOKUP(I22:I24,I22:K24,3,0) we get an error

    Looking in Evaluate(“=VLOOKUP(I22:I24,I22:K24,3,0)”) returns us, as possibly expected, just the first value, 1.
    As might also be expected, ( by virtue of F9 on =VLOOKUP(I22:I24,I22:K24,3,0) in the formula bar not working ), we find that typical “to get Multivalve Wonks “ 110 120 do not work, simply returning us {1}.

    150 160 Very interestingly, we do not kill Interception.

    Is this all telling us that VLookUp is not , for some strange reason , doing the preliminary Excuse for an Abortion. God only knows why,
    it could be anything – For example ,
    How about :
    A hold on the second argument as fixed Matrix Look Up Table may have inadvertently been applied to the first argument??
    This would tie up with the next few lines in particular that this sort of thing
    180 __ =If(Row(),VLOOKUP({""A"";""B"";""C""},I22:K24,3,0))
    _.. is not working, either in the code, as above, or, in its spreadsheet version of this
    _ VLOOKUP({"A";"B";"C"},I22:K24,3,0)
    ( The latter returns 1 rather than an error in the spreadsheet as Interception is apparently not working, the former returns {1} as String in a (1 To 1) Variant type Array, as we do array analysis , but on a simple constant of 1 )

    So it appears we do not have an Array at that first argument, resulting in VLookUp returning a single simple value.
    So we now investigate how to “force” an Array in there.
    Whatever trick is needed here, it is not to force multivalve analysis , as this ( as we already saw ) , will simple change the single value to be returned, ( all be it in a (1 To 1 ) Array )

    ' I know an answer, as I have it from here: https://excelxor.com/2014/09/05/inde...ray-of-values/ . The answer appears to have no known logic or understanding. It is currently more or less the only known way to achieve what is likely to be achieving making the second argument, in this case {"A";"B";"C"} , actually look at that point as {"A";"B";"C"} .

    The trick is T(IF(1, {“A”;”B”;”C”}))

    The “trick” turns back on Interception resulting in an error in the substrate without CSE
    Formula: VLookUpFormula.JPG http://imgur.com/sXKOOEu http://imgur.com/a/fK4DY
    =VLOOKUP(T(IF(1,{"A";"B";"C"})),I22:K24,3,0)
    Value returned in cell: VLookUpInCellInterceptionError.JPG http://imgur.com/aur3Ez7 http://i.imgur.com/aur3Ez7.jpg
    #WERT!
    The result after highlighting the formula in the Formula Bar and hitting F9 is
    VLookUpF9FormulaBar.JPG http://imgur.com/MAuIwbZ http://i.imgur.com/MAuIwbZ.jpg
    ={1;2;3}
    =
    {1;2;3}


    and the more usual results in evaluate(“ “), of a single value , 240 , or with a typical recursion , 250 , the final full values

    ' I do not know why the trick, The trick is T(IF(1, {“A”;”B”;”C”})) , works or what it does , ( yet https://excelxor.com/2014/09/05/inde.../#comment-2493 )
    ' I expect nobody does


    Please Login or Register  to view this content.

    ' I do not know why the trick, The trick is T(IF(1, {“A”;”B”;”C”})) , works or what it does , ( yet https://excelxor.com/2014/09/05/inde.../#comment-2493 )
    ' I expect nobody does '_- ¯\(?)/¯?_(?)_? ¯\(ツ)/¯ʅ_(ツ)_ʃ



    https://www.excelforum.com/tips-and-...ml#post4605327
    https://www.excelforum.com/developme...ml#post4603535
    http://www.excelfox.com/forum/showth...0172#post10172

    Last edited by Doc.AElstein; 03-14-2017 at 08:49 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1