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.