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


Code:
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