Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

[DocAElstein]

Coding for last post
https://www.excelfox.com/forum/showt...ll=1#post23185

Sub EvaluateSmarter()
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim vTemp As Variant
 Let Range("A10:A11") = Evaluate("=A6:A7") ' Empty!    Watch : + : Evaluate("=A6:A7") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                                      Watch : + : Range("A10:A11") :  : Object/Range : Sheet1.EvaluateSmarter
 Let vTemp = Evaluate("=A6:A7")                      ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                              - : vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                                  : vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                              + : vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                                  : vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
'                                                      Watch : + : Evaluate("=A6:A7") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 Let Ws1.Range("A10:A11") = Ws1.Evaluate("=A6:A7") ' Empty!    Watch : + :  Ws1.Evaluate("=A6:A7") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                                              Watch : + : Ws1.Range("A10:A11") :  : Object/Range : Sheet1.EvaluateSmarter
 Let ActiveSheet.Range("A10:A11") = Ws1.Evaluate("=A6:A7") ' Empty!  Watch : + :  Ws1.Evaluate("=A6:A7") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 '                                                                   Watch : + :  ActiveSheet.Range("A10:A11") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 ' I am in the worksheet object code module, so Range and Ws1.Range are likely the same
 Let Range("A10:A11") = Evaluate("=IF(1=1,A6:A7)") ' Empty  Watch : + :  Evaluate("=IF(1=1,A6:A7)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 Let vTemp = Evaluate("=IF(1=1,A6:A7)") '  Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
 '                                                 - : vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
 '                                                   : vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                  + : vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                    : vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF({1},A6:A7)") ' Watch : - :  Evaluate("=IF({1},A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
 '                                                           - :  Evaluate("=IF({1},A6:A7)")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                              :  Evaluate("=IF({1},A6:A7)")(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                            - :  Evaluate("=IF({1},A6:A7)")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                              :  Evaluate("=IF({1},A6:A7)")(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 Let vTemp = Evaluate("=IF({1},A6:A7)") '  Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                  - :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                    :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
 '                                                 - :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
 '                                                   :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=1*A6:A7") 'Watch : + :  Evaluate("=1*A6:A7") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                           + : Evaluate("=1*A6:A7")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                             : Evaluate("=1*A6:A7")(1,1) : 44 : Variant/Double : Sheet1.EvaluateSmarter
'                                           + : Evaluate("=1*A6:A7")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                             : Evaluate("=1*A6:A7")(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
'                                         Watch : + : Range("A10:A11") :  : Object/Range : Sheet1.EvaluateSmarter

 Let vTemp = Evaluate("=1*A6:A7") '        Watch : + : vTemp :    : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                             + : vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                               : vTemp(1,1) : 44 : Variant/Double : Sheet1.EvaluateSmarter
'                                             + : vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                               : vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter

 Let Range("A10:A11") = Evaluate("=IF(ROW(),A6:A7)") 'Watch : + :  Evaluate("=IF(ROW(),A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                  + : Evaluate("=IF(ROW(),A6:A7)")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                    : Evaluate("=IF(ROW(),A6:A7)")(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                  + : Evaluate("=IF(ROW(),A6:A7)")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                    : Evaluate("=IF(ROW(),A6:A7)")(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
'                                                Watch : + : Range("A10:A11") :  : Object/Range : Sheet1.EvaluateSmarter

 Let vTemp = Evaluate("=IF(ROW(),A6:A7)") 'Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                            + :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                              :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                            + :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                              :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(1=1,A6:A7,A6:A7)") ' Empty Watch : + :  Evaluate("=IF(1=1,A6:A7,A6:A7)"):  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                               Watch : + :  Range("A10:A11") :  : Object/Range : Sheet1.EvaluateSmarter
'                                           Print Evaluate("=IF(1=1,A6:A7,A6:A7)").Address
'                                                             $A$6:$A$7
 Let Range("A10:A11") = Evaluate("=IF(1=1,A6:A7,A6:A10)") 'Watch : + : Evaluate("=IF(1=1,A6:A7,A6:A10)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                           print   Evaluate("=IF(1=1,A6:A7,A6:A10)").address
'                                                             $A$6:$A$7
 Let Range("A10:A11") = Evaluate("=IF(1=1,A6:A10,A6:A7)") 'Watch : + : Evaluate("=IF(1=1,A6:A10,A6:A7)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                           Print Evaluate("=IF(1=1,A6:A10,A6:A7)").Address
'                                                             $A$6:$A$10
 
 Let vTemp = Evaluate("=IF(1=1,A6:A7,A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                 + :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                   :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                 + :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                   :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(1=1,A6:A7,1*A6:A7)") ' Empty Watch : + : Evaluate("=IF(1=1,A6:A7,1*A6:A7)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                                                  Watch : + : Range("A10:A11")  :    :  Object/Range : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(1=1,A6:A7,1*A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                       + : vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                         : vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                       + : vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                         : vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(1=1,1*A6:A7,A6:A7)") ' Watch : + : Evaluate("=IF(1=1,1*A6:A7,A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                         + : Evaluate("=IF(1=1,1*A6:A7,A6:A7)")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                           : Evaluate("=IF(1=1,1*A6:A7,A6:A7)")(1,1) : 44 : Variant/Double : Sheet1.EvaluateSmarter
'                                                         + : Evaluate("=IF(1=1,1*A6:A7,A6:A7)")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                           : Evaluate("=IF(1=1,1*A6:A7,A6:A7)")(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(1=1,1*A6:A7,A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                     + : vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                       : vTemp(1,1) : 44 : Variant/Double : Sheet1.EvaluateSmarter
'                                                     + : vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                       : vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(1=0,1*A6:A7,A6:A7)") ' Empty Watch : + :  Evaluate("=IF(1=0,1*A6:A7,A6:A7)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(1=0,1*A6:A7,A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
Dim Var(1 To 2, 1 To 1) As Variant
 Let Var(1, 1) = "44": Let Var(2, 1) = 55       ' Watch : + : Var :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                         - : Var(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                           : Var(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                         - : Var(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                           : Var(2,1) : 55 : Variant/Integer : Sheet1.EvaluateSmarter
Dim V1(1 To 1) As Variant: Let V1(1) = "44": Dim V2(1 To 1) As Variant: Let V2(1) = 55
 Let Var(1, 1) = V1(1): Let Var(2, 1) = V2(1)
'                                                 Watch : - : V2 :  : Variant/Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                           : V2(1) : 55 : Variant/Integer : Sheet1.EvaluateSmarter
'                                                 Watch : + : V1 :  : Variant/Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                           : V1(1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                 Watch : + : Var :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                         - : Var(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                           : Var(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                         - : Var(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                           : Var(2,1) : 55 : Variant/Integer : Sheet1.EvaluateSmarter
Let Var(1, 1) = V1(): Let Var(2, 1) = V2()     '  Watch : + : Var :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                     - : Var(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            + : Var(1,1) :  : Variant/Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                              : Var(1,1)(1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                     - : Var(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            + : Var(2,1) :  : Variant/Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                              : Var(2,1)(1) : 55 : Variant/Integer : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(1,A6:A7,A6:A7)") ' Empty Watch : + :  Evaluate("=IF(1,A6:A7,A6:A7)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(1,A6:A7,A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[DocAElstein]

Another solution
Instead of relying on a trick to change the RHS Evaluate("A1:F7") to return an array of values instead of a range object, we can keep the range object, and apply the .Value(RangeValueDataType:=xlRangeValueDefault) Property / Method thing to it, which returns an array of values. (We can also do that to the usual range object, Range("A1:F7")
In '1c) we do that and get very similar results, and have the advantage that the empty cell(s) are returned as empty cells without needing to do anything further
Here the results for that compared to the very first Evaluate("IF({1},A1:F7)")

    Evaluate("IF({1},A1:F7)")                                Range("A1:F7").Value                            Evaluate("A1:F7").Value
  


.
.
.
.
.
.
.
.
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=jdPeMPT98QU
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX4t
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl 
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4RI2
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA