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

**DocAElstein** · 08-13-2023, 07:43 PM

Some *more notes, (* second time around) in support of this forum post
https://eileenslounge.com/viewtopic....28c881#p309137
Excel - alignment of numeric values in cells? https://eileenslounge.com/viewtopic....310023#p310023 https://eileenslounge.com/viewtopic....310024#p310024

I did a solution who’s basic form started like this , pseudo coding

Code:

  Range2 =  Evaluate("=IF(ISNUMBER(A1:F7),1*A1:F7, A1:F7)")

The logic was that
_ if ISNUMBER( ) recognised a number held as text in any cells, then those cells would be multiplied by 1 to change them to normal numbers
_ the final modified range would then be returned/ put in the Range2
It seemed to work. Well it does. But my logic is up the spout, totally.
_ For one thing, ISNUMBER( ) does not recognise a number held as text as a number https://www.excelfox.com/forum/showt...ll=1#post22079
_ On the right hand side, RHS, of a typical code line like this ,
Range2.Value = Range1
, if Range1 is a multi cell range, then nothing at all is returned!!! #21995 #12 https://www.excelfox.com/forum/showt...age2#Post21995

So what the fuck is going on…..
Take a look at the coding in the next posts, https://www.excelfox.com/forum/showt...age2#Post21995
https://www.excelfox.com/forum/showt...ll=1#post23186
We are looking at various variations of code lines with a similar RHS Evaluate(" ") to that in my original initial solution. We apply the result to both
_ a test range, which corresponds to the LHS idea of Range2.Value = Range1
_ we also apply it to a Variant variable
_ we also apply it to a Variant variable
We use the test range A6:A7 which has a number held as text and also a normal number
https://i.postimg.cc/fLXddyGQ/A6-A7.jpg
A6 A7.JPG

In the coding I have put the results of the Watch Window in the 'comments

Now let’s analyse some of those results
https://www.excelfox.com/forum/showt...ll=1#post23189
https://www.excelfox.com/forum/showt...ge59#post23189

**DocAElstein** · 08-13-2023, 07:43 PM

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

Code:

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** · 08-13-2023, 07:51 PM

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

Code:

 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
 
 Let Range("A10:A11") = Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)") ' Watch : + : Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                     - : Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                       : Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)")(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                     - : Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                       : Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)")(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(ISTEXT(A6:A7),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(ISTEXT(A6:A7),A6:A7)") ' Watch : + : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                          - : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)")(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                          - : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)")(2,1) : Falsch : Variant/Boolean : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(ISTEXT(A6:A7),A6:A7)")   '  Watch : + : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)") :  : 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) : Falsch : Variant/Boolean : Sheet1.EvaluateSmarter


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

