Right Hand Side Range Range Value values Range=Range.Value only sometimes. Range Range.Value Anomaly

**DocAElstein** · 08-14-2023, 12:17 PM

This Post is #23193 #3
https://www.excelfox.com/forum/showt...ll=1#post23193

Code:

'  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
'  https://www.excelfox.com/forum/showthread.php/2914-Tests-and-Notes-on-Range-objects-in-Excel-Cell/page2#Post21995   #12
Sub RangeOnlyRHS()
Rem 0 test data
Dim Ws As Worksheet
 Set Ws = ThisWorkbook.Worksheets("RangeValuevalues")
 Ws.Range("A1:D5").Clear
'0a) This puts an array into a cell.  VBA can do this. We cant
 Let Ws.Range("A1") = Split("44")    ' This puts a 1 element string array in, which causes the   Number held as Text thing   https://i.postimg.cc/CK1bnk7G/Number-held-as-Text-thing.jpg
 '                    Watch : - :  Split("44") :  : Variant/String(0 to 0) : Tabelle6.RangeOnlyRHS
 '                              :  Split("44")(0) : "44" : String : Tabelle6.RangeOnlyRHS
 Let Ws.Range("A1") = Split("44 55") ' This does the same thing, it puts the two element array in, (but we will only see the first value as we are just filling in the first cell
'                     Watch : + :  Split("44 55") :  : Variant/String(0 to 1) : Tabelle6.RangeOnlyRHS
'                               :  Split("44 55")(0) : "44" : String : Tabelle6.RangeOnlyRHS
'                               :  Split("44 55")(1) : "55" : String : Tabelle6.RangeOnlyRHS
Dim StrS(0 To 0) As String
 Let StrS(0) = "44"
 Let Ws.Range("A1") = StrS()
'                     Watch : + :  StrS() :  : String(0 to 0) : Tabelle6.RangeOnlyRHS
'                               :  StrS()(0) : "44" : String : Tabelle6.RangeOnlyRHS
 
'0b) For comparison, VBA is putting a single string value into a cell. We can also do that. When this happens I think Excel decides what the thing is, a number or text, so if it looks like a number, then it will make it a number
 Let Ws.Range("B1") = "44"
 Let Ws.Range("B1") = Split("44")(0) '
 Let Ws.Range("B1") = Split("44 55")(0)
 Let Ws.Range("B1") = StrS()(0)
'0c) Putting an array generally into a cell will not necerssarily get you that  Number held as Text thing
Dim VarVals() As Variant: Let VarVals() = Ws.Range("A1:B1") '         ( When assigning to a variable, other than a Range object, Excel probably has a different set of rules, which some people might regard as coercing to suit the variable )
'                     Watch : + : VarVals() :  : Variant/Variant(1 to 1, 1 to 2) : Tabelle6.RangeOnlyRHS
'                             + : VarVals()(1) :  : Variant(1 to 2) : Tabelle6.RangeOnlyRHS
'                               : VarVals()(1,1) : "44" : Variant/String : Tabelle6.RangeOnlyRHS
'                               : VarVals()(1,2) : 44 : Variant/Double : Tabelle6.RangeOnlyRHS
 Let Ws.Range("B1") = VarVals()
Rem 1 ' I am not fully sure what is going on here, but I might be returning this     https://i.postimg.cc/y83k0dH3/range-...ate-Window.jpg
 Let Ws.Range("A2") = Ws.Evaluate("=IF(1=1,A1)") ' Watch : + : Ws.Evaluate("=IF(1=1,A1)") : "44"  : Variant/Object/Range : Tabelle6.RangeOnlyRHS
 Let Ws.Range("A2") = Ws.Range("=IF(1=1,A1)") '       Watch : + : Ws.Range("=IF(1=1,A1)") : "44"  : Object/Range : Tabelle6.RangeOnlyRHS
 Let Ws.Range("A2") = Ws.Range("A1")                        ' Watch : + :  Ws.Range("A1") : "44"  : Object/Range : Tabelle6.RangeOnlyRHS
 Let Ws.Range("C2") = Ws.Range("A1:B2") ' This puts nothing in the cell , and there is nothing
 '   in the Immediate window   value   place either         Watch : + : Ws.Range("A1:B2") :       : Object/Range : Tabelle6.RangeOnlyRHS         https://i.postimg.cc/BvBQ2KRn/Multi-...ate-Window.jpg
Rem 2
 Let Ws.Range("A3") = "=A1"  ' Put a formula into a cell with  VBA  or  manually and I think maybe Excel decides what allignment to give you.
 '                                                          Watch : + : Ws.Range("A3") : "44" : Object/Range : Tabelle6.RangeOnlyRHS
 Let Ws.Range("B3") = "=B1"  '                              Watch : + : Ws.Range("B3") : 44  : Object/Range : Tabelle6.RangeOnlyRHS
 Let Ws.Range("C4:D4").FormulaArray = "=A1:B1"
 Let Ws.Range("A4:B4") = "=A1:B1" ' Works because of   Interception and Implicit Intersection    https://www.excelfox.com/forum/showt...on-and-VLookUp
  
 Let Ws.Range("A5") = Ws.Range("A3") ' Watch : + : Ws.Range("A3") : "44" : Object/Range : Tabelle6.RangeOnlyRHS
End Sub
Sub ObjectGivesString()
Dim Ws As Worksheet
 Set Ws = ThisWorkbook.Worksheets("RangeValuevalues")

Dim Obj As Object
 Set Obj = Excel.Application ' Watch : + : Excel.Application : "Microsoft Excel" : Object/Application : Tabelle6.ObjectGivesString
'                              Watch : + :  Obj : "Microsoft Excel" : Object/Application : Tabelle6.ObjectGivesString

 Let Ws.Range("A1") = Obj

Dim StrS(0 To 0) As String
 Let StrS(0) = "55"
 Let Ws.Range("A1") = StrS()
End Sub