Tests and Notes on Range objects in Excel Cell

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