Excel VBA Interception and Implicit Intersection and VLookUp

[DocAElstein]

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 )

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.

[DocAElstein]

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






Edit later ref: https://support.microsoft.com/en-us/...rs=en-us&ad=us

[DocAElstein]

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.

First row formula
 
Row\Col
B

3
=VLOOKUP(A3,$A$16:$C$33,3,FALSE)


Row\Col
C

3
{=VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)}


Row\Col
D

3
=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)



Look Up values and returned formula Values seen in cells
Row\Col
A
B
C
D

2
Given Look Up Value
Simple Formula. First argument single Look Up value. Draged down
Column range reference first argument - Type 2 CS Entry 
First argument array. Type 2 CS Entry 

3
Chocolate-europe aroma
4
4
4

4
Chocolate-Cookies
0
0
0

5
Banana-Chocolate-Split
10
10
10

6
Limette-Käsekuchen
16
16
16

7
Erdbeere-Quark
8
8
8

8
Erdbeere-Mix
0
0
0

9
Jamaica Sun
6
6
6

10
Waldbeeren
0
0
0



Look Up Table
Row\Col
A
B
C

14
LOOKUP Table
Column 2
Column 3

15
Look Up ValueProduct Name



16
Haselnuß-Walnuß-aromatisiert



17
Tiramisu

2

18
Chocolate-colonial blend



19
Chocolate-europe aroma

4

20
Chocolate-Cookies



21
Jamaica Sun

6

22
Himbeere-Joghurt



23
Erdbeere-Quark

8

24
Erdbeere-Mix



25
Banana-Chocolate-Split

10

26
Waldbeeren



27
Kirsche

12

28
Kirsche-grüner Apfel



29
Kirsche-Ananas

14

30
Stracciatella



31
Limette-Käsekuchen

16

32
grüner Apfel-Quark



33
Blutorange-Quark

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

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:

1








2








3

Chocolate-europe aroma
Chocolate-europe aroma
Chocolate-europe aroma
Chocolate-europe aroma
Chocolate-europe aroma
Chocolate-europe aroma

4

Chocolate-Cookies
Chocolate-Cookies
Chocolate-Cookies
Chocolate-Cookies
Chocolate-Cookies
Chocolate-Cookies

5

Banana-Chocolate-Split
Banana-Chocolate-Split
Banana-Chocolate-Split
Banana-Chocolate-Split
Banana-Chocolate-Split
Banana-Chocolate-Split

6

Limette-Käsekuchen
Limette-Käsekuchen
Limette-Käsekuchen
Limette-Käsekuchen
Limette-Käsekuchen
Limette-Käsekuchen

7

Erdbeere-Quark
Erdbeere-Quark
Erdbeere-Quark
Erdbeere-Quark
Erdbeere-Quark
Erdbeere-Quark

8

Erdbeere-Mix
Erdbeere-Mix
Erdbeere-Mix
Erdbeere-Mix
Erdbeere-Mix
Erdbeere-Mix

9

Jamaica Sun
Jamaica Sun
Jamaica Sun
Jamaica Sun
Jamaica Sun
Jamaica Sun

10

Waldbeeren
Waldbeeren
Waldbeeren
Waldbeeren
Waldbeeren
Waldbeeren

11








12








13

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.

[DocAElstein]

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([size=1]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

[DocAElstein]

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.

[DocAElstein]

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.

[DocAElstein]

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/viewtop...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/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
)

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.

[DocAElstein]

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

Row\Col
I
J
K

22
A
-
1

23
B
-
2

24
C
-
3


Row\Col
M

40


41



