Tests and Notes on Range objects in Excel Cell

**DocAElstein** · 08-01-2023, 06:27 PM

Evaluate Range type solutions,
Text strings in and out of spreadsheet and effect on Number held as text thing
Consequences to Evaluate Range type solutions, from conclusions from last post

These were the conclusions from the last few posts:
_1) If the thing we try to put in a cell via like Range("G45").Value = is a simple single value variable, there does not appear to be a way to put "44" in a cell which would result in getting that _ Number held as text thing _
_2) If the thing we try to put in a cell via like Range("G45").Value = is an element in an array, then the situation is slightly more varied. In order to get that Number held as text thing ,
_2)(i) the array type must be String. (It can even be an array of just one element as in the experiment example line 32)
, but _2)(ii) It must be applied as a an array – if you apply a single specific element from the array, then you will not get that Number held as text thing

So that is the case when we put in a cell or cells via like Range("G45").Value = , a variable or element of an array, or an array, and it seems that only an array of string type will work to get that Number held as text thing thing

We might be able to make a good guess on the conclusions, based on the following:
_ The evaluate range type solutions are of this general form
_____ Range(____) .Value = Evaluate(________)
_ The RHS of the equation replaces the array or array element or simple variable we were looking at in the last two posts.
_ Most usually our range evaluate solutions are involved in getting the RHS to return an array of values
_ In more general use, Evaluate(__) may return a large variety of different things, and so correspondingly it has been designed to return Variant. Theoretically in general we can have an array of string element types in a Variant, and we can show that this will give us a Number held as text thing , ( https://www.excelfox.com/forum/showt...ll=1#post11210
https://www.excelfox.com/forum/showt...age2#post11210 )
However we are playing around with spreadsheet ranges, and whether in a spreadsheet or in VBA, any thing, any wiring, that gets values from them is wired to have Variant type elements since values can be a few things like numbers empties, strings, etc. This last bit is a bit dodgy and I might have to think about that again.
But for now, let’s do some experiments and see what actually happens.

Test Range
What do we want to do?:- We want to see what happens when we work on ranges in Evaluate(" ") and then put the result back into the spreadsheet,
To get some cells exhibiting Number held as text thing , the Split function is convenient

Code:

    Sub EvalAndHeldAsStringTypeInSpreadsheet()
    Dim Ws12 As Worksheet: Set Ws12 = ThisWorkbook.Worksheets.Item("Sheet1 (2)")
     Let Ws12.Range("A50:D50").Value = Split("44 55 66 77")

https://i.postimg.cc/L4vDz4ZP/Split-...ring-thing.jpg
Split Function convenient to put in Number held as string thing.JPG

**DocAElstein** · 08-01-2023, 06:27 PM

Results 1 Single Cell in/ out of Evaluate(" ")

This following macro is quite extensive, looking at what a variable assigned to the Evaluate(" ") looks like
variable = Evaluate(" ")
, then puts it in the spreadsheet via the typical
Range( "GH45").Value = variable
, and
Range( "GH45").Value = Evaluate(" ")

, a few surprises,

I will just try to summarise for now

Code:

    Sub EvalAndHeldAsStringTypeInSpreadsheet_SingleCell() '  https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21978&viewfull=1#post21978
    Dim Ws12 As Worksheet: Set Ws12 = ThisWorkbook.Worksheets.Item("Sheet1 (2)")
     Let Ws12.Range("A50:D50").Value = Split("44 55 66 77")
    Dim vTemp As Variant, Rng As Range, Str As String, Lng As Long
    Dim Strs1(1 To 1) As String, Strs() As String, Vs1(1 To 1) As Variant, Vs() As Variant

51   Let vTemp = Evaluate("=A50")        '  Watch :   :  vTemp : "44" : Variant/String : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet
    '  
    '  
     Let Range("A51").Value = vTemp: Range("B51").Value = Evaluate("=A50")
52   Set Rng = Evaluate("=A50")          '  Watch : + : Rng : "44" : Range/Range : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet
     Let Range("A52").Value = Rng: Range("B52").Value = Rng.Value: Range("C52").Value = Rng.Value2: Range("D52").Value = Rng.Text
53   Let Str = Evaluate("=A50")          '  Watch :   : Str : "44" : String : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet
     Let Range("A53").Value = Str
     Let Range("B53").Value = Array(Str) '  Watch : - : Array(Str) :  : Variant/Variant(0 to 0) : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet_Sin
                                         '            : Array(Str)(0) : "44" : Variant/String : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet_Sin
    Dim arrStr() As String: '  Let arrStr = Array(Str)  '  Error type mismatch
54   Let Lng = Evaluate("=A50")          '  Watch :   : Lng : 44 : Long : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet
     Let Range("A54").Value = Lng
     Let Strs1(1) = Evaluate("=A50") '      Watch :   : Strs1(1) : "44" : String : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet_Sin
     Let Range("B54").Value = Strs1(1)
     Let Range("C54").Value = Strs1()  '  This does get the  Number as text thing         
    ' Let Strs() = Evaluate("=A50")  '   Error  Type mismatch
55   Let Vs1(1) = Evaluate("=A50")        '  Watch :   :  Vs1(1) : "44" : Variant/String : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet
     Let Range("A55").Value = Vs1(1)
    ' Let Vs() = Evaluate("=A50")    '   Error  Type mismatch
    
56   Let vTemp = Array(Evaluate("=A50"))  '  Watch : - :  vTemp :  : Variant/Variant(0 to 0) : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet
' https://i.postimg.cc/x87T82MF/Array-Evaluate-A50.jpg     + :  vTemp(0) :  : Variant/Object/Range : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet
     Let Range("A56").Value = vTemp: Range("B56").Value = Array(Evaluate("=A50")):  '   Range("C56").Value = vTemp.Value: Range("D56").Value = Array(Evaluate("=A50")).Value       '  Runtime Error  424    Object needed
57   Let vTemp = Array(Lng)               '  Watch : + :  vTemp :  : Variant/Variant(0 to 0) : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet
' https://i.postimg.cc/Wzt1s3Fk/Array-Lng.jpg                :  vTemp(0) : 44 : Variant/Long : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet
     Let Range("A57").Value = vTemp: Range("B57").Value = Array(Lng)
58   Let vTemp = Split(Lng)               '  Watch : - :  vTemp :  : Variant/String(0 to 0) : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet
' https://i.postimg.cc/FRTKtcRv/Split-Lng.jpg            Watch :   : Lng : 44 : Long : Sheet2.EvalAndHeldAsStringTypeInSpreadsheet
     Let Range("A58").Value = vTemp: Range("B58").Value = Split(Lng)
' 
' bild upload kostenlos
    
    
    End Sub

Results1 Single Cell in out of Evaluate( ).JPG

**DocAElstein** · 08-01-2023, 06:27 PM

Results1 Single Cell in out of Evaluate( ).JPG

Results 1 Single Cell in/ out of Evaluate(" ")
Evaluate(" ") works a bit differently for a range.
It appears initially to return a range object. Put that in a cell via like
Range( "GH45").Value = That range object
, in the development coding example, B51 or A52, (and A56 or B56), or like in the macro below, and you will get that Number held as text thing preserved when that range object is “put in the cell” via like
Range( "GH45").Value = That range object

Code:

Sub SetRangeValueWithRange()
    Dim Ws12 As Worksheet: Set Ws12 = ThisWorkbook.Worksheets.Item("Sheet1 (2)")
     Let Ws12.Range("A50:D50").Value = Split("44 55 66 77")
    Dim CelRng As Range
     Set CelRng = Ws12.Range("A50")
     Let Ws12.Range("A60").Value = CelRng       '         Gives  Number held as text thing 
     Let Ws12.Range("B60").Value = CelRng.Value ' does not give      "
'   https://i.postimg.cc/MTw4jdpV/Set-Ra...text-thing.jpg
'   https://i.postimg.cc/rm9HGxDb/Set-Ra...text-thing.jpg
'          


End Sub

**DocAElstein** · 08-01-2023, 06:27 PM

Results/ Conclusions 1 Single Cell in/ out of Evaluate(" ")
Again some Measurements, Results and conclusions with hindsight

Consider the following coding.
Rem 0
Two cells are considered, both are similar numbers, only one of the cells exhibits the Number held as text thing
Rem 1
For a simple single cell range, Evaluate(" ") , seems to return an actual range object, and if we attempt to put that in a cell, it won’t default to taking the default property, .Value, instead it appears to put the range object in. This will preserve the string of a number held as a string, A70 and B70
If it did default to the .Value property, then we would get the result as in C70
Rem 2
If you assign the result from Evaluate(" ") for a simple single cell range, into a variable, then VBA seems to be quite obliging, coercing appropriately to suit the declaration of any reasonable variable type.
If the variable is a Variant, the type held in the Variant will be a string in the case of Evaluate(" ") of a cell exhibiting the number held as text thing.
Rem 3
Any attempt to put a string into a cell won’t give the number held as text thing. Strange.

Code:

Sub SetRangeValueWithSingleCellRangeFromEvaluate() '   https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21979&viewfull=1#post21979
    Rem 0
    ' Test data range
        Dim Ws12 As Worksheet: Set Ws12 = ThisWorkbook.Worksheets.Item("Sheet1 (2)")
         Ws12.Range("A69:D71").Clear  '  .Clear  to get rid of all valiues and any formats
         Let Ws12.Range("A69").Value = Split("44") ' '         Gives  Number held as text thing 
         Let Ws12.Range("D69").Value = "55"        '   does not give          "
    ' 
    ' 
    Rem 1
    ' Evalute(" ") from the single cell ranges
        Dim VA69 As Variant, VD69 As Variant, RA69 As Range, RD69 As Range
    ' 1a) Fubdamentally, I think Evalute(" ") returns a range, and it knows what is what
     Set RA69 = Evaluate("=A69") '         Watch : + :  RA69 : "44" : Range/Range : Sheet2.SetRangeValueWithSingleCellRangeFromEval
     Set RD69 = Evaluate("=D69") '         Watch : + : RD69 : 55 : Range/Range : Sheet2.SetRangeValueWithSingleCellRangeFromEval
     
70   Let Ws12.Range("A70") = Evaluate("=A69") ' This appears to apply the range to a cell ..... and it knows waht is what
     
     Let Ws12.Range("A70") = RA69 '        Watch : + :  Ws12.Range("A70") : "44" : Object/Range : Sheet2.SetRangeValueWithSingleCellRangeFromEval
     Let Ws12.Range("B70").Value = RA69 '  Watch :   :  Ws12.Range("B70").Value : "44" : Variant/String : Sheet2.SetRangeValueWithSingleCellRangeFromEval
     Let Ws12.Range("C70") = RA69.Value '  Watch : + :  Ws12.Range("C70") : 44 : Object/Range : Sheet2.SetRangeValueWithSingleCellRangeFromEval
                                        '  Watch :   :  RA69.Value : "44" : Variant/String : Sheet2.SetRangeValueWithSingleCellRangeFromEval
     Let Ws12.Range("D70") = Evaluate("=D69")
     Let Ws12.Range("D70") = RD69
     
    Rem 2 Assigning Evalute(" ") to a variable is fairly obliging
     Let VA69 = Evaluate("=A69") '     Watch :   : VA69 : "44" : Variant/String : Sheet2.SetRangeValueWithSingleCellRangeFromEval
     Let VD69 = Evaluate("=D69") '     Watch :   : VB69 : 55 : Variant/Double : Sheet2.SetRangeValueWithSingleCellRangeFromEval
    Dim Str As String, Lng As Long
     Let Str = Evaluate("=D69")  '     Watch :   : Str : "55" : String : Sheet2.SetRangeValueWithSingleCellRangeFromEval
     Let Str = Evaluate("=A69")  '     Watch :   : Str : "44" : String : Sheet2.SetRangeValueWithSingleCellRangeFromEval
     Let Lng = Evaluate("=A69")  '     Watch :   :  Lng : 44 : Long : Sheet2.SetRangeValueWithSingleCellRangeFromEval
     Let Lng = Evaluate("=D69")  '     Watch :   :  Lng : 44 : Long : Sheet2.SetRangeValueWithSingleCellRangeFromEval
    
71  Rem 3  Any attempt to put a string into a cell won't give the number held as text thing
     Let Ws12.Range("A71").Value = Str
     Let Ws12.Range("B71") = Str
     Let Ws12.Range("C71") = "45"

72  Rem 4
    Dim arrRng(1 To 1) As Range
     Set arrRng(1) = Evaluate("=A69") 'Watch : + : arrRng(1) : "44" : Range/Range : Sheet2.SetRangeValueWithSingleCellRangeFromEval
     Let Ws12.Range("A72").Value = arrRng(1) ' gives the number held as text thing
     Let Ws12.Range("A72").Value = arrRng()  ' gives the number held as text thing
     Let Ws12.Range("B72") = arrRng(1) '       gives the number held as text thing
    Dim arrStr(1 To 1) As String
     Let arrStr(1) = "46"
     Let Ws12.Range("C72").Value = arrStr(1) ' this DOES NOT gives the number held as text thing
     Let Ws12.Range("C72").Value = arrStr()  ' gives the number held as text thing
     
     
     
     '  
     '  
End Sub