Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

[DocAElstein]

Coding for this main Thread post

https://www.excelfox.com/forum/showt...ll=1#post24132

'  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=18479&viewfull=1#post18479
Sub CarriageReturnLineFeedExcelVBAFormula()  '   https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24132&viewfull=1#post24132
Call TidyCellView2
Rem 2 Put formula in Cell
' 2a  CHAR(13)&CHAR(10)
 Let Range("B10") = "=""a""" & "&" & "CHAR(13)&CHAR(10)" & "&" & """b""" ' For VBA
' simplify a bit as  Evaluate(" ") can't return the  VBA  " & "  - it will simply join them
 Let Range("B10") = "=""a""&CHAR(13)&CHAR(10)&""b""" ' For VBA

Dim strEval As String
 Let strEval = """" & "=" & """" & """" & "a" & """" & """" & "&" & "CHAR(13)&CHAR(10)" & "&" & """" & """" & "b" & """" & """" & """"
Debug.Print strEval  '  "=""a""&CHAR(13)&CHAR(10)&""b"""
 Let Range("B10") = Evaluate(strEval)
 Let Range("B10") = Evaluate("""" & "=" & """" & """" & "a" & """" & """" & "&" & "CHAR(13)&CHAR(10)" & "&" & """" & """" & "b" & """" & """" & """")
Debug.Print strEval  '  "=""a""&CHAR(13)&CHAR(10)&""b"""
Debug.Print Evaluate("""" & "=" & """" & """" & "a" & """" & """" & "&" & "CHAR(13)&CHAR(10)" & "&" & """" & """" & "b" & """" & """" & """")    '   gives  ="a"&CHAR(13)&CHAR(10)&"b"   This is what gets put in the cell, the string seen in VBa
' final simplification - simply remove all   " & "   bits
Let Range("B10") = Evaluate("""=""""a""""&CHAR(13)&CHAR(10)&""""b""""""")
Let Range("B10") = Evaluate("=""=""""a""""&CHAR(13)&CHAR(10)&""""b""""""")

' 2b  vbCr & vbLf
' 2b(i)

' 2b(ii)
'    strEval = """" & "=" & """" & """" & "a" & """" & """" & "&" &           "CHAR(13)&CHAR(10)"           & "&" & """" & """" & "b" & """" & """" & """"
 Let strEval = """" & "=" & """" & """" & "a" & """" & """" & "&" & """" & """" & vbCr & vbLf & """" & """" & "&" & """" & """" & "b" & """" & """" & """"

Debug.Print strEval  '  "=""a""&""
'                       ""&""b"""
Debug.Print Evaluate(strEval)
 
 Let Range("C10") = Evaluate(strEval)
 Let Range("C10") = Evaluate("""" & "=" & """" & """" & "a" & """" & """" & "&" & """" & """" & vbCr & vbLf & """" & """" & "&" & """" & """" & "b" & """" & """" & """")
' final simplification - simply remove all   " & "   bits
 Let Range("C10") = Evaluate("""=""""a""""&""""" & vbCr & vbLf & """""&""""b""""""")
 Let Range("C10") = Evaluate("=""=""""a""""&""""" & vbCr & vbLf & """""&""""b""""""")




End Sub

Public Sub TidyCellView()    '  A few things to make things in the demo look closer to what they are, to help demo purpposes
    With Rows("1:4")
     .RowHeight = 30                                                '  So we see two rows in a cell.  Sometimes it might happen automatically if we are playing around with text of two lines, but not always - This is because Excel is quite good at guessing what we want to see, and changes thins appropriately but it does not always guess correctly
     .WrapText = True                                               '  This makes sure that rows Usually Excel guesses we want this when we are playing around with line breaks in cell text,but not always. If it does not geuss correct then it might show a multi-line text in this sort of form, with the lines tacked on to each other with no indication of any line break characters that may be there     Line1Line2Line3      etc.
     .VerticalAlignment = xlTop:     .HorizontalAlignment = xlLeft  '  Thes two makes sure any text we have will start top left, otherwise Excel may occaisionally mess this up and give us misleading views
    End With
 Let Application.FormulaBarHeight = 2
End Sub
Public Sub TidyCellView2()    '  A few things to make things in the demo look closer to what they are, to help demo purpposes
    With Rows("10")
     .RowHeight = 30                                                '  So we see two rows in a cell.  Sometimes it might happen automatically if we are playing around with text of two lines, but not always - This is because Excel is quite good at guessing what we want to see, and changes thins appropriately but it does not always guess correctly
     .WrapText = True                                               '  This makes sure that rows Usually Excel guesses we want this when we are playing around with line breaks in cell text,but not always. If it does not geuss correct then it might show a multi-line text in this sort of form, with the lines tacked on to each other with no indication of any line break characters that may be there     Line1Line2Line3      etc.
     .VerticalAlignment = xlTop:     .HorizontalAlignment = xlLeft  '  Thes two makes sure any text we have will start top left, otherwise Excel may occaisionally mess this up and give us misleading views
    End With
 Let Application.FormulaBarHeight = 2
End Sub