Sub EvalRep1() '
10   Dim strEval As String
20    Let strEval = "=REPT({""A"";""B""},2)": Debug.Print strEval   ' Ctrl + g gives:  =REPT({"A";"B"},2)   which is what we would write in a cell
30   Dim vTemp As Variant 'Choose Variant as we may get a single value or Array from Evaluate
40    Let vTemp = Evaluate(strEval)                  'Returns "AA"
50    Let vTemp = Evaluate("=REPT({""A"";""B""},2)") 'Returns "AA"
60    Let strEval = "=REPT(I22:I23,2)"               'Returns "AA"
70    Let vTemp = Evaluate(strEval)                  'Returns "AA"
80    Let vTemp = Evaluate("=REPT(I22:I23,2)")       'Returns "AA"
90    Let vTemp = Evaluate(strEval)                  'Returns "AA"
100  ' A few strings for evaluate Preliminary: repeted in next code    '_- results given by Evaluate(strEval)
110   Let strEval = "={" & """""" & ";" & """""" & "}" & "&" & "REPT(I22:I23,2)": Debug.Print strEval ' ={"";""}&REPT(I22:I23,2)
120   Let vTemp = Evaluate(strEval)                  '_- Just first value, "AA" returned  ' A null string is being concatenated.
130   Let strEval = "=M40:M41" & "&" & "REPT(I22:I23,2)": Debug.Print strEval       '    =M40:M41&REPT(I22:I23,2)
140   Let vTemp = Evaluate(strEval)                  '_- Just first value, AA returned    ' A null string is being concatenated.
150   Let strEval = "=If(row(),M40:M41)" & "&" & "REPT(I22:I23,2)": Debug.Print strEval '=If(row(),M40:M41)&REPT(I22:I23,2)
160   Let vTemp = Evaluate(strEval)                  '_- returns "0AA"   = {0;0} & {AA;BB} but only rule of a single value ??
170   Let strEval = "=If(row(),M40:M41)" & "&" & "If(row(),REPT(I22:I23,2))": Debug.Print strEval '=If(row(),M40:M41)&If(row(),REPT(I22:I23,2))
180   Let vTemp = Evaluate(strEval)                  '?? Ahh '_- returns 1 "column" 2 "row" array  '  {0AA;0BB}  It is doing the evaluation of range Object and getting values from the Function
190   Let strEval = "=If(row(),M40:M41)": Debug.Print strEval                         '  =If(row(),M40:M41)
200   Let vTemp = Evaluate(strEval)                  '_- returns 1 "column" 2 "row" array  '  {0;0}  It is doing the evaluation
210   Let strEval = "=M40:M41": Debug.Print strEval                                   ' =M40:M41
220   Let vTemp = Evaluate(strEval)                  '_- returns 1 "column" 2 "row" array  '  {Empty;Empty}  value from Range object
230   Let strEval = "M40:M41": Debug.Print strEval                                    '  M40:M41
240   Let vTemp = Evaluate(strEval)                  '_- returns 1 "column" 2 "row" array  '  {Empty;Empty}  value from Range object
250
260  ' '_- 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/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-14.html#post4595462        http://www.eileenslounge.com/viewtopic.php?f=30&t=25213&p=202227#p202227
270   Let strEval = "='[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets("XLORX").Name & "'!M40:M41"
280   Let vTemp = Evaluate(strEval)                  '_- returns 1 "column" 2 "row" array  '  {Empty;Empty}  value from Range object
290   Let vTemp = Evaluate(strEval).Value            '_- returns 1 "column" 2 "row" array  '  {Empty;Empty}  value from Range object
300   Let vTemp = Range("M40:M41").Value             '_- returns 1 "column" 2 "row" array  '  {Empty;Empty}  value from Range object
310   Let vTemp = Range(strEval).Value               '_- returns 1 "column" 2 "row" array  '  {Empty;Empty}  value from Range object
320  Dim RngTemp As Range
330   Set RngTemp = Evaluate(strEval) '1 Areas Rng   '_-  Evaluate works slightly differently for a range referrence, and will return a Range Object if the recieving variable is declared ( Dim'ed ) appropriately
340   Let strEval = "='[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets("XLORX").Name & "'!M40:M41,N42"
350   Let vTemp = Evaluate(strEval)                  '_- returns 1 "column" 2 "row" array  '  {Empty;Empty}  value from first Area of Range object
360   Let vTemp = Evaluate(strEval).Value            '_- returns 1 "column" 2 "row" array  '  {Empty;Empty}  value from first Area of Range object
370   Set RngTemp = Evaluate(strEval) '2 Areas Rng   '_-  Evaluate works slightly differently for a range referrence, and will return a Range Object if the recieving variable is declared ( Dim'ed ) appropriately. Here it is a 2 Areas range object
380
390  ' '_- Index way of looking at it:
400   Let strEval = "=Index(I22:K24,0,0)"
410   Let vTemp = Evaluate(strEval)                  '_- Without specific type, this defaults to Evaluate(strEval).Value  --- Variant member type array
420   Set RngTemp = Evaluate(strEval)                '_- Range object returned
430   Let strEval = "=Index(I22:K24,1,0)"
440   Let vTemp = Evaluate(strEval)
450   Set RngTemp = Evaluate(strEval)                '_- Range object returned
460   Let strEval = "=Index(I22:K24,1,1)"
470   Let vTemp = Evaluate(strEval)
480   Set RngTemp = Evaluate(strEval)                '_- Range object returned
490   Let vTemp = Application.Index(Range("I22:K24"), 0, 0)         '_- 3 x 3 Array returned
500   Set RngTemp = Application.Index(Range("I22:K24"), 0, 0)       '_- 3 x 3 Range object returned
510   Let vTemp = Application.Index(Range("I22:K24"), 1, 0)         '_- 1 Dimension 3 Member element Array returned Array returned
520   Set RngTemp = Application.Index(Range("I22:K24"), 1, 0)       '_- 1 x 3 Range object returned
530   Let vTemp = Application.Index(Range("I22:K24"), 1, 1)         '_- return value A in String type variable
540   Let vTemp = Application.Index(Range("I22:K24"), 1, 3)         '_- return value 1 in Double type variabel
545   Let vTemp = Application.Index(Range("I22:K24"), 1, 3).Value   '_- return value 1 in Double type variabel
550   Set RngTemp = Application.Index(Range("I22:K24"), 1, 1)       '_- 1 cell Range object returned, or deafault to "A"
555   Set RngTemp = Application.Index(Range("A1:B2,I22:K24"), 1, 1, 2)          '_- 1 cell Range object returned( same cell as last line )
560   Let vTemp = Application.Index(Range("A1:B2,I22:K24"), 1, 3, 2).Value      '_- return value 1 in Double type variabel    pgc01   https://www.mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column-2.html#post4375560
570  ' '_- Evaluate multivalues 1 ( Back To is now ;) )
580   Let strEval = "={1;2}+{3;4;5}"
590   Let vTemp = Evaluate(strEval)                  '_- returns {4;6;error}
600   Let strEval = "=M40:M42"
610   Let vTemp = Evaluate(strEval)                  '_- returns {Empty;Empty;Empty}
620   Set RngTemp = Evaluate(strEval)                '_- Range object returned
630   Let strEval = "={1;2}+M40:M42"
640   Let vTemp = Evaluate(strEval)                  '_- returns {4;6;error}
650   Let strEval = "={1}+M40:M42"
660   Let vTemp = Evaluate(strEval)                  '_- returns {1;1;1}
670   Let strEval = "={1,2}+M40:M42"
680   Let vTemp = Evaluate(strEval)                  '_- returns {1,2;1,2;1,2}
690   Let strEval = "=1+M40:M42"
700   Let vTemp = Evaluate(strEval)                  '_- returns {1;1;1}
710
End Sub

[DocAElstein]

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 )

