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