Let vTemp = Evaluate("=IF(ISNUMBER(A6:A7),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





End Sub

Sub ArrayShenanigans()

End Sub

Sub Sidiary()
Dim Strs() As String
 Let Strs() = Split("44 55")  '  Watch : - : Strs() :  : String(0 to 1) : Sheet1.Sidiary
'                                     : Strs()(0) : "44" : String : Sheet1.Sidiary
'                                     : Strs()(1) : "55" : String : Sheet1.Sidiary
Dim Sids(0 To 1) As String
 Let Sids(0) = "44": Let Sids(1) = "55"
'                                Watch : - : Sids :  : String(0 to 1) : Sheet1.Sidiary
'                                     : Sids(0) : "44" : String : Sheet1.Sidiary
'                                     : Sids(1) : "55" : String : Sheet1.Sidiary
Dim S1(1 To 1) As String: Let S1(1) = "44"
Dim S2(1 To 1) As String: Let S2(1) = "55"
 Let Sids(0) = S1(1): Let Sids(1) = S2(1)
'                                Watch : - : Sids :  : String(0 to 1) : Sheet1.Sidiary
'                                     : Sids(0) : "44" : String : Sheet1.Sidiary
'                                     : Sids(1) : "55" : String : Sheet1.Sidiary
Dim Sids21(1 To 2, 1 To 1) As String '    Watch : - : Sids21 :  : String(1 to 2, 1 to 1) : Sheet1.Sidiary
 Let Sids21(1, 1) = "a":             '             + : Sids21(1) :  : String(1 to 1) : Sheet1.Sidiary
 Let Sids21(2, 1) = "c"              '                 : Sids21(1,1) : "a" : String : Sheet1.Sidiary
'                                                  + : Sids21(2) :  : String(1 to 1) : Sheet1.Sidiary
'                                                      : Sids21(2,1) : "c" : String : Sheet1.Sidiary
'
Dim Sids12(1 To 1, 1 To 2) As String '    Watch : + :  Sids12 :  : String(1 to 1, 1 to 2) : Sheet1.Sidiary
 Let Sids12(1, 1) = "a": Sids12(1, 2) = "b"   '     + :  Sids12(1) :  : String(1 to 2) : Sheet1.Sidiary
'                                                         :  Sids12(1,1) : "a" : String : Sheet1.Sidiary
'                                                         :  Sids12(1,2) : "b" : String : Sheet1.Sidiary
'
Dim Sids22(1 To 2, 1 To 2) As String '    Watch : - : Sids22 :  : String(1 to 2, 1 to 2) : Sheet1.Sidiary
 Let Sids22(1, 1) = "a": Sids22(1, 2) = "b"  '    - : Sids22(1) :  : String(1 to 2) : Sheet1.Sidiary
 Let Sids22(2, 1) = "c": Sids22(2, 2) = "d"    '          : Sids22(1,1) : "a" : String : Sheet1.Sidiary
'                                                         : Sids22(1,2) : "b" : String : Sheet1.Sidiary
'                                                 + : Sids22(2) :  : String(1 to 2) : Sheet1.Sidiary
'                                                         : Sids22(2,1) : "c" : String : Sheet1.Sidiary
'                                                         : Sids22(2,2) : "d" : String : Sheet1.Sidiary




End Sub


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

**DocAElstein** · 08-13-2023, 07:51 PM

_................continued from https://www.excelfox.com/forum/showt...ll=1#post23185

Here the test range agaon, A6:A7 which has a number held as text and also a normal number
https://i.postimg.cc/fLXddyGQ/A6-A7.jpg
A6 A7.JPG

In the coding in the last two post, https://www.excelfox.com/forum/showt...ll=1#post23188
https://www.excelfox.com/forum/showt...ll=1#post23186 , we apply the result to both
_ a test range, which corresponds to the LHS idea of Range2.Value = Range1
_ we also apply it to a Variant variable

Initially we are expecting the RHS, Evaluate(" ") to be returning a range object, ( when it is Let on the LHS to a range ) , even for those code lines using the Excel worksheet function IF, since that is one of the Excel worksheet functions that returns a range object ( https://eileenslounge.com/viewtopic....280942#p280942
https://www.myonlinetraininghub.com/...#comment-84892 )

We also see that when we apply the result to a Variant variable, and as typical, we see that Excel seems to oblige to take the values from the returned range object from the RHS

In many of the code lines we confirm this in the 'comments showing the Watch Window results. However we see that sometimes the results retuned to the LHS are identical for the case of LHS the variable or a range.
Hmm … what’s going on there…
Well this phenomena is not so unusual. We see often see a similar phenomenon, or rather we try to make it happen, when
_ something is not giving us array results when we would like to have them,
_ we then empirically try various tricks to get the array of results. Often we end up with some seemingly redundant or extra thing in the expression, that whilst not directly linked to what we are doing, seems to as a by-product make the expression or function return multi values.
You can see in the various code line variations some of the tricks often done. My favourite, a very simple one, is if I have something not returning multiple values as I wish , then this modification usually does it:
Change
something not returning multiple values as I wish
, to:
IF({1}, something not returning multiple values as I wish )

In addition to such a simple trick, we often find that as progress with developing a solution with more things in the expression, then something we do inadvertently does the same thing.
Looking at the very last code line ,
=IF(ISNUMBER(A6:A7),1*A6:A7,A6:A7) ' returns range object in Evaluate(" ")
, and comparing that with results for one such as this
=IF(1=1,1*A6:A7,A6:A7) ' returns array of values in Evaluate(" ")
, tends to suggest that the ISNUMBER(A6:A7) is doing the trick. So the 1* was not doing anything –( it couldn’t because ISNUMBER(A6) does not return true https://www.excelfox.com/forum/showt...ll=1#post22079 ) , but the whole thing was returning an array of values in a Variant element type array, and as we found out here, https://www.excelfox.com/forum/showt...ll=1#post22084 , even if we have strings in the elements, they are not put in as a string, rather Excel seems to correct them to a number

That’s it. We now figured out what was going on. In the next post we give a final solution based on all we have learnt in the past few weeks…..
Excel Functions ISTEXT and ISNUMBER https://www.excelfox.com/forum/showt...el-Cell/page10
Explanations for my original Evaluate Range one liner until I realised I messed up https://www.excelfox.com/forum/showt...s-etc-)/page58
Starting again with hindsight https://www.excelfox.com/forum/showt...s-etc-)/page59
Range Object Default Properties (Methods?) https://www.excelfox.com/forum/showt...el-Cell/page12
code line type Range2.Value = Range1.Value Question from the web https://www.excelfox.com/forum/showt...ll=1#post23192

Have another think about a solution , next post

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.9fxrOrrvTln9g9wr8mv2 CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t
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.9g9wJCunNRa9gJGhDZ4R I2
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**DocAElstein** · 08-13-2023, 07:51 PM

This is post 23187
https://www.excelfox.com/forum/showt...ll=1#post23187
https://www.excelfox.com/forum/showt...ge59#post23187

The new solution
Having done all the donkey work, and now, knowing probably better than anyone what it is all about we can go straight into some solutions.

Code:

 Sub SimplerSolutionDevelopment()    '     https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23187&viewfull=1#post23187
Rem 0 test data range
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim Rng As Range
 Set Rng = Worksheets.Item("Sheet1").Range("A1:F7")
Rem 1
Rng.Offset(0, Rng.Columns.Count + 1).Clear

'1a These do nothing, and we know why now   https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly?p=23192&viewfull=1#post23192
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Range("A1:F7")
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("A1:F7")
'1b(i) a trick will do it
' Let Rng.Offset(0, Rng.Columns.Count + 1) = Range("IF({1},A1:F7)") ' Run time error  '1004'
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("IF({1},A1:F7)")
 'Watch : - : Evaluate("IF({1},A1:F7)") :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
'                              
'1b(ii)
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")
' Watch : + : Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))") :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
'                              

'1b(iii)
 Rng.Offset(0, Rng.Columns.Count + 1).Clear
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("IF(A1:F7="""","""",A1:F7)")
' Watch : + : Evaluate("IF(A1:F7="""","""",A1:F7)") :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
'                              


'1c)
 Rng.Offset(0, Rng.Columns.Count + 1).Clear
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Range("A1:F7").Value(RangeValueDataType:=xlRangeValueDefault)
 ' Watch : + : Range("A1:F7").Value :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
 '                             
 Rng.Offset(0, Rng.Columns.Count + 1).Clear
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("A1:F7").Value(RangeValueDataType:=xlRangeValueDefault)
 ' Watch : + : Evaluate("A1:F7").Value :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
 '                             
End Sub

Conclusions / A final solution(s)

In the coding above, we do a very simple trick in '1b(i) to get the array of values passed to the LHS, this sort of thing
Range2 = Evaluate("IF({1},A1:F7)")

Code:


.
.
.
.
..
.
.
.
.
..
.
.
.
.
..
.
.
.

That is OK, it almost gets what we want, but we see that we have the problem of just an empty cell returning 0 in the tweaked Evaluate(" ") solution, https://i.postimg.cc/4NF4tH97/proble...d-Evaluate.jpg
: Evaluate("IF({1},A1:F7)")(2,6) : 0 : Variant/Double : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF({1},A1:F7)")(3,6) : 0 : Variant/Double : Sheet1.SimplerSolutionDevelopment

We had this problem in the original solution as well, https://eileenslounge.com/viewtopic....309137#p309137 ,….. If you have any cells in the selection that are empty, then you had better use this next version instead, or else it will change empty cells into a cell with a 0 in it …..
This is perhaps understandable because we are doing mathematical calculations in VBA in Variants, and I am taking a guess that VBA calculations default to 0. Or some similar explanation.
As in the original solution, we can remove that problem by a modification, as in '1b(ii) , so we have like
Range2 = Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")
In fact, as we often find, the extra bit does the trick, so we can remove the IF({1},____) and so simplify the solution to like as in '1b(iii)
Range2 = Evaluate("IF(A1:F7="""","""",A1:F7)")
( One small thing to note with '1b(ii) and '1b(iii) is that the Empty is replace by / shown as "" , a zero length String ""
: Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")(2,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")(3,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF(A1:F7="""","""",A1:F7)")(2,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF(A1:F7="""","""",A1:F7)")(3,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment )

Code:

 Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")                   Evaluate("IF(A1:F7="""","""",A1:F7)")
 
.
.
..
.
.
.
.
.
.
..
.
.
.
.
.
.
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

_.______________________

Well, we got as far as we did before with the original solution, but done it correctly this time.
But we can do another offering:
See next post

**DocAElstein** · 08-13-2023, 07:51 PM

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)")

Code:

    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

**DocAElstein** · 08-14-2023, 04:58 PM

**DocAElstein** · 02-07-2024, 08:57 PM

Later

**DocAElstein** · 02-07-2024, 08:58 PM

In support of these forum posts
https://www.excelfox.com/forum/showt...ll=1#post23981
https://eileenslounge.com/viewtopic....313971#p313971

Developing an Evaluate Range solution
In column A we have things like this: (just showing the first few words in each row for clarity )
Shell.OmitFromView -- PKEY_Shell_Omi …..
SimpleRating -- PKEY_SimpleRating // Type: UInt32 – VT…..
Size -- PKEY_Size ……….// Type: UInt64 – VT
SoftwareUsed -- PKEY_SoftwareUsed// Type: Strin …
SourceItem -- PKEY_SourceItem // Typ …….
I only want this sort of thing
Shell.OmitFromView
SimpleRating
Size
SoftwareUsed
SourceItem
, so the first character set before any spaces
Spreadsheet Formula
So lets try to get those character sets in the fourth column ( Column D ) ,.
Building up a spreadsheet formula to do that is easy. Here is an example:
_____ Workbook: WSO_PropNamesExtended.xls ( Using Excel 2013 32 bit )

_Row\^Col	A	B	C
2	Address.Country -- PKEY_Address_Country // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 100 DEFINE_PROPERTYKEY(PKEY_Address_Country, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 100); #define INIT_PKEY_Address_Country { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 100 }	=SEARCH(" -- PKEY",A2)	=LEFT(A2,B2-1)
3	Address.CountryCode -- PKEY_Address_CountryCode // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 101 DEFINE_PROPERTYKEY(PKEY_Address_CountryCode, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 101); #define INIT_PKEY_Address_CountryCode { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 101 }	=SEARCH(" -- PKEY",A3)	=LEFT(A3,B3-1)
4	Address.Region -- PKEY_Address_Region // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 102 DEFINE_PROPERTYKEY(PKEY_Address_Region, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 102); #define INIT_PKEY_Address_Region { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 102 }	=SEARCH(" -- PKEY",A4)	=LEFT(A4,B4-1)

Worksheet: Ext(Hidden)proph
_____ Workbook: WSO_PropNamesExtended.xls ( Using Excel 2013 32 bit )

_Row\^Col	A	B	C
2	Address.Country -- PKEY_Address_Country // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 100 DEFINE_PROPERTYKEY(PKEY_Address_Country, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 100); #define INIT_PKEY_Address_Country { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 100 }	16	Address.Country
3	Address.CountryCode -- PKEY_Address_CountryCode // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 101 DEFINE_PROPERTYKEY(PKEY_Address_CountryCode, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 101); #define INIT_PKEY_Address_CountryCode { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 101 }	20	Address.CountryCode
4	Address.Region -- PKEY_Address_Region // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 102 DEFINE_PROPERTYKEY(PKEY_Address_Region, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 102); #define INIT_PKEY_Address_Region { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 102 }	15	Address.Region

Worksheet: Ext(Hidden)proph

Now, in column D we simply paste the last working formula , ( from column C ) , and then look from the left of column C so as to get the formula only referring to the first column. That way we can do away with the columns B and C

_____ Workbook: WSO_PropNamesExtended.xls ( Using Excel 2013 32 bit )

_Row\^Col	D
2	=LEFT(A2,SEARCH(" -- PKEY",A2)-1)
3	=LEFT(A3,SEARCH(" -- PKEY",A3)-1)
4	=LEFT(A4,SEARCH(" -- PKEY",A4)-1)

Worksheet: Ext(Hidden)proph

VBA Evaluate Range solution.
The simple direct Evaluate Range implementation would be
Evaluate("LEFT(A2:A1055,SEARCH("" -- PKEY"",A2:A1055)-1)")
As sometimes happens we find that this needs a little trick to get all the available values, since otherwise just the first value is returned.
This final working Evaluate Range line does the trick,
Evaluate("IF({1},LEFT(A2:A1055,SEARCH("" -- PKEY"",A2:A1055)-1))")

Here is a full coding to get the property name words in column E in the uploaded file

Code:

Option Explicit
'   https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23979&viewfull=1#post23979
Sub ExtendedPropertiesList()
' Rem 1 Get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                    ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "propkey h.txt"   '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
'Get #FileNum, , TotalFile
'  Or  http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
 Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum
' Rem 2 Split the Prophs
Dim arrProphs() As String: Let arrProphs() = Split(TotalFile, "//  Name:     System.", -1, vbBinaryCompare)
' 2a) Quick look at list
Dim LCnt As Long: Let LCnt = UBound(arrProphs())
Dim Rws() As Variant, Clms() As Variant, VertList() As Variant
 Let Rws() = Evaluate("ROW(1:" & LCnt + 1 & ")/ROW(1:" & LCnt + 1 & ")")
 Let Clms() = Evaluate("ROW(1:" & LCnt + 1 & ")")
 Let VertList() = Application.Index(arrProphs(), Rws(), Clms())
 Let Me.Range("A1:A" & LCnt & "") = VertList()
Me.Cells.WrapText = False
'' 2b) Look at some example props   using function    WtchaGot_Unic_NotMuchIfYaChoppedItOff
''     The next text is copied from cell A 350
''       "Size -- PKEY_Size
''    //  Type:     UInt64 -- VT_UI8
''    //  FormatID: (FMTID_Storage) {B725F130-47EF-101A-A5F1-02608C9EEBAC}, 12 (PID_STG_SIZE)
''    //
''    //
''    DEFINE_PROPERTYKEY(PKEY_Size, 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC, 12);
''    #define INIT_PKEY_Size { { 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC }, 12 }
''
''    "
''    The next text is copied from watch window at  arrProphs()(349)
''        : arrProphs()(349) : "Size -- PKEY_Size
''    //  Type:     UInt64 -- VT_UI8
''    //  FormatID: (FMTID_Storage) {B725F130-47EF-101A-A5F1-02608C9EEBAC}, 12 (PID_STG_SIZE)
''    //
''    //
''    DEFINE_PROPERTYKEY(PKE"
' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(arrProphs()(349), "Size349")

Rem 3 Evaluate Range to get just the property to use in like    .ExtendedProperty("System.Size")
Dim arrExtProps() As Variant   '
 Let arrExtProps() = Me.Evaluate("IF({1},LEFT(A2:A1055,SEARCH("" -- PKEY"",A2:A1055)-1))")
' 3b) Paste into a worksheet column
 Let Me.Range("E2:E1055") = arrExtProps()
'Stop
End Sub

https://i.postimg.cc/90Znck96/Ext-Pr...n-Column-E.jpg
ExtPropListInColumnE.jpg

**DocAElstein** · 03-04-2024, 05:56 PM

This is post
https://www.excelfox.com/forum/showt...ll=1#post24048
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=24048&viewfull=1#post24048
https://www.excelfox.com/forum/showt...ge12#post24048
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff/page12#post24048