[DocAElstein]

Code for last Post

'10   '         I   J   K
'20   '
'30   '  22     A   -   1
'40   '  23     B   -   2
'50   '  24     C   -   3
'60   '
Sub EvalRep2()      '                                https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-14.html#post4602295
70   Rem 1) Here I go again. Just getting First value.    http://listenonrepeat.com/watch/?v=WyF8RHM1OCg#Whitesnake_-_Here_I_Go_Again__87
80   Dim strEval As String
90    Let strEval = "=REPT({""A"";""B""},2)": Debug.Print strEval           '  Ctrl+g reveals   =REPT({"A";"B"},2)    in the Immediate Window" , which is as "seen" by VBA, which is how we would write it in a cell  -  Quotes:  http://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-11.html#post4555023
100  Dim vTemp As Variant 'Choose Variant as we may get a single value or Array from Evaluate
110   Let vTemp = Evaluate(strEval)                                         ' Returns "AA"
120   Let vTemp = Evaluate("=REPT({""A"";""B""},2)")                        ' Returns "AA"
130   Let strEval = "=REPT(I22:I23,2)": Debug.Print strEval                 '  Ctrl+g reveals   =REPT(I22:I23,2)   in the Immediate Window
140   Let vTemp = Evaluate(strEval)                                         ' Returns "AA"
150   Let vTemp = Evaluate("=REPT(I22:I23,2)")                              ' Returns "AA"
160   Let vTemp = Evaluate(strEval)                                         ' Returns "AA"
170  Rem 2) Trying to get both possible values out.
180  '2a) review Excel VBA multi value analysis
190   Let strEval = "=I22:I23": Debug.Print strEval                         '    =I22:I23
200  Dim RngTemp As Range
210   Set RngTemp = Evaluate(strEval)
220   Let vTemp = Evaluate(strEval).Value: Let vTemp = Evaluate(strEval)    '   Default of .Value for returned Range object returned for unspecific Declaration ( Dim ing )
230   Let strEval = "=I22:I23" & "&" & "I22:I23": Debug.Print strEval       '    =I22:I23&I22:I23
240   'Set RngTemp = Evaluate(strEval) ' Error '421 Object required. - Excel takes this as a formula and not a referrence, values are given for the two referrences
250   Let vTemp = Evaluate(strEval)                                         ' Returns {AA;BB}
260   Let strEval = "=I23:I24" & "&" & "I22:I23": Debug.Print strEval       '    =I23:I24&I22:I23
270   Let vTemp = Evaluate(strEval)                                         ' Returns {BA;CB}
'2a(ii) When there is a mismatch in Array sizes,
280   Let strEval = "=I22:I24" & "&" & "I22:I23": Debug.Print strEval       '    =I22:I24&I22:I23
290   Let vTemp = Evaluate(strEval)                                         ' Returns {AA;BB;error 2042}
'Interception Theory:   http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp              https://www.excelforum.com/tips-and-tutorials/1172587-excel-vba-interception-and-implicit-intersection-and-vlookup.html
'_(ii) ' Single value
300   Let strEval = "=I22:I24" & "&" & "I22": Debug.Print strEval           '    =I22:I24&I22
310   Let vTemp = Evaluate(strEval)                                         ' Returns {AA;BA;CA}
320   Let strEval = "=I22:I24" & "&" & """A""": Debug.Print strEval         '    =I22:I24&"A"
330   Let vTemp = Evaluate(strEval)                                         ' Returns {AA;BA;CA}
'_(iii) ' Single breadth
340   Let strEval = "=I22:J24" & "&" & """A""": Debug.Print strEval         '    =I22:J24&"A"
350   Let vTemp = Evaluate(strEval)                                         ' Returns {AA,-A;BA,-A;CA,-A}
360   Let strEval = "=I22:J24" & "&" & "I22:I23": Debug.Print strEval       '    =I22:J24&I22:I23
370   Let vTemp = Evaluate(strEval)                                         ' Returns {AA,-A;BB,-B;error 2042,error 2042}
380   Let strEval = "=I22:J24" & "&" & "{""A"";""B""}": Debug.Print strEval '    =I22:J24&{"A";"B"}
390   Let vTemp = Evaluate(strEval)                                         ' Returns {AA,-A;BB,-B;error 2042,error 2042}
400 '
410  '2b)(i) Attempting concatenations
420   Let strEval = "=I22:I23" & "&" & "REPT(I22:I23,2)": Debug.Print strEval '    =I22:I23&REPT(I22:I23,2)
430   Let vTemp = Evaluate(strEval) '   String housed in Variant type --      ' Returns "AAA" which is A & AA , so first value again
440   Let strEval = "={" & """""" & ";" & """""" & "}" & "&" & "REPT(I22:I23,2)": Debug.Print strEval ' ={"";""}&REPT(I22:I23,2)
450   Let vTemp = Evaluate(strEval)                                           ' Just first value, "AA" returned  ' A null string is being concatenated.
460   Let strEval = "=M40:M41" & "&" & "REPT(I22:I23,2)": Debug.Print strEval       '    =M40:M41&REPT(I22:I23,2)  NOTE: M40:M41 are arbritrary empty cells
470   Let vTemp = Evaluate(strEval)                                           ' Just first value, AA returned    ' A null string is being concatenated.
480   Let strEval = "={""A"";""B""}" & "&" & "REPT(I22:I23,2)": Debug.Print strEval '    ={"A";"B"}&REPT(I22:I23,2)
490  '2b(ii) Killing Interception
500   Let vTemp = Evaluate(strEval) '   String housed in Variant type --      ' Returns "AAA" which is A & AA   First Value.   KILLED interception
510   Let strEval = "={""A"";""B""}" & "&" & "REPT({""A"";""B""},2)": Debug.Print strEval '    ={"A";"B"}&REPT({"A";"B"},2)
520   Let vTemp = Evaluate(strEval) '   String housed in Variant type --      ' Returns "AAA" which is A & AA   First Value.   KILLED interception
530   Let strEval = "={""A"";""B""}" & "&" & """AA""": Debug.Print strEval    '   ={"A";"B"}&"AA"
540   Let vTemp = Evaluate(strEval) '                                         ' Returns {"AAA";"BAA"}
550   Let strEval = "={""A"";""B""}" & "&" & "REPT(""A"",2)": Debug.Print strEval '    ={"A";"B"}&REPT("A",2)
560   Let vTemp = Evaluate(strEval) '                                         ' Returns {"AAA";"BAA"}
570   Let strEval = "=I22:I23" & "&" & "REPT({""A"";""B""},2)": Debug.Print strEval       '   =I22:I23&REPT({"A";"B"},2)
580   Let vTemp = Evaluate(strEval) '   String housed in Variant type --      ' Returns "AAA" which is A & AA   First Value.   KILLED interception
590   Let strEval = "=I22:J23" & "&" & "REPT({""A"";""B""},2)": Debug.Print strEval       '   =I22:J23&REPT({"A";"B"},2)
600   Let vTemp = Evaluate(strEval) '   String housed in Variant type --      ' Returns "AAA" which is A & AA   First Value.   KILLED interception
610 '
620  '2c) Multivalue from REPT({"A";"B"},2)        "to get Multivalue Wonks"
630  '2c)(i) Index Wonks
640   Let strEval = "=Index(REPT({""A"";""B""},2),0,0)": Debug.Print strEval  '=Index(REPT({"A";"B"},2),0,0)
650   Let strEval = "=Index(REPT({""A"";""B""},2),0)"
660   Let strEval = "=Index(REPT({""A"";""B""},2),0,1)"
670   Let strEval = "=Index(REPT({""A"";""B""},2),)"
680   Let strEval = "=Index(REPT({""A"";""B""},2),,)"
690   Let strEval = "=Index(REPT({""A"";""B""},2),,0)"
700   Let strEval = "=Index(REPT({""A"";""B""},2),,1)"
710   Let vTemp = Evaluate(strEval)      '    All above succesful             ' Returns {"AA";"BB"}                                              '
720
730   Let strEval = "=I22:J24" & "&" & "Index(REPT({""A"";""B""},2),0,0)"
740   Let vTemp = Evaluate(strEval)      '    Intersection well behaved       ' Returns {"AAA,"-AA";"BBB,"-BB";"error 2042,error 2042"}
750
760  '2c)(ii) Transpose Wonk
770   Let strEval = "=Transpose(REPT({""A"",""B""},2))"
780   Let vTemp = Evaluate(strEval)                                           ' Returns {"AA";"BB"}
790 '
800  '2c(iii) If(Row(), ) If(Column(), ) Wonks
810   Let strEval = "=If(Column(),REPT({""A"";""B""},2))"
820   Let vTemp = Evaluate(strEval)                                           ' Returns {"AA";"BB"}
830 '
840  '2c(iv) If(Row(j1:j2)xColumn(i1:i2), ) Wonks
850   Let strEval = "=If(Row(1:2),REPT({""A"";""B""},2))"
860   Let strEval = "=If(Row(45:46),REPT({""A"";""B""},2))"
870   Let strEval = "=If(Row(A4:A5),REPT({""A"";""B""},2))"
880   Let vTemp = Evaluate(strEval)                                           ' All above Returns {"AA";"BB"}
890
900   Let strEval = "=If(Row(1:3),REPT({""A"";""B""},2))"
910   Let vTemp = Evaluate(strEval)                                           ' Returns {"AA";"BB";error 2042}
920
921 '2c(v) Some background to Row( ) type stuff.
922   Let strEval = "=If({1;1},REPT({""A"";""B""},2))"
923   Let strEval = "=If({True;True},REPT({""A"";""B""},2))"
924   Let strEval = "=If({True;1},REPT({""A"";""B""},2))"
925   Let strEval = "=If({True;2},REPT({""A"";""B""},2))"                     ' NOTE: any other than 0 is taken as True or 1
926   Let vTemp = Evaluate(strEval)                                           ' All above Returns {"AA";"BB"}
927   Let strEval = "=If({True;0},REPT({""A"";""B""},2))"
928   Let vTemp = Evaluate(strEval)                                           ' Returns {"AA";False}
' Behaving itself once the Multivalues are obtained.
930   Let strEval = "=If(Row(1:3)*Column(A:B),REPT({""A"";""B""},2))"         ' First argument returns an Array size, Interception ( Implicit Intersection on single breadth Arrays ) giving Array ##
935   Let strEval = "=If(Row(3:5)*Column(AB:AC),REPT({""A"";""B""},2))"       ' First argument returns an Array size, Interception ( Implicit Intersection on single breadth Arrays ) giving Array ##
940   Let vTemp = Evaluate(strEval)                                           ' All above Returns {"AA","AA";"BB","BB";error 2042,error 2042}
950 '
960 Rem 3
970   Let vTemp = Evaluate("=Row(3:5)*Column(D:E)")                         ' Returns {12,15;16,20;20,25} = {2x4,3x5;4x4,4x5,5x4,5x5}
End Sub