Tests and Notes on Range objects in Excel Cell

**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

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

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

SJHSASAHG

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

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

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

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

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

RangeObjectDefaultProperties(Methods?)SingleCells*

Single cell range objects and the .Value story
I am thinking that there may be some over simplification on the so called .Value Property, and this typical Pseudo code line, needs some re thinking
_________________ Range2.Value = Range1.Value
(*There could be distinct differences with the multi cell case, so I will consider that case separately)

LHS
_________________ Range2.Value = Range1.Value

My feeling is that the LHS can either be written as Range2 = or Range2.Value. So these are the same
_________________ Range2 _ = Range1.Value
_________________ Range2.Value = Range1.Value
.Value in such a situation is a perhaps poorly / vaguely/ imprecisely / incompletely defined as “Property”, (or by Default Property for the case that you miss it out) . This definition is perhaps some attempt to make it fit with Object Orientated Programing, but it’s a bit more vague/ imprecise/ not really so well fitting as things might more often be on average.
It is probably a bit better, IMO, to think of it (it being the LHS whether Range2 = or Range2.Value ) as writing text or numbers into the cell, specifically the numbers or text that is related to the RHS
Saying the same thing a bit differently, the LHS ( if the LHS is Range2 = or Range2.Value ) will take what’s on the RHS and put the associated text or number related to what is on the RHS

The .Value thing/ idea/Property/Default Property concept, call it what you like, but whatever it is on the LHS , it has something to do with writing in the cell something about what is on the RHS. I guess "putting in a value" and "writing something in" is saying something similar, so that is how the vague concept comes about of “Property”, or Default Value Property of the Range2 object
It’s debatable perhaps if the LHS of Range2 = or Range2.Value is a method, as it looks at what is on the RHS and gives some written attribute about it.

I think from now on, or for now from now, based on some of these recent investigations, I might prefer to use
Let Range2 =
, instead of
Range2.Value = or Let Range2.Value =
This helps me to remember that I am writing in numbers or text, related to what is on the RHS, probably either
, _ something from here:
https://i.postimg.cc/ryGZ6jb6/Writte...erty-Value.jpg
Written Property Value.JPG

, or
, _ perhaps something from here.
https://i.postimg.cc/k5N8264j/Writte...from-Value.jpg

Which of those will vary depending of the RHS, where it seems the exact syntax used does make a difference …….
, that will be considered in more detail under the RHS heading below, first a couple of side issues

……."values" The word values often refers generally to things in the cell numbers text and the specific formatting only that can be done with just numbers and text. It all is done by putting/ wring in a cell, and so it follows, or makes some sense/sensible correlation, that all these values, not just .Value can be used or not on the LHS and the effect is the same as having none of them. In other words, the following code lines all have the same effect in the Range2.Value = Range1.Value type code lines that we have been talking about
Range2.Value =
Range2 =
Range2.FormulaR1C1Local =
Range2.Formula =
Perhaps anytime we use any of these sort of Properties, we default to just Range2 =

…….Rem 1 Variable type considerations
It is not directly relevant to our Range2.Value = Range1.Value, considerations but indirectly when experimenting can be worth being aware of. If the LHS is a Variable, then things will not necessarily follow a similar, "Pattern", (set of rules, occurrences, or what ever you want to call it), to what we now consider , as we consider the RHS ………

RHS
I am thinking that possibly we ought to consider these 2 things as different
_________________ Range2.Value = Range1.Value
_________________ Range2.Value = Range1
The first one, ________________ Range2.Value = Range1.Value , will work similarly to something like ____ = Range1.Interior.Color , and in conjunction with the LHS as so far discussed, (Range2 = or Range2.Value) , it will give some number or text attribute, specifically for the thing asked for, in these two cases .Value or .Interior.Color
It won’t make any difference whether the LHS is Range2 = or Range2.Value, (as far as I can tell, it never makes a difference), the result will be the same , a number or text will be written in the cell, and if a text is written in the cell, which looks like a number, even if it is from a cell exhibiting the Number held as text thing , then we will get a number. In other words, if I have It seems to be doing similar what we as a human would, writing in the cell. Excel will then decide if it’s a number, text, or whatever. A text looking like a number, will be displayed as a number and we won’t get the Number held as text thing showing. This is important so just to say that again slightly differently: Using ____ = __.Value on the RHS to bring the value of cell exhibiting the Number held as text thing into another cell will not result in that other cell exhibiting the Number held as text thing : that warning thing will not appear in the other cell

Now it gets interesting, for the case of not including the ____ = __.Value on the RHS
It’s often said that in this situation Range2.Value = Range1 the default property, .Value will be used. But that does not seem to be quite right. I see it perfume a bit differently sometimes.
It may be only subtly different and I am not sure exactly what is going on, but a guess is that it is initially taking the range object, and then taking something from it. It then puts that in the cell, but in some other way.
Or , maybe it is taking this thing: https://i.postimg.cc/ryGZ6jb6/Writte...erty-Value.jpg
Written Property Value.JPG

In that last screen shot, for cell C69, where we have a number, showing as a number , we see 44
In this next screenshot, for cell A69, where we have a number but the cell is displaying the Number stored as text Thing , we see there " 44 "
https://i.postimg.cc/Xq1HsdJL/Cell-A69.jpg
Cell A69.JPG

This might be the "thing" that we sometimes get for various objects, when we assign that object to a string type variable. For some reason it works then differently, rather than "writing" into the cell in similar way that we might do, it does it in some other way such that the actual text is put in, and so we appear to get the Number stored as text Thing warning thing preserved.

Some coding in the next posts demonstrate some of the above