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

Continued from last post ( https://excelfox.com/forum/showthrea...ll=1#post19863 )

Explanation of the two Match bits

Rem 1
Code:
Rem 1 try to match the name & Activity & Sub-activity
Dim arrD1() As Variant: Let arrD1() = WsD1.Evaluate("=A1:A25 & B1:B25 & C1:C25") ' This is a convenient way to get an array of the three things for all rows
Dim strSrch As String ': Let strSrch = Range("E2").Value & Range("F2").Value & Range("G2").Value ' This gives for example, "JohnA.1A.1.1"
Dim TgRw As Long: Let TgRw = Target.Row ' the changed target row
Let strSrch = Range("E" & TgRw & "").Value & Range("F" & TgRw & "").Value & Range("G" & TgRw & "").Value
Dim MtchRw As Long
Let MtchRw = Application.Match(strSrch, arrD1(), 0) ' this tries to match the correct row in Database1
This is fairly straight forward.
This is the array of name & Activity & Sub-activity

arrD1() =
NameActivitySub-activity
JohnA.1A.1.1
JohnA.1A.1.2
JohnA.2A.2.1
JohnA.2A.2.2
JohnA.3A.3.1
JohnA.3A.3.2
JohnA.4A.4.1
JohnA.4A.4.2
MarkA.1A.1.1
MarkA.1A.1.2
MarkA.2A.2.1
MarkA.2A.2.2
MarkA.3A.3.1
MarkA.3A.3.2
MarkA.4A.4.1
MarkA.4A.4.2
AnneA.1A.1.1
AnneA.1A.1.2
AnneA.2A.2.1
AnneA.2A.2.2
AnneA.3A.3.1
AnneA.3A.3.2
AnneA.4A.4.1
AnneA.4A.4.2


Now take for example the case of row 2 from worksheet database. I am looking to match this
JohnA.1A.1.1

So that is obviously 2 – ( Match tells me the position along in the array that it finds what it is looking for, the second position in this case is where it finds JohnA.1A.1.1 )



Rem 2 This is more tricky, and may not always work with different data. This initial solution may need to be re thought

This is the array of dates
arrDts() =
Name Activity Sub-activity 44866 44896 44927 44958 44986 45017 45047 45078

Now take for example from row 2, the date 30 November, 2022. Excel holds dates internally as numbers, and for the date 30 November, 2022 it has 44895.
I don’t have the number 44895 in my array. So this, Application.Match(DteV2, arrDts(), 0) , would give an error. It errors because it cannot find an exact match.

However we can use a 1 or -1 instead of the 0 in that final third argument in the Match - The syntax supports 3 different options, -1 0 1

If we choose 1 or -1 , then it will not error if it finds something close to what its looking for. The exact rules governing how that works are not so clear. I have a feeling Microsoft have some inconstant documentation in this.
For the case of using a 1 , we must have the numbers in the array in ascending order, which we have in the test data. I think then it tries to find the nearest smallest number.

Simple example to clarify:
In other words, if as a simple example, we had a 4 and a 9, in an array like this
{ 2 3 4 9 }
but we were lookig for a 8, then it would think that the matched value was 4, if we used a third argument of 1 in Match . So it would return us a 3 in that case as 4 is in position 3 along in the array.
In other words the 4 is the third number and its the nearest number to 8 that is smaller than 8. The number 9 is nearer, but that is a bigger number. (If we wanted that number 9 to get matched to 8, then I think the array must be in decending order, and we must use a -1 as the third argument in the Match


So...
Lets look again in detail at the test data and add some color highlighting to demo the results i get, which luckily are correct with the test data

The array: ( from Database1)
1
2
3
4
5
6
7
8
9
10
11
Name Activity Sub-activity
44866
44896
44927
44958
44986
45017
45047
45078


The 4 numbers I use from column D (from Database ) to get the results I want
44895
44895
44926
44985



If you follow the logic you can see that we will get the correct column numbers we want with the test data: We will get
4 4 5 7

But there are at least 3 issues to consider:

_1) This is relying on the date in column D in database being typically bigger than the corresponding date to be matched in Database1

_2) I am not sure how the Match always reacts to having a mixture of numbers and text in the array. It does not seem to cause a problem with the test data.

_3) The dates in Database1 must be typically in acending order. (I expect they probably willl be?)











( I added some extra stuff in the Database worksheet on the uploaded file to help show things a bit better )

https://i.postimg.cc/bNWqMYqT/1.jpg

https://i.postimg.cc/28Vz2LNy/2.jpg

https://i.postimg.cc/8kypRB3x/3.jpg



https://postimg.cc/TKSXsXV3

Some extra notes in support of this Thread post
https://excelfox.com/forum/showthrea...ll=1#post19860

I am using Match in two few places, and that is the main workings of the macro
Rem 1 - one gets the row number need for the output
Rem 2 – the other gets the column needed for the output

This is the test data given:





This is the requirement

Quote:

---

Originally Posted by Atlantis764

.....
- the value from H2 cell (Database) to be added in D2 cell (Database1) because the name is John, the Activity is A.1, Sub-activity is A.1.1 and the Month is November 2022 (with yellow)
- the value from H5 cell (Database) to be added in G20 cell (Database1) because the name is Anne, the Activity is A.2, Sub-activity is A.2.1 and the Month is February 2023 (with gray)
.......and so on

---

Next post, some further explanations , in particular about what the Match bits are about

Simple example to clarify:

Continued from last post ( https://excelfox.com/forum/showthrea...ll=1#post19863
https://www.excelfox.com/forum/showt...ll=1#post21921 )

Explanation of the two Match bits

Rem 1

Code:

---

Rem 1 try to match the  name & Activity & Sub-activity
Dim arrD1() As Variant: Let arrD1() = WsD1.Evaluate("=A1:A25 & B1:B25 & C1:C25") ' This is a convenient way to get an array of the three things for all rows
Dim strSrch As String ': Let strSrch = Range("E2").Value & Range("F2").Value & Range("G2").Value ' This gives for example,  "JohnA.1A.1.1"
Dim TgRw As Long: Let TgRw = Target.Row ' the changed target row
 Let strSrch = Range("E" & TgRw & "").Value & Range("F" & TgRw & "").Value & Range("G" & TgRw & "").Value
Dim MtchRw As Long
 Let MtchRw = Application.Match(strSrch, arrD1(), 0) ' this tries to match the correct row in Database1


---

This is fairly straight forward.
This is the array of name & Activity & Sub-activity

arrD1() =
NameActivitySub-activity
JohnA.1A.1.1
JohnA.1A.1.2
JohnA.2A.2.1
JohnA.2A.2.2
JohnA.3A.3.1
JohnA.3A.3.2
JohnA.4A.4.1
JohnA.4A.4.2
MarkA.1A.1.1
MarkA.1A.1.2
MarkA.2A.2.1
MarkA.2A.2.2
MarkA.3A.3.1
MarkA.3A.3.2
MarkA.4A.4.1
MarkA.4A.4.2
AnneA.1A.1.1
AnneA.1A.1.2
AnneA.2A.2.1
AnneA.2A.2.2
AnneA.3A.3.1
AnneA.3A.3.2
AnneA.4A.4.1
AnneA.4A.4.2


Now take for example the case of row 2 from worksheet database. I am looking to match this
John A.1 A.1.1

So that is obviously 2 – ( Match tells me the position along in the array that it finds what it is looking for, the second position in this case )

---

Rem 2 This is more tricky, and may not always work with different data. This initial solution may need to be re thought

This is the array of dates
arrDts() =
Name Activity Sub-activity 01-Nov-2022 44896 44927 44958 44986 45017 45047 45078

Now take for example from row 2, the date 30 November, 2022. Excel holds dates internally as numbers, and for the date 30 November, 2022 it has 44895.
I don’t have the number 44895 in my array. So this, Application.Match(DteV2, arrDts(), 0) , would give an error. It errors because it cannot find an exact match.

However we can use a 1 or -1 instead of the 0
If we do that it will not error if it finds something close to what its looking for. The exact rules governing how that works are not so clear. I have a feeling Microsoft have some inconstant documentation in this.
For the case of using a 1 , we must have the numbers its looking for in ascending order, which we have in the test data. I think then it tries to find the next smallest number.

Lets look again in detail at the test data and add some color highlighting to demo that

The array: ( from Database1)

1	2	3	4	5	6	7	8	9	10	11
Name	Activity	Sub-activity	44866	44896	44927	44958	44986	45017	45047	45078

The 4 numbers I use from column D (from Database ) to get the results I want

44895

44895

44926

44985

If you follow the logic you can see that we will get the correct column numbers we want with the test data: We will get
4 4 5 7

But there are at least two issues to consider:

_1) This is relying on the date in column D in database being typically bigger than the corresponding date to be matched in Database1

_2) I am not sure how the Match always reacts to having a mixture of numbers and text in the array. It does not seem to cause a problem with the test data.

Second solution, Solution 2 for this thread
https://excelfox.com/forum/showthrea...cell-in-sheet2

Code:

---

Sub ConsolidateLines_Solution2() '  https://excelfox.com/forum/showthread.php/2815-Copy-data-from-multiple-rows-between-two-keyword-and-paste-the-data-in-row(s)-of-single-cell-in-sheet2
Rem 0 worksheets data info
Dim Ws1 As Worksheet, Ws2 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1): Set Ws2 = ThisWorkbook.Worksheets.Item(2)
Dim Lr As Long: Let Lr = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
 
Rem 3 Initial to get started, finding first start point of text we want
 Dim RngStt As Range ' This will be the cell with the first  Keywrod1
  Set RngStt = Ws1.Range("A1:A" & Lr & "").Find(What:="Keywrod1", After:=Ws1.Range("A" & Lr & ""), LookIn:=xlValues, LookAt:=xlPart, Searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True)
Rem 4 main text manipulation
'4a)
    Do While Not RngStt Is Nothing ' This the main outer loop will terminate if we find no new first keyword #####
    Dim RngStp As Range ' This willl be the cell with the next  Keyword2
    Set RngStp = Ws1.Range("A" & RngStt.Row + 1 & ":A" & Lr & "").Find(What:="Keyword2", After:=Ws1.Range("A" & RngStt.Row + 1 & ""), LookIn:=xlValues, LookAt:=xlPart, Searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True)
        If RngStp Is Nothing Then Exit Do  ' This is for the case of if there is no Keyword2  after  a found  Keywrod1
'4b)
    Dim Rw As Long
        For Rw = RngStt.Row To RngStp.Row Step 1 ' We loop through the cells in between and including the cells with  Keywrod1  and  keyword2
        Dim NewCelStr As String ' This is used to build the string for a new cell
        Let NewCelStr = NewCelStr & Ws1.Range("A" & Rw & "").Value2 & vbLf  ' Add the next cell text followed by a new line character
        Next Rw
    Let NewCelStr = Left(NewCelStr, Len(NewCelStr) - 1)
    Dim Lr2 As Long: Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row
    Let Ws2.Range("A" & Lr2 + 1 & "").Value = NewCelStr
'4c(ii)
    Let NewCelStr = ""
    Set RngStt = Ws1.Range("A" & RngStp.Row & ":A" & Lr + 1 & "").Find(What:="Keywrod1", After:=Ws1.Range("A" & RngStp.Row + 1 & ""), LookIn:=xlValues, LookAt:=xlPart, Searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True)
    Loop ' While Not RngStt = Nothing '  ### Main outer loop terminates when main text manipulation is finished ##
 
 
 Ws2.Columns(1).WrapText = False
End Sub

---

Second solution, Solution 2 for this thread
https://excelfox.com/forum/showthrea...cell-in-sheet2

Code:

---

Sub ConsolidateLines_Solution2() '  https://excelfox.com/forum/showthread.php/2815-Copy-data-from-multiple-rows-between-two-keyword-and-paste-the-data-in-row(s)-of-single-cell-in-sheet2
Rem 0 worksheets data info
Dim Ws1 As Worksheet, Ws2 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1): Set Ws2 = ThisWorkbook.Worksheets.Item(2)
Dim Lr As Long: Let Lr = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
 
Rem 3 Initial to get started, finding first start point of text we want
 Dim RngStt As Range ' This will be the cell with the first  Keywrod1
  Set RngStt = Ws1.Range("A1:A" & Lr & "").Find(What:="Keywrod1", After:=Ws1.Range("A" & Lr & ""), LookIn:=xlValues, LookAt:=xlPart, Searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True)
Rem 4 main text manipulation
'4a)
    Do While Not RngStt Is Nothing ' This the main outer loop will terminate if we find no new first keyword #####
    Dim RngStp As Range ' This willl be the cell with the next  Keyword2
    Set RngStp = Ws1.Range("A" & RngStt.Row + 1 & ":A" & Lr & "").Find(What:="Keyword2", After:=Ws1.Range("A" & RngStt.Row + 1 & ""), LookIn:=xlValues, LookAt:=xlPart, Searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True)
        If RngStp Is Nothing Then Exit Do  ' This is for the case of if there is no Keyword2  after  a found  Keywrod1
'4b)
    Dim Rw As Long
        For Rw = RngStt.Row To RngStp.Row Step 1 ' We loop through the cells in between and including the cells with  Keywrod1  and  keyword2
        Dim NewCelStr As String ' This is used to build the string for a new cell
        Let NewCelStr = NewCelStr & Ws1.Range("A" & Rw & "").Value2 & vbLf  ' Add the next cell text followed by a new line character
        Next Rw
    Let NewCelStr = Left(NewCelStr, Len(NewCelStr) - 1)
    Dim Lr2 As Long: Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row
    Let Ws2.Range("A" & Lr2 + 1 & "").Value = NewCelStr
'4c(ii)
    Let NewCelStr = ""
    Set RngStt = Ws1.Range("A" & RngStp.Row & ":A" & Lr + 1 & "").Find(What:="Keywrod1", After:=Ws1.Range("A" & RngStp.Row + 1 & ""), LookIn:=xlValues, LookAt:=xlPart, Searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True)
    Loop ' While Not RngStt = Nothing '  ### Main outer loop terminates when main text manipulation is finished ##
 
 
 Ws2.Columns(1).WrapText = False
End Sub

---

In support of this forum post
https://eileenslounge.com/viewtopic.php?f=30&t=38895

This is Hans final working Solution

Code:

---

'    Hans  https://eileenslounge.com/viewtopic.php?p=300746#p300746
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Pee As String
On Error GoTo Myerror
 Let Application.EnableEvents = False
'    If Not Intersect(Target, Columns(1)) Is Nothing Then
        For Each Rng In Intersect(Target, Columns(1))
        Let Pee = Replace(Replace(Evaluate("Proper(""" & Rng.Value & """)"), " ", ""), "-", "")
            Select Case Pee
                Case "A"
                Let Rng.Value = "ABC"
                Case "Aa"
                Let Rng.Value = "XXD"
                Case Else
                Rng.ClearContents
            End Select
        Next Rng
'    End If
Myerror:
 Let Application.EnableEvents = True
End Sub


---

---

We can pretty this via Evaluate Range up in two main ways
_ The VBA Replace ideas can be replaced in the Evaluate by Excel spreadsheet SUBSTITUTE
_ The VBA Select Case ideas can be replaced by the Excel spreadsheet IF

The full coding example here follows approximately the Excel spreadsheet workings in the uploaded file. (In that file my workings are for the example range D11:D12 being copied and pasted into column 1, range A11:A12)
Note that in some steps the trick IF({1},___) was needed to get more than one result, but it was often found that in subsequent steps this could be removed. I expect this is because whatever phenomena is going on in that trick, goes on when we introduce steps involving IFs on multi cell ranges, so then the trick is redundant.

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)  '  https://excelfox.com/forum/showthread.php/2834-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=19028&viewfull=1#post19028
Dim Pee As String '  , Rng As Range
On Error GoTo Myerror
 Let Application.EnableEvents = False
    If Not Intersect(Target, Columns(1)) Is Nothing Then
    Dim varTest As Variant
    Let varTest = Evaluate("=IF({1},PROPER(" & Target.Address & "))")
    Let varTest = Evaluate("=IF({1},SUBSTITUTE(IF({1},PROPER(" & Target.Address & ")),"" "",""""))")
    Let varTest = Evaluate("=IF({1},SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""))")
    Let varTest = Evaluate("=IF({1},SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"",""""))")
    Let varTest = Evaluate("=IF(IF({1},SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"",""""))=""A"",""ABC"",""x"")")
    Let varTest = Evaluate("=IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""A"",""ABC"",""x"")")
    Let varTest = Evaluate("=IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""A"",""ABC"",IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""Aa"",""XXD"",""""))")
    'Let Target.Value2 = varTest
    Let Target.Value2 = Evaluate("=IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""A"",""ABC"",IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""Aa"",""XXD"",""""))")
'        For Each Rng In Intersect(Target, Columns(1))
'        Let Pee = Replace(Replace(Evaluate("Proper(""" & Rng.Value & """)"), " ", ""), "-", "")
'            Select Case Pee
'                Case "A"
'                Let Rng.Value = "ABC"
'                Case "Aa"
'                Let Rng.Value = "XXD"
'                Case Else
'                Rng.ClearContents
'            End Select
'        Next Rng
    Else
    ' case not pasted in column 1
    End If
Myerror:
 Let Application.EnableEvents = True
End Sub


---

In support of this forum post
https://eileenslounge.com/viewtopic.php?f=30&t=38895

This is Hans final working Solution

Code:

---

'    Hans  https://eileenslounge.com/viewtopic.php?p=300746#p300746
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Pee As String
On Error GoTo Myerror
 Let Application.EnableEvents = False
'    If Not Intersect(Target, Columns(1)) Is Nothing Then
        For Each Rng In Intersect(Target, Columns(1))
        Let Pee = Replace(Replace(Evaluate("Proper(""" & Rng.Value & """)"), " ", ""), "-", "")
            Select Case Pee
                Case "A"
                Let Rng.Value = "ABC"
                Case "Aa"
                Let Rng.Value = "XXD"
                Case Else
                Rng.ClearContents
            End Select
        Next Rng
'    End If
Myerror:
 Let Application.EnableEvents = True
End Sub


---

---

We can pretty this via Evaluate Range up in two main ways
_ The VBA Replace ideas can be replaced in the Evaluate by Excel spreadsheet SUBSTITUTE
_ The VBA Select Case ideas can be replaced by the Excel spreadsheet IF

The full coding example here follows approximately the Excel spreadsheet workings in the uploaded file. (In that file my workings are for the example range D11:D12 being copied and pasted into column 1, range A11:A12)
Note that in some steps the trick IF({1},___) was needed to get more than one result, but it was often found that in subsequent steps this could be removed. I expect this is because whatever phenomena is going on in that trick, goes on when we introduce steps involving IFs on multi cell ranges, so then the trick is redundant.

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)  '  https://excelfox.com/forum/showthread.php/2834-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=19028&viewfull=1#post19028
Dim Pee As String '  , Rng As Range
On Error GoTo Myerror
 Let Application.EnableEvents = False
    If Not Intersect(Target, Columns(1)) Is Nothing Then
    Dim varTest As Variant
    Let varTest = Evaluate("=IF({1},PROPER(" & Target.Address & "))")
    Let varTest = Evaluate("=IF({1},SUBSTITUTE(IF({1},PROPER(" & Target.Address & ")),"" "",""""))")
    Let varTest = Evaluate("=IF({1},SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""))")
    Let varTest = Evaluate("=IF({1},SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"",""""))")
    Let varTest = Evaluate("=IF(IF({1},SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"",""""))=""A"",""ABC"",""x"")")
    Let varTest = Evaluate("=IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""A"",""ABC"",""x"")")
    Let varTest = Evaluate("=IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""A"",""ABC"",IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""Aa"",""XXD"",""""))")
    'Let Target.Value2 = varTest
    Let Target.Value2 = Evaluate("=IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""A"",""ABC"",IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""Aa"",""XXD"",""""))")
'        For Each Rng In Intersect(Target, Columns(1))
'        Let Pee = Replace(Replace(Evaluate("Proper(""" & Rng.Value & """)"), " ", ""), "-", "")
'            Select Case Pee
'                Case "A"
'                Let Rng.Value = "ABC"
'                Case "Aa"
'                Let Rng.Value = "XXD"
'                Case Else
'                Rng.ClearContents
'            End Select
'        Next Rng
    Else
    ' case not pasted in column 1
    End If
Myerror:
 Let Application.EnableEvents = True
End Sub


---

spare post for later

spare post for later

This is post https://www.excelfox.com/forum/showt...ll=1#post21923
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21923&viewfull=1#post21923
Page #58 https://www.excelfox.com/forum/showt...s-etc-)/page58
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page58

---

Some notes arising from discussions in this Thread
http://www.eileenslounge.com/viewtop...61085a6575decc

---

Edit Aug 2023: The general ideas are OK here, but we all overlooked, or maybe did not understand fully some basic stuff about how Range and range "values" in this basic code line work, Range2.Value = Range1.Value. Because of this some of the stuff in the final actual solution are redundant: - in particular, the solution of 1x and related stuff.
See
https://www.excelfox.com/forum/showt...el-Cell/page10
https://www.excelfox.com/forum/showt...el-Cell/page11
https://www.excelfox.com/forum/showt...el-Cell/page12

Number stored as text, alignment of numeric values in cells

This problem/feature pops up from time to time.
https://i.postimg.cc/GpwT3Mzj/Number-stored-as-text.jpg
https://i.postimg.cc/18B3Jrkg/Number-stored-as-text.jpg Attachment 5104

I have seen it so far in these sort of places/ things, such as text box contents are strings by definition , …VBA enters text that is inherent to a TextBox. Every 'number' in a textBox is a string "1234". VBA has to convert those strings into long/integer, etc….

, and when pasting things out,
https://www.eileenslounge.com/viewto...269105#p269105 https://eileenslounge.com/viewtopic....272704#p272704
, but it’s not always consistent, - in the next example the 3 seems to go in as a number, but the 4 and 5 go in as …" Number stored as text"
https://i.postimg.cc/cCzwRfG1/Number...onsistancy.jpg

Code:

---

'  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21923&viewfull=1#post21923
Sub Number_stored_as_text__alignment_of_numeric_values_in_cells()  '      https://i.postimg.cc/cCzwRfG1/Number-stored-as-text-inconsistancy.jpg
Dim Ex As String
 Let Ex = "3"
 Let ActiveSheet.Range("A10").Value = Ex ' Puts in as number
Dim Exs(1 To 2) As String: Let Exs(1) = "4": Exs(2) = "5"
 Let ActiveSheet.Range("A11:B11").Value = Exs()
End Sub

---

https://i.postimg.cc/cCzwRfG1/Number...onsistancy.jpg

Number stored as text, alignment of numeric values in cells
Evaluate Range correction

The problem from the last post lends itself nicely to the Evaluate Range ideas. As often, this idea is based on a simple mathematical formula, which is itself one of a few ways in Excel and VBA we have a few ways to coerce a text to a number:
In Excel and VBA we have a few ways to coerce or "Methods to convert", http://www.eileenslounge.com/viewtop...309140#p309140
One simple way is to multiply by 1.
Edit Aug 2023: The general ideas are OK here, but we all overlooked, or maybe did not understand fully some basic stuff about how Range and range "values" in this basic code line work, Range2.Value = Range1.Value. Because of this some of the stuff in the final actual solution are redundant: - in particular, the solution of 1x and related stuff.
See
https://www.excelfox.com/forum/showt...el-Cell/page10
https://www.excelfox.com/forum/showt...el-Cell/page11
https://www.excelfox.com/forum/showt...el-Cell/page12



As ever with these Evaluate Range solutions, we start with a single cell formula which works in Excel.
Take the screenshot example from the last post. In C11 this appears to work, =1*A11
_____ Workbook: Number stored as text, alignment of numeric values in cells.xls ( Using Excel 2013 32 bit )

Row\Col	A	B	C
10	3
11	4	5	=1*A11
12

Worksheet: Sheet1
https://i.postimg.cc/prJFZt2s/1xA11.jpg
https://i.postimg.cc/prJFZt2s/1xA11.jpg Attachment 5105
What that simple formula appears to do is to make the 4 in cell A11 be written in cell C11 a true number, and so we effectively get rid of that warning of the form , "Number stored as Text"

Now the Evaluate Range bit.
Evaluate Range ideas are often a neat alternative to looping in VBA
Staying with Excel initially. (**Note: This explanation may not suit so well for an Excel version with the Spill/Dynamic arrays stuff, (currently from about version 365).)
In Excel, like in a lot of computer stuff, what we look at, or get as output to see, is based on something
chucking out from left to right
,then returning to the left, ( sometimes referred to as "invisible" character like Carriage Return vbCr )
, and then going to a new line/row, ( sometimes referred to as "invisible" character like LineFeed vbLf )
, then starting again until we get a 2 dimensional picture we as humans understand. In some cases this means that similar calculations may be done by default over an extended range, but normally we don’t see that as in Excel we look at doing stuff in one of excel boxes/cells. Somehow Excel will be wired to show a single value in a cell in a typical formula such as our =1*A11
Whether by design or accident, right from the start of when Excel came about, we can get some interesting results if we replace a single cell reference by a multiple one.
For example, in C11, write =1*A11:B11 instead, so as to maybe see if we can somehow do our basic formula for cells A11 and B11 at the same time.
But, try that and it will error**. Never mind. Let’s keep experimenting.
https://i.postimg.cc/k5CmNLgp/1x-A11-B11.jpg
https://i.postimg.cc/zBXY6WWw/1xA11B11.jpg
(** For Excel 365 it may not error, and I prefer not to consider yet what is happening there)

Type 1 CSE entry
There may be some reason such as extra overheads for Excel to show the same calculation for both cells, resulting in perhaps a deliberate prevention of the thing working, but my guess is that it does work without too much effort. Often we talk about Type 1 CSE entry, or CSE entry in general, as a way to tell Excel that you would like to take advantage of the possibility to do the calculation for a range other than a single cell.
To get the first inclination about what I am talking about, do this: do the same thing again, in C11, write =1*A11:B11 , but don’t hit Enter yet. Instead, hold keys Ctrl+Shift and then hit Enter
We don’t error anymore. But we still only see one value. https://i.postimg.cc/m2v5xMCx/1x-A11-B11-CSE1.jpg
If you apply a bit of lateral thinning to what we said earlier about how computer things work , ( ……chucking out from left to right , returning to the left and gong to a new line/row, then starting again ) , a good guess might be that in our example the other calculated value might be somewhere close to the right. That takes us nicely on to the next section, and how that relates to our example

Type 2 CSE entry
CSE generally seems to tell Excel to do multiple cell calculations, at least that is what we suggested. (We talk sometimes about "array calculations", but this may be strictly in mathematical definitions a bit incorrect)
But we generally write things in a cell, and generally for simple formulas we would expect a result in a cell.

But let's try it a bit differently, bearing in mind our example. So, start again, but before you type =1*A11:B11, first select the two cells, C11 and D11. Then after you type that, do the CSE entry again, not a simple Enter
https://i.postimg.cc/m2w2rQN9/1x-A11-B11-CSE2.jpg
Bingo!
https://i.postimg.cc/m2RgRdMR/1x-A11-B11-CSE2-Bingo.jpg
https://i.postimg.cc/z3trFF0P/1x-A11-B11-CSE2-Bingo.jpg Attachment 5106


Evaluate Range Solution as alternative to VBA Looping
So far we have just considered an Excel solution. That background was necessary to understand the Evaluate Range type Solution as alternative to VBA Looping
In VBA, a conventional way to do multi cell calculations would be to Loop. It loops, in other words does a similar codung a few times. It would need to do it twice, https://i.postimg.cc/Wb8ZRJM7/Loop-i...mn-A-and-B.jpg , in our example:

Code:

---

Sub LoopItTwice()  '  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21924&viewfull=1#post21924
Dim Nmbr As Long
    For Nmbr = 1 To 2 Step 1
    Let ActiveSheet.Cells.Item(12, Nmbr + 2).Value = ActiveSheet.Cells.Item(11, Nmbr).Value  '    Always uses row 12 ,  done twice, first  for  column 3 value = column 1 value    and  then  column 4 value = column 2
    Next Nmbr
End Sub

---

We have just discussed that the CSE stuff in Excel gives us a way to do it in one go. Let’s investigate if we can do something like that in VBA

Do Excel stuff in VBA
In VBA there is a Evaluate("__") function. In simple terms, to a first approximation, we can put Excel formulas inside the quotes, here
Evaluate("here")
, and the result is returned, similar to what would be returned in a cell with that formula in it.
Exactly what may be "returned" (chucked at us from Evaluate("__") ) , may be many things, depending on the exact formula, so it makes sense that the thing is wired to return a Variant type, so that can hold whatever actual type comes from the evaluation. So something like this sort of form would be done in VBA
Dim Var As Variant
_Let Var = Evaluate("=A11:B11*1")

If you put a Stop statement (or alternatively click in the left margin to get a brown circle which works the same), then run the following full macro, then while it is paused, (or before you start) , highlight Var , hold key Shift and then hit key F9, to put a watch on that variable, then you can see the results.
https://i.postimg.cc/vmPf13Cx/Put-a-watch-on-Var.jpg
https://i.postimg.cc/mDtM77GX/Put-a-watch-on-Var.jpg

Code:

---

Sub RangeEvaluate()
Dim Var As Variant
 Let Var = Evaluate("=A11:B11*1")

Stop  '    If you put a Stop statement (or alternatively click in the left margin to get a brown circle which works the same), then run the following full macro, then while it is paused, (or before you start) , highlight Var , hold key Shift and then hit key F9, to put a watch on that variable, then you can see the results.
'https://i.postimg.cc/vmPf13Cx/Put-a-watch-on-Var.jpg
'https://i.postimg.cc/mDtM77GX/Put-a-watch-on-Var.jpg
End Sub


---

https://i.postimg.cc/9Mb931YQ/Put-a-watch-on-Var.jpg
https://i.postimg.cc/9Mb931YQ/Put-a-watch-on-Var.jpg

Occasionally, the Evaluate(" ") does not "return" more than one result in formulas involving multi cell ranges, but this appears to be a separate issue to the need for CSE measures. Often we can do empirically derived tricks to overcome such problems.
But in many cases the Evaluate(" ") does returns the same results immediately that are only returned in a spreadsheet via the CSE way discussed. It would appear that principally the Evaluate(" ") is not restricted by the single cell , or need for CSE entry issues as in the spreadsheet. Perhaps this is because in use, Evaluate(" ") is not tied in some way to a particular spreadsheet format to show its output. There appears to be a lot of uncertainty ad empirically derived solutions and tricks in Evaluate Range

So, we have our result, or a way to get it chucked at us , "returned" , by using an Excel spreadsheet formula from within VBA
So what do we do with it/ how do we get it in a cell, or more specifically in our example, how do we get it in cells,
……. Next post

From last post, to recap a bit……
Do Excel stuff in VBA
In VBA there is a Evaluate("__") function. In simple terms, to a first approximation, we can put Excel formulas inside the quotes, here
Evaluate("here")
, and the result is returned, similar to what would be returned in a cell with that formula in it.
Exactly what may be "returned" (chucked at us from Evaluate("__") ) , may be many things, depending on the exact formula, so it makes sense that the thing is wired to return a Variant type, so that can hold whatever actual type comes from the evaluation. So something like this sort of form would be done in VBA
Dim Var As Variant
_Let Var = Evaluate("=A11:B11*1")

If you put a Stop statement (or alternatively click in the left margin to get a brown circle which works the same), then run the following full macro, then while it is paused, (or before you start) , highlight Var , hold key Shift and then hit key F9, to put a watch on that variable, then you can see the results.
https://i.postimg.cc/vmPf13Cx/Put-a-watch-on-Var.jpg
https://i.postimg.cc/mDtM77GX/Put-a-watch-on-Var.jpg

Code:

---

Sub RangeEvaluate()
Dim Var As Variant
 Let Var = Evaluate("=A11:B11*1")

Stop  '    If you put a Stop statement (or alternatively click in the left margin to get a brown circle which works the same), then run the following full macro, then while it is paused, (or before you start) , highlight Var , hold key Shift and then hit key F9, to put a watch on that variable, then you can see the results.
'https://i.postimg.cc/vmPf13Cx/Put-a-watch-on-Var.jpg
'https://i.postimg.cc/mDtM77GX/Put-a-watch-on-Var.jpg
End Sub


---

https://i.postimg.cc/9Mb931YQ/Put-a-watch-on-Var.jpg
https://i.postimg.cc/9Mb931YQ/Put-a-watch-on-Var.jpg

So, we have our result, or a way to get it chucked at us , "returned" , by using an Excel spreadsheet formula from within VBA
So what do we do with it/ how do we get it in a cell, or more specifically in our example, how do we get it in cells


Simple application of Evaluate("=A11:B11*1")type solutions
( Put what is chucked at us, ("returned") into cells )
Conveniently, Excel VBA allows a “field” or array of values, in other words a “square” or grid full of values to be applied directly to a rectangular range, pseudo code examples:

Simple application of Evaluate("=A11:B11*1") type solutions
Conveniently, Excel VBA allows a “field” or array of values, in other words a “square” or grid full of values to be applied directly to a rectangular range, pseudo code examples:

Range(“A1:B2”) values = { a b
_________________--__________c d }

Range(“A1:C1”) values = { x y z }

Range(“D1:E1”) values = { 4, 5 }

The right hand side of that last equation could be the array or field of values which was returned by our example, Evaluate("=A11:B11*1") . An actual working syntax to do in VBA what we did via CSE techniques previously would be
Range("C11:D11").Value = Evaluate("=A11:B11*1")

VBA coding “ works backwards “ , doing the right hand side first, so if we wanted to replace the original range with the values without the Number stored as text thing, then this would work

Range("A11:B11").Value = Evaluate("=A11:B11*1")

At this point we note that this basic coding layout can be written in a few different forms. In particular, we have great flexibility on the right hand side by virtue of the fact that the Evaluate takes a string argument. We are free to build up strings in VBA as we choose. They can be built up from hard coded text, as we currently have, or we can also include VBA things which would be converted at run time to what they evaluate to. For example, Range("A11:B11").Address returns $A$11:$B$11 , which for our purposes, in spreadsheet syntax, as used in a spreadsheet cell, has the same effect as A11:B11. So that last code line could be written

Range("A11:B11").Value = Evaluate("=" & Range("A11:B11").Address & "*1")


The last two code lines are effectively replacing a range which might have the Number stored as text thing showing to a range without that showing. In other words, it removes the Number stored as text thing.
We are free to replace the range Range("A11:B11") with any range we choose.
_ It could be some variable defined as a range like this
Dim Rng As Range
Set Rng = Range("A11:B11")
Let Rng.Value = Evaluate("=" & Rng.Address & "*1")
_ Similarly, we could make some selection in the spreadsheet, and then run the macro on this range using
Let Selection.Value = Evaluate("=" & Selection.Address & "*1")
This last macro is often the most convenient, but bear in mind that it will replace the original range, and things done by VBA can’t be undone. Best would be to test first by pasting a copy in a spare range. For example, if you have data up to column F, then this would put the test range to the side of it.
Selection.Offset(0, 6).Value = Evaluate("=" & Selection.Address & "*1")
(As often in VBA, the Offset works on the top left, shifting the whole range with it. In the inner workings of Excel VBA, things are often anchored or referenced to the top left)

In a few posts time we some simple variations of the Evaluate range way to remove the Number stored as text thing issue, specifically we are making the last few coding version less susceptible to problems / more workable on different data types
But first we will recap and discuss a useful typical format / versions of our final coding for test and developments






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

Number stored as text, alignment of numeric values in cells

Improved/ Extended Evaluate Range solution. Recap General Purpose solution
In a few posts time we will discusses extending the last solution to overcome a couple of problems that may occur with certain types of range data, specifically, if there may be empty cells or cells with real text in it. In this and the following 2 posts we will recap and come up with a general purpose solution to then go on and modify.

General all purpose version of our final Evaluate Range solution Range variable, Rng
Let us both standardise and recap on a good general purpose version of our Evaluate Range solution, with emphasis on learning and test and developing

Range variable, Rng
It’s probably more often than not a good idea to use a variable for our main data range. In technical terms we might re say that, as a variable for our main Range object. Using a variable for things is often a good idea, and particular a good idea for a range object, since Excel and Excel is all about boxes and manipulating them, and in VBA in particular, we have a lot of useful built in things like Methods, Properties, and the such, that we can use on them/ with them, in particular the range object since that is in particular to do with the box, and boxes structure that Excel is all about.
So for some test range, such as A1:F7,
https://i.postimg.cc/nctNH0WJ/Range-A1-F7.jpg
Attachment 5113 https://i.postimg.cc/nM032YZR/Range-A1-F7.jpg
, then somewhere towards the start of any macro we might have something like this
Dim Rng As Range
_Set Rng = Worksheets.Item("Sheet1").Range("A1:F7")
You can often get away without the Worksheets.Item("Sheet1"). , if you know what you are doing and are sure that Excel will guess correctly which worksheet you are wanting to refer to, but often in a final coding, Excel may guess wrong, so more fully referencing a range is a good habit to get into.
We may as well, from the start of our coding get fully into such good habits, and use a variable for the worksheet also
Dim Ws1 As Worksheet
_Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
Dim Rng As Range
_Set Rng = Ws1.Range("A1:F7")
This Rng is referring to the test data range……._

General all purpose version of our final Evaluate Range solution Range variable, The left hand side, LHS, of a general purpose code line.
The left hand side, LHS, of a general purpose code line.
_....... This Rng is referring to the test data range.
We want to modify/ correct that, so up until now that was the thing on the LHS.
But, it is convenient in test and development not to replace the original range, but perhaps rather, if you have space, put the output from the coding calculations somewhere alongside. That way if something goes wrong, you don’t have to try and find the original data in order to try again. For example this would be the LHS to place the result alongside to the right. _Let Rng.Offset(0, Rng.Columns.Count).Value =

That is a good choice to use as we are sure to place to the right, since we offset the output a number of column equal to the number of columns that the range has. Personally, I might do this
_Let Rng.Offset(0, Rng.Columns.Count + 1).Value =
, since an extra empty column in between can have later advantages.



















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

General all purpose version of our final Evaluate Range solution Range variable, The right hand side, RHS, of a general purpose code line.
The right hand side, RHS, of a general purpose code line.
As far as the RHS of the equation goes, that is our Evaluate Range bit, and that will want to be referencing our test data, which is simply Rng, as far as VBA is concerned – that is the whole point about using a variable,
However we need to remember that we need Excel spreadsheet syntax inside the Evaluate("____") . For simple hardcoding that is =A1:F7 , just as we would write it in the cell. We could also use fixed cell referencing, =$A$1:$F$7 , as for our purposes it would work just the same. So they are OK for us.
For our general purpose standardised solution we want to make it dynamic, in other words we don’t want to hard code to a specific range, but would like to be able to get the Excel syntax from our VBA Rng variable, regardless of what the actual range might be at any time in our test and development
The VBA Range.Address Property (Excel) is useful here ( https://learn.microsoft.com/en-us/of....range.address )
The VBA Range.Address Property
The VBA Range.Address Property has a few arguments, here is the first 4
RowAbsolute:= default value is True, which gives the absolute $ type references $A$1:$F$7 , which are OK for us
ColumnAbsolute:= default value is True, which gives the absolute $ type references $A$1:$F$7 , which are OK for us
ReferenceStyle:= default gives us the default Excel spreadsheet column Letter and row Number referencing ( in other words A1, B$3 etc., rather than RC[-1] stuff ) which we want
External:= This could do with a bit of discussion:
If we set this to True, which is not the default we are arguably doing things a bit better since we are ensuring we refer to the correct worksheet, (and the correct workbook).
https://i.postimg.cc/TYCc8Fc4/Range-...ernal-True.jpg
https://i.postimg.cc/zGvFq7tX/Range-...ernal-True.jpg
https://i.postimg.cc/jS1Q2fm6/Range-...ernal-True.jpg
https://i.postimg.cc/LXmjVN2z/Range-...ate-Window.jpg
And/ but note a couple of things,
_Although the full syntax is allowed in the worksheet, ( https://i.postimg.cc/jS78Gv9p/Full-e...-Worksheet.jpg
https://i.postimg.cc/fTF221H7/Full-W...-Worksheet.jpg ) , it is not necessary in the worksheet, since the formula is in the worksheets, so the extra syntax is redundant
_Evaluate(" ") is not necessarily always linked to a worksheet, so the full reference could be a good idea.
However, relying on the defaults for the VBA Range.Address Property on the RHS helps us to simplify the RHS equation, so we will not do this, but instead note*** we can do this better, more fuller referencing in another way, Later***.

So the point of this post was to produce a general purpose Evaluate Range type solution to then go on and extend, and this last bit was considering the RHS
Hard coded had in it of this form for the data range, , A1:F7 , and we noted that this will do as well, $A$1:$F$7
We can get that by relying on the defaults in the The VBA Range.Address Property and so can , in VBA get something like that from Rng.
https://i.postimg.cc/5jdHjw5R/Range-...Properties.jpg
Attachment 5114 https://i.postimg.cc/sGmfNGNY/Range-...Properties.jpg

Code:

---

Sub DevelopmentTest()  '  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21964&viewfull=1#post21964
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
Dim Rng As Range
 Set Rng = Ws1.Range("A1:F7")
Debug.Print Rng.Address '  gives im Immediate Window        $A$1:$F$7      ( After running macro,  hit  keys    Ctrl+g    from  VB Editor to get Immediate window( Direktbereich in German), in which this code line should have put the value of    Rng.Address(External:=True)      something like this sort of form      '[Number stored as text, alignment of numeric values in cells.xls]Sheet1'!$A$1:$F$7
Debug.Print Rng.Address(External:=True) ' After running macro,  hit  keys    Ctrl+g    from  VB Editor to get Immediate window( Direktbereich in German), in which this code line should have put the value of    Rng.Address(External:=True)      something like this sort of form      '[Number stored as text, alignment of numeric values in cells.xls]Sheet1'!$A$1:$F$7
End Sub


---

We want this result , $A$1:$F$7, to be available inside Evaluate(" ")
Possibly one of the most useful things about Evaluate(" ") is that it takes a string of text, in other words, what goes in between the " ") is in the syntax of what you would put in a worksheet cell. In VBA we are free to use actual hardcoded text and/ or VBA things to build up that text within VBA, following the usual VBA syntax rules of having text and other things joined by a __&___ Actual hard coded text, as you want in the worksheet formula goes inside a " ") pair. VBA things in VBA syntax should not be inside a " ") pair, as then VBA will first “do them” , and as long as they return a text string, the _&__ or _&_s will finally result in one final string.
If we are only using a single VBA thing, and nothing else, then " ") no pair is required, since the use of a [FONT=Courier New]" ")/FONT] pair in VBA coding tells VBA that it is being given actual hardcoded text. When the VBA thing is done, it returns a string, or text, which any _&__s would join to other text. What I am trying to say here is that to get the text $A$1:$F$7 inside Evaluate(" "), then this would do
Evaluate(Rng.Address)

As far as what VBA “sees”, that is the same as if you were to write this
Evaluate("$A$1:$F$7")

A few oddities
It is not clear why you do not need a leading =, but it will accept it, and these return the same as if the = was ommited.
Evaluate("=" & Rng.Address)
Evaluate("=$A$1:$F$7")
My guess is that VBA assumes you have it/ effectively adds it by default if you miss it out. (Perhaps we should note here that we cannot write a simple text in Evaluate("__") as we would in a cell. This would error Evaluate("xyz")
Evaluate("__") will however take a number. This would not error Evaluate("6")
So there is not a perfect correlation between what we put in Evaluate("___" ) and what we put in a cell.
Perhaps Evaluate("__") is associated with all things in a cell to do with numbers and calculations. Simple text writing in a cell is something extra for a worksheet and slightly outside what Excel is about. Excel is mainly about boxes with numbers in it, and calculations on them


Almost finished, back to the main issue
Personally, even if I am just using a VBA thing in the Evaluate("__"), I prefer to include both leading and trailing " " bits, like this
Evaluate("=" & Rng.Address & "")
Doing that has the advantage, for example, of making it more easy to build up a final string.
In our case, for example, we finally want =1*A1:F7 ( or =1*$A$1:$F$7 will do just as well )
I think it is fairly easy at this stage to see what we finally need on the RHS:
__________________Evaluate("=1*" & Rng.Address & "")

One final Useful Tip
A final useful Tip, that helps to make sure you halve the correct syntax inside the Evaluate("___ ")
If we try to be very precise, I think we can say that it is the thing inside the brackets in this, Evaluate(___ ) , that takes the syntax of a string. Now, bearing that in mind, along with the fact that in VBA coding an enclosing " " needs to be used to tell VBA that you are giving it text, we can either hard code like this
_________________________ = Evaluate("=A1")
, or use a string variable like this
Dim strEval As String
_ Let strEval = "=A1"
_________________________ = Evaluate(strEval)
( We must always remember the strange exceptional difference between what we can put in a cell and what we can put in = Evaluate("___") – Unlike in a cell, it will not take a simple text , in Evaluate("___") it must be a formula or number. )

It is very easy to get text and text shown in enclosing "s mixed up, since often in various writings an enclosing " " pair may or may not be used. As an approximate general rule we can often say that
_ (i) a medium predominantly used for displaying normal human readable basic written text, will not display the text in enclosing "s.
_ (ii) a medium which uses text but may not predominantly be used to display normal human readable basic written text, or a medium not used to display some specific normal human readable basic written text that comes from somewhere else, (for example, when quoting some text from somewhere else) will enclose the normal human readable basic written text in enclosing "s.
VBA is like (ii) . It needs to be or otherwise coding might get mistaken for simple normal human readable basic written text


The Immediate Window, is like (i) , because its primary use is to allow us to see the single value in a simple variable, or one element of an array.
OK, now the Tip
What is inside the " " here, Evaluate(" ") or the actual text in our variable strEval is what we would manually put in an Excel cell.
So if we use
Debug.Print strEval
, then what is seen in the Immediate window should have the correct syntax for what we would manually write in an Excel cell. If it is not then, Evaluate(strEval) will most likely error.
Using our previous example,

Code:

---

Sub CheckSyntaxForEvalString()
Dim Ws1 As Worksheet, Rng As Range
 Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
 Set Rng = Ws1.Range("A1:F7")
Dim strEval As String
 Let strEval = "=1*" & Rng.Address & ""
Debug.Print strEval
End Sub

---

, we can clearly see that we have the correct syntax
https://i.postimg.cc/GpLGBTPQ/Debug-Print-str-Eval.jpg
Attachment 5115 https://i.postimg.cc/G4fHV258/Debug-Print-str-Eval.jpg
https://i.postimg.cc/GpLGBTPQ/Debug-Print-str-Eval.jpg

Number stored as text, alignment of numeric values in cells

Improved/ Extended Evaluate Range solution.

Based on all the posts so far on this page, we have got this far,

Code:

---

Sub Number_stored_as_text()  '    https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21967&viewfull=1#post21967
Dim Ws1 As Worksheet, Rng As Range
 Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
 Set Rng = Ws1.Range("A1:F7")
Dim strEval As String
 Let strEval = "=1*" & Rng.Address & ""
Debug.Print strEval '  gives =1*$A$1:$F$7

 Let Rng.Offset(0, Rng.Columns.Count + 1).Value = Evaluate(strEval)
End Sub

---

This is our test data, https://i.postimg.cc/KvhPVN44/Test-Data.jpg
https://i.postimg.cc/8PkRNCBV/Test-Data.jpg
Attachment 5116https://i.postimg.cc/0rZKK7Yq/Test-Data.jpg https://i.postimg.cc/8PkRNCBV/Test-Data.jpg
_____ Workbook: Number stored as text, alignment of numeric values in cells.xls ( Using Excel 2013 32 bit )

Row\Col	A	B	C	D	E	F
1	Page	Letter	ordernr	Quant	Cost	Cost/quant
2	11	22	33	44	55
3	11	22	33	44	55
4	55	44	33	22	11	0,5
5	55	44	33	22	11	0,5
6	44	Y	23457	34	60,7	1,7568741
7	55	X	2234	34	160,7	4,60458453

Worksheet: Sheet1

This is the result after running the macro

_____ Workbook: Number stored as text, alignment of numeric values in cells.xls ( Using Excel 2013 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Page	Letter	ordernr	Quant	Cost	Cost/quant		#WERT!	#WERT!	#WERT!	#WERT!	#WERT!	#WERT!
2	11	22	33	44	55			11	22	33	44	55	0
3	11	22	33	44	55			11	22	33	44	55	0
4	55	44	33	22	11	0,5		55	44	33	22	11	0,5
5	55	44	33	22	11	0,5		55	44	33	22	11	0,5
6	44	Y	23457	34	60,7	1,7568741		44	#WERT!	23457	34	60,7	1,756874
7	55	X	2234	34	160,7	4,60458453		55	#WERT!	2234	34	160,7	4,604585

Worksheet: Sheet1
https://i.postimg.cc/v8V73jYp/Test-Data-After.jpg


Oh bollox, a couple of problems
We see a couple of problems. An empty cell comes out as a 0 in the output, and text in any cell results in an error in the output.

In the next post, we look at overcoming these two problems

Number stored as text, alignment of numeric values in cells

Improved/ Extended Evaluate Range solution.

Problem 1 text produces error in output.
Is it text or is it a number. Not as simple a question as you might think.
Initially a Layman might expect this problem, since multiplying a text by a number sounds dodgy. But we never know exactly how the makers of Excel program things, so we might just as well have thought it might return one of the text, or just recognise the text and so decide to do nothing but return it. But it appears that it errors.
So what to do.
The point of using these Evaluate Range ideas is to get a simple single code line that appears to do all in one go what more conventionally would be done in classic looping techniques. So initial we would usually see if we can solve the problem in the spreadsheet formula / expression.
We need something to help us distinguish between text and numbers, bearing in mind we have the complication of some numbers held as text. We want something that will distinguish between what we see as numbers and text, regardless of how Excel is holding anything we see as a number.
We have an ISTEXT and ISNUMBER function in Excel.
If we do some simple testing on the test data range we find that the ISTEXT will return true for our cells with a number stored as text, but not for cells with a simple number. So that looks perhaps less useful to us. Initially I did not expect the ISNUMBER to be useful either, since the Microsoft documentation say something like ISNUMBER("19") will return False.
Initial experiments confirm this. Take a small sample of our test range, A6:B7
https://i.postimg.cc/jdBJLVDM/Range-A6-B7.jpg
Attachment 5117https://i.postimg.cc/8jBC00Bv/Range-A6-B7.jpg
https://i.postimg.cc/jdBJLVDM/Range-A6-B7.jpg

Stop
Time to think again, before going further
Aug 2023: The general ideas were/ are OK here so far, but we all overlooked, or maybe did not understand fully some basic stuff about how Range and range "values" in this basic code line work, Range2.Value = Range1.Value. Because of this some of the stuff in the final actual solution are redundant: - in particular, the solution of 1x and related stuff.
See
https://www.excelfox.com/forum/showt...el-Cell/page10
https://www.excelfox.com/forum/showt...el-Cell/page11
https://www.excelfox.com/forum/showt...el-Cell/page12










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

This post https://www.excelfox.com/forum/showt...age2#post21998 Post #15

---

Some notes on support of these Threads and posts
https://eileenslounge.com/viewtopic....309151#p309151
https://www.excelfox.com/forum/showt...ll=1#post22078

Why did we Stop after the last few posts
We need to start again with the Evaluate(" "), that is to say the RHS _____ = Evaluate(" ") thing , and consider at the same time the RHS range thing _____ = Range(" ")

_ RHS _____ = Evaluate(" "), and RHS range object assignment thing _____ = Range(" ")
There seems to be some similarity in things they do
What is Evaluate(" "), with emphasis with what goes on in a spreadsheet cell
(Or what is going on in a cell after typing = ). I think maybe that when in a [cell] you write = , it can be considered as similar to this pseudo coding in VBA
[write in Cell] = Evaluate("What you put in the cell after the = ")
In simple terms, to a first approximation, Evaluate(" ") allows you to do in VBA within the " " what you do after the = in a cell.
([color=grey]So perhaps its understandable that
_ you can omit the = within the " " in Evaluate(" "), although it is accepted syntaxly: Perhaps it is then just redundant* since Evaluate is about doing what is after the = in an Excel cell
_ You can’t just put simple text in evaluate, as you can with a cell, you need to do it as
___ ' 1a)(i) you can additionally give text in a cell, as part of a formula, ( Example In cell ="Text in Formula in Excel"
___ ' 1a)(ii) or carefully tell the Evaluate(" ") to take/Evaluate a literal string, rather than Evaluate the string as if it were something after a = in a cell, which is what Evaluate is about*
Range objects in spreadsheets and Excel worksheet functions returning a range object. )
When we use cells in a spreadsheet, we may appear to be using simple text, numbers and formulas. Often we may have a formula which finally gives us a number. In some cases we may indeed get a simple number result. Within a formula, cell references may appear to just get the number or text from a cell, but in fact Excel manipulates range objects. Once you have some basic VBA knowledge, this will perhaps make sense, especially if you are aware that it is something similar to VBA that makes what you see work: Think of Excel spreadsheets as lots of functions that trigger when you change things in cells.
Bearing this in mind, it is perhaps reasonable to expect that things like Evaluate("=A1") , Evaluate("A1:B2") etc. in VBA might return a range object. Indeed they do!
You must make sure to do a Set them to a range object on the LHS,
Set Rng = Evaluate("=A1")
If you Let them to a Variant variable, (or for the case of a single cell, any suiting variable type), then VBA seems to be written to oblige and give an array of values (or a value for a single cell)

Code:

---

Sub RangeEvaluate()  ' https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=22078&viewfull=1#post22078
Rem 0
Dim Ws As Worksheet
 Set Ws = ThisWorkbook.Worksheets("RangeValuevalues")
 
Ws.Range("B1").Clear
Rem 1 write in Cell, a value and something else - I want to do this cell writing from VBA  ="Text in Formula in Excel"
' 1a)(i)Excel value is =      "    Text in Formula in Excel    "    in other words a formula
 Let Ws.Range("B1") = "=" & """" & "Text in Formula in Excel" & """"  ' This is like manually writing  ="Text in Formula in Excel"  in a cell, a formula    or like saying is to be evaluated  "Text in Formula in Excel"
                    Ws.Range("B1") = "=""Text in Formula in Excel""" ' simplid Alternative - take out all  " & "  since in VBA they stop text" and lin& to "start of another text
Ws.Range("B1").Clear  '      is      "    =    "      "      Text in Formula in Excel    "      "    "
Let Ws.Range("B1") = Evaluate("=" & """" & "=" & """" & """" & "Text in Formula in Excel" & """" & """" & """"): Debug.Print "=" & """" & "=" & """" & """" & "Text in Formula in Excel" & """" & """" & """"  '  It is always worth doing this to see what text  Evaliate("  ")  actually sees inside the  "  " , in this case it gives in  Immediate Window    ="=""Text in Formula in Excel"""
    Ws.Range("B1") = Evaluate("=""=""""Text in Formula in Excel""""""") ' Alternative    https://i.postimg.cc/W3n3Q2ST/Remove-some-in-VBA-Quote-Ambersand-Quote.jpg
' 1a)(ii) use Evaluate to put simple text in a cell, sort of, it actually does an evaluation of the literal string, perhaps a combination of the  =  and then the final  carriage return
Ws.Range("B1").Clear:                                    Debug.Print "=""Text in Formula in Excel"""        '  It is always worth doing this to see what text  Evaliate("  ")  actually sees inside the  "  "    It gives in this case  ="Text in Formula in Excel"  But this is not quite the equivalent of what you would write in a cell, since  Excel would not try to evaluate  and error instead https://i.postimg.cc/htQPr8dL/Wtire-Text-in-Formula-in-Excel-in-a-Cell-and-it-will-error.jpg
                      Ws.Range("B1").Clear:  Let Ws.Range("B1") = Evaluate("=""Text in Formula in Excel"""): Debug.Print "=""Text in Formula in Excel""" ' gives ="Text in Formula in Excel"    or  try    ? "=""Text in Formula in Excel"""  ="Text in Formula in Excel"      This is extra to  Evaluate(" ")  - it is attempting to evaluate a string of  Text in Formula in Excel, like  =Text in Formula in Excel  and it recognises that as a text, so puts that in the cell    Watch :  : Evaluate("=""Text in Formula in Excel""") : "Text in Formula in Excel" : Variant/String : Tabelle6.RangeEvaluate

---

'1b) Something Else
In the first cell, A1, we have a number held as text. The range object seems to know this, and we find that if we try to write that in another cell, ( Let LHS = ) then we don’t get an error, but rather the text is put in. This is a strange thing, and its discussed in more detail somewhere else, ( Post is #21995 Page2 #12 https://www.excelfox.com/forum/showt...age2#Post21995 ) , but important for our considerations here is that it is a range object phenomena, so in '1b) we compare what happens for the RHS of Range(" ") or Evaluate(" ").
The result is the same.
In fact, not known to many people, some Excel functions also return range objects, ( https://eileenslounge.com/viewtopic....280942#p280942
https://www.myonlinetraininghub.com/...#comment-84892 February 20, 2021 at 3:18 am ). and we find that the behaviour in what happens for the RHS of Range(" ") or Evaluate(" ") is consistent for this as well, it’s the same again

Code:

---

'1b) Something Else    https://www.youtube.com/watch?v=Td3r55oyiu0
Ws.Range("A2").Clear
 Let Ws.Range("A2") = Ws.Range("=IF(1,A1)")    ' Watch : + : Ws.Range("=IF(1,A1)") : "44" : Object/Range : Tabelle6.RangeEvaluate
Ws.Range("A2").Clear
 Let Ws.Range("A2") = Ws.Evaluate("=IF(1,A1)") ' Watch : + :  Ws.Evaluate("=IF(1,A1)") : "44" : Variant/Object/Range : Tabelle6.RangeEvaluate

---

So far so good.

What’s all this got to do with my Evaluate range one liner answer here: https://eileenslounge.com/viewtopic....309137#p309137
Well, I was trying to get a number held as text to change to a number, (to get rid of the little warning and the alignment difference to that for a number). My solution was based on pseudo code of CellValue x 1, as this is a typical way in Excel, or computing generally, to get a text that looks like a number to become a true number.
It worked.
But then when I looked at this in more detail, I thought it shouldn’t
https://www.excelfox.com/forum/showt...el-Cell/page10
https://www.excelfox.com/forum/showt...el-Cell/page11
, and then I went right back to re learn about range objects in particular with reference to the typical code line of
Range2.Value = Range1.Value
https://www.excelfox.com/forum/showt...el-Cell/page12


I know enough now to start again.
https://www.excelfox.com/forum/showt...ll=1#post23185
https://www.excelfox.com/forum/showt...ll=1#post23186
https://www.excelfox.com/forum/showt...ll=1#post23189

Some *more notes, (* second time around) in support of this forum post
https://eileenslounge.com/viewtopic....28c881#p309137
Excel - alignment of numeric values in cells? https://eileenslounge.com/viewtopic....310023#p310023 https://eileenslounge.com/viewtopic....310024#p310024

---

I did a solution who’s basic form started like this , pseudo coding

Code:

---

  Range2 =  Evaluate("=IF(ISNUMBER(A1:F7),1*A1:F7, A1:F7)")

---

The logic was that
_ if ISNUMBER( ) recognised a number held as text in any cells, then those cells would be multiplied by 1 to change them to normal numbers
_ the final modified range would then be returned/ put in the Range2
It seemed to work. Well it does. But my logic is up the spout, totally.
_ For one thing, ISNUMBER( ) does not recognise a number held as text as a number https://www.excelfox.com/forum/showt...ll=1#post22079
_ On the right hand side, RHS, of a typical code line like this ,
Range2.Value = Range1
, if Range1 is a multi cell range, then nothing at all is returned!!! #21995 #12 https://www.excelfox.com/forum/showt...age2#Post21995

So what the fuck is going on…..
Take a look at the coding in the next posts, https://www.excelfox.com/forum/showt...age2#Post21995
https://www.excelfox.com/forum/showt...ll=1#post23186
We are looking at various variations of code lines with a similar RHS Evaluate(" ") to that in my original initial solution. We apply the result to both
_ a test range, which corresponds to the LHS idea of Range2.Value = Range1
_ we also apply it to a Variant variable
_ we also apply it to a Variant variable
We use the test range A6:A7 which has a number held as text and also a normal number
https://i.postimg.cc/fLXddyGQ/A6-A7.jpg
Attachment 5508 https://i.postimg.cc/vgYDFY80/A6-A7.jpg
https://i.postimg.cc/fLXddyGQ/A6-A7.jpg

In the coding I have put the results of the Watch Window in the 'comments

Now let’s analyse some of those results
https://www.excelfox.com/forum/showt...ll=1#post23189
https://www.excelfox.com/forum/showt...ge59#post23189

Coding for last post
https://www.excelfox.com/forum/showt...ll=1#post23185

Code:

---

Sub EvaluateSmarter()
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim vTemp As Variant
 Let Range("A10:A11") = Evaluate("=A6:A7") ' Empty!    Watch : + : Evaluate("=A6:A7") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                                      Watch : + : Range("A10:A11") :  : Object/Range : Sheet1.EvaluateSmarter
 Let vTemp = Evaluate("=A6:A7")                      ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                              - : vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                                  : vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                              + : vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                                  : vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
'                                                      Watch : + : Evaluate("=A6:A7") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 Let Ws1.Range("A10:A11") = Ws1.Evaluate("=A6:A7") ' Empty!    Watch : + :  Ws1.Evaluate("=A6:A7") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                                              Watch : + : Ws1.Range("A10:A11") :  : Object/Range : Sheet1.EvaluateSmarter
 Let ActiveSheet.Range("A10:A11") = Ws1.Evaluate("=A6:A7") ' Empty!  Watch : + :  Ws1.Evaluate("=A6:A7") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 '                                                                  Watch : + :  ActiveSheet.Range("A10:A11") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 ' I am in the worksheet object code module, so Range and Ws1.Range are likely the same
 Let Range("A10:A11") = Evaluate("=IF(1=1,A6:A7)") ' Empty  Watch : + :  Evaluate("=IF(1=1,A6:A7)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 Let vTemp = Evaluate("=IF(1=1,A6:A7)") '  Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
 '                                                - : vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
 '                                                  : vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                  + : vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                    : vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF({1},A6:A7)") ' Watch : - :  Evaluate("=IF({1},A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
 '                                                          - :  Evaluate("=IF({1},A6:A7)")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                              :  Evaluate("=IF({1},A6:A7)")(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                            - :  Evaluate("=IF({1},A6:A7)")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                              :  Evaluate("=IF({1},A6:A7)")(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 Let vTemp = Evaluate("=IF({1},A6:A7)") '  Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                  - :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                    :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
 '                                                - :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
 '                                                  :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=1*A6:A7") 'Watch : + :  Evaluate("=1*A6:A7") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                          + : Evaluate("=1*A6:A7")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                            : Evaluate("=1*A6:A7")(1,1) : 44 : Variant/Double : Sheet1.EvaluateSmarter
'                                          + : Evaluate("=1*A6:A7")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                            : Evaluate("=1*A6:A7")(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
'                                        Watch : + : Range("A10:A11") :  : Object/Range : Sheet1.EvaluateSmarter

 Let vTemp = Evaluate("=1*A6:A7") '        Watch : + : vTemp :    : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                            + : vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                              : vTemp(1,1) : 44 : Variant/Double : Sheet1.EvaluateSmarter
'                                            + : vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                              : vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter

 Let Range("A10:A11") = Evaluate("=IF(ROW(),A6:A7)") 'Watch : + :  Evaluate("=IF(ROW(),A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                  + : Evaluate("=IF(ROW(),A6:A7)")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                    : Evaluate("=IF(ROW(),A6:A7)")(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                  + : Evaluate("=IF(ROW(),A6:A7)")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                    : Evaluate("=IF(ROW(),A6:A7)")(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
'                                                Watch : + : Range("A10:A11") :  : Object/Range : Sheet1.EvaluateSmarter

 Let vTemp = Evaluate("=IF(ROW(),A6:A7)") 'Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                            + :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                              :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                            + :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                              :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(1=1,A6:A7,A6:A7)") ' Empty Watch : + :  Evaluate("=IF(1=1,A6:A7,A6:A7)"):  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                              Watch : + :  Range("A10:A11") :  : Object/Range : Sheet1.EvaluateSmarter
'                                          Print Evaluate("=IF(1=1,A6:A7,A6:A7)").Address
'                                                            $A$6:$A$7
 Let Range("A10:A11") = Evaluate("=IF(1=1,A6:A7,A6:A10)") 'Watch : + : Evaluate("=IF(1=1,A6:A7,A6:A10)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                          print  Evaluate("=IF(1=1,A6:A7,A6:A10)").address
'                                                            $A$6:$A$7
 Let Range("A10:A11") = Evaluate("=IF(1=1,A6:A10,A6:A7)") 'Watch : + : Evaluate("=IF(1=1,A6:A10,A6:A7)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                          Print Evaluate("=IF(1=1,A6:A10,A6:A7)").Address
'                                                            $A$6:$A$10
 
 Let vTemp = Evaluate("=IF(1=1,A6:A7,A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                + :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                  :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                + :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                  :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(1=1,A6:A7,1*A6:A7)") ' Empty Watch : + : Evaluate("=IF(1=1,A6:A7,1*A6:A7)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
'                                                                  Watch : + : Range("A10:A11")  :    :  Object/Range : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(1=1,A6:A7,1*A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                      + : vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                        : vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                      + : vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                        : vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(1=1,1*A6:A7,A6:A7)") ' Watch : + : Evaluate("=IF(1=1,1*A6:A7,A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                        + : Evaluate("=IF(1=1,1*A6:A7,A6:A7)")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                          : Evaluate("=IF(1=1,1*A6:A7,A6:A7)")(1,1) : 44 : Variant/Double : Sheet1.EvaluateSmarter
'                                                        + : Evaluate("=IF(1=1,1*A6:A7,A6:A7)")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                          : Evaluate("=IF(1=1,1*A6:A7,A6:A7)")(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(1=1,1*A6:A7,A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                    + : vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                      : vTemp(1,1) : 44 : Variant/Double : Sheet1.EvaluateSmarter
'                                                    + : vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                      : vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(1=0,1*A6:A7,A6:A7)") ' Empty Watch : + :  Evaluate("=IF(1=0,1*A6:A7,A6:A7)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(1=0,1*A6:A7,A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
Dim Var(1 To 2, 1 To 1) As Variant
 Let Var(1, 1) = "44": Let Var(2, 1) = 55      ' Watch : + : Var :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                        - : Var(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                          : Var(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                        - : Var(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                          : Var(2,1) : 55 : Variant/Integer : Sheet1.EvaluateSmarter
Dim V1(1 To 1) As Variant: Let V1(1) = "44": Dim V2(1 To 1) As Variant: Let V2(1) = 55
 Let Var(1, 1) = V1(1): Let Var(2, 1) = V2(1)
'                                                Watch : - : V2 :  : Variant/Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                          : V2(1) : 55 : Variant/Integer : Sheet1.EvaluateSmarter
'                                                Watch : + : V1 :  : Variant/Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                          : V1(1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                Watch : + : Var :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                        - : Var(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                          : Var(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                        - : Var(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                          : Var(2,1) : 55 : Variant/Integer : Sheet1.EvaluateSmarter
Let Var(1, 1) = V1(): Let Var(2, 1) = V2()    '  Watch : + : Var :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                    - : Var(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            + : Var(1,1) :  : Variant/Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                              : Var(1,1)(1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                    - : Var(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            + : Var(2,1) :  : Variant/Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                              : Var(2,1)(1) : 55 : Variant/Integer : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(1,A6:A7,A6:A7)") ' Empty Watch : + :  Evaluate("=IF(1,A6:A7,A6:A7)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(1,A6:A7,A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

---

More codong for post
https://www.excelfox.com/forum/showt...ll=1#post23185

Code:

---

Let Range("A10:A11") = Evaluate("=IF(1,A6:A7,A6:A7)") ' Empty Watch : + :  Evaluate("=IF(1,A6:A7,A6:A7)") :  : Variant/Object/Range : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(1,A6:A7,A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)") ' Watch : + : Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                    - : Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                      : Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)")(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                    - : Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                      : Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)")(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(ISTEXT(A6:A7),A6:A7,A6:A7)") ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter
 
 Let Range("A10:A11") = Evaluate("=IF(ISTEXT(A6:A7),A6:A7)") ' Watch : + : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                          - : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)")(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)")(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                          - : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)")(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)")(2,1) : Falsch : Variant/Boolean : Sheet1.EvaluateSmarter
 
 Let vTemp = Evaluate("=IF(ISTEXT(A6:A7),A6:A7)")  '  Watch : + : Evaluate("=IF(ISTEXT(A6:A7),A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                          + :  vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                            :  vTemp(2,1) : Falsch : Variant/Boolean : Sheet1.EvaluateSmarter


Let Range("A10:A11") = Evaluate("=IF(ISNUMBER(A6:A7),1*A6:A7,A6:A7)") ' Watch : + : Evaluate("=IF(ISNUMBER(A6:A7),1*A6:A7,A6:A7)") :  : Variant/Variant(1 to 2, 1 to 1) : VBAProject.Sheet1.EvaluateSmarter
'                                                      + : Evaluate("=IF(ISNUMBER(A6:A7),1*A6:A7,A6:A7)")(1) :  : Variant(1 to 1) : VBAProject.Sheet1.EvaluateSmarter
'                                                        : Evaluate("=IF(ISNUMBER(A6:A7),1*A6:A7,A6:A7)")(1,1) : "44" : Variant/String : VBAProject.Sheet1.EvaluateSmarter
'                                                      + : Evaluate("=IF(ISNUMBER(A6:A7),1*A6:A7,A6:A7)")(2) :  : Variant(1 to 1) : VBAProject.Sheet1.EvaluateSmarter
'                                                        : Evaluate("=IF(ISNUMBER(A6:A7),1*A6:A7,A6:A7)")(2,1) : 55 : Variant/Double : VBAProject.Sheet1.EvaluateSmarter

Let vTemp = Evaluate("=IF(ISNUMBER(A6:A7),1*A6:A7,A6:A7)")  ' Watch : + : vTemp :  : Variant/Variant(1 to 2, 1 to 1) : Sheet1.EvaluateSmarter
'                                                              + : vTemp(1) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                                : vTemp(1,1) : "44" : Variant/String : Sheet1.EvaluateSmarter
'                                                              + : vTemp(2) :  : Variant(1 to 1) : Sheet1.EvaluateSmarter
'                                                                : vTemp(2,1) : 55 : Variant/Double : Sheet1.EvaluateSmarter





End Sub

Sub ArrayShenanigans()

End Sub

Sub Sidiary()
Dim Strs() As String
 Let Strs() = Split("44 55")  '  Watch : - : Strs() :  : String(0 to 1) : Sheet1.Sidiary
'                                    : Strs()(0) : "44" : String : Sheet1.Sidiary
'                                    : Strs()(1) : "55" : String : Sheet1.Sidiary
Dim Sids(0 To 1) As String
 Let Sids(0) = "44": Let Sids(1) = "55"
'                                Watch : - : Sids :  : String(0 to 1) : Sheet1.Sidiary
'                                    : Sids(0) : "44" : String : Sheet1.Sidiary
'                                    : Sids(1) : "55" : String : Sheet1.Sidiary
Dim S1(1 To 1) As String: Let S1(1) = "44"
Dim S2(1 To 1) As String: Let S2(1) = "55"
 Let Sids(0) = S1(1): Let Sids(1) = S2(1)
'                                Watch : - : Sids :  : String(0 to 1) : Sheet1.Sidiary
'                                    : Sids(0) : "44" : String : Sheet1.Sidiary
'                                    : Sids(1) : "55" : String : Sheet1.Sidiary
Dim Sids21(1 To 2, 1 To 1) As String '    Watch : - : Sids21 :  : String(1 to 2, 1 to 1) : Sheet1.Sidiary
 Let Sids21(1, 1) = "a":            '            + : Sids21(1) :  : String(1 to 1) : Sheet1.Sidiary
 Let Sids21(2, 1) = "c"              '                : Sids21(1,1) : "a" : String : Sheet1.Sidiary
'                                                  + : Sids21(2) :  : String(1 to 1) : Sheet1.Sidiary
'                                                      : Sids21(2,1) : "c" : String : Sheet1.Sidiary
'
Dim Sids12(1 To 1, 1 To 2) As String '    Watch : + :  Sids12 :  : String(1 to 1, 1 to 2) : Sheet1.Sidiary
 Let Sids12(1, 1) = "a": Sids12(1, 2) = "b"  '    + :  Sids12(1) :  : String(1 to 2) : Sheet1.Sidiary
'                                                        :  Sids12(1,1) : "a" : String : Sheet1.Sidiary
'                                                        :  Sids12(1,2) : "b" : String : Sheet1.Sidiary
'
Dim Sids22(1 To 2, 1 To 2) As String '    Watch : - : Sids22 :  : String(1 to 2, 1 to 2) : Sheet1.Sidiary
 Let Sids22(1, 1) = "a": Sids22(1, 2) = "b"  '    - : Sids22(1) :  : String(1 to 2) : Sheet1.Sidiary
 Let Sids22(2, 1) = "c": Sids22(2, 2) = "d"    '          : Sids22(1,1) : "a" : String : Sheet1.Sidiary
'                                                        : Sids22(1,2) : "b" : String : Sheet1.Sidiary
'                                                + : Sids22(2) :  : String(1 to 2) : Sheet1.Sidiary
'                                                        : Sids22(2,1) : "c" : String : Sheet1.Sidiary
'                                                        : Sids22(2,2) : "d" : String : Sheet1.Sidiary




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

---

_................continued from https://www.excelfox.com/forum/showt...ll=1#post23185

Here the test range agaon, A6:A7 which has a number held as text and also a normal number
https://i.postimg.cc/fLXddyGQ/A6-A7.jpg
Attachment 5508 https://i.postimg.cc/vgYDFY80/A6-A7.jpg
https://i.postimg.cc/fLXddyGQ/A6-A7.jpg
In the coding in the last two post, https://www.excelfox.com/forum/showt...ll=1#post23188
https://www.excelfox.com/forum/showt...ll=1#post23186 , we apply the result to both
_ a test range, which corresponds to the LHS idea of Range2.Value = Range1
_ we also apply it to a Variant variable

Initially we are expecting the RHS, Evaluate(" ") to be returning a range object, ( when it is Let on the LHS to a range ) , even for those code lines using the Excel worksheet function IF, since that is one of the Excel worksheet functions that returns a range object ( https://eileenslounge.com/viewtopic....280942#p280942
https://www.myonlinetraininghub.com/...#comment-84892 )

We also see that when we apply the result to a Variant variable, and as typical, we see that Excel seems to oblige to take the values from the returned range object from the RHS

In many of the code lines we confirm this in the 'comments showing the Watch Window results. However we see that sometimes the results retuned to the LHS are identical for the case of LHS the variable or a range.
Hmm … what’s going on there…
Well this phenomena is not so unusual. We see often see a similar phenomenon, or rather we try to make it happen, when
_ something is not giving us array results when we would like to have them,
_ we then empirically try various tricks to get the array of results. Often we end up with some seemingly redundant or extra thing in the expression, that whilst not directly linked to what we are doing, seems to as a by-product make the expression or function return multi values.
You can see in the various code line variations some of the tricks often done. My favourite, a very simple one, is if I have something not returning multiple values as I wish , then this modification usually does it:
Change
something not returning multiple values as I wish
, to:
IF({1}, something not returning multiple values as I wish )

In addition to such a simple trick, we often find that as progress with developing a solution with more things in the expression, then something we do inadvertently does the same thing.
Looking at the very last code line ,
=IF(ISNUMBER(A6:A7),1*A6:A7,A6:A7) ' returns range object in Evaluate(" ")
, and comparing that with results for one such as this
=IF(1=1,1*A6:A7,A6:A7) ' returns array of values in Evaluate(" ")
, tends to suggest that the ISNUMBER(A6:A7) is doing the trick. So the 1* was not doing anything –( it couldn’t because ISNUMBER(A6) does not return true https://www.excelfox.com/forum/showt...ll=1#post22079 ) , but the whole thing was returning an array of values in a Variant element type array, and as we found out here, https://www.excelfox.com/forum/showt...ll=1#post22084 , even if we have strings in the elements, they are not put in as a string, rather Excel seems to correct them to a number

That’s it. We now figured out what was going on. In the next post we give a final solution based on all we have learnt in the past few weeks…..
Excel Functions ISTEXT and ISNUMBER https://www.excelfox.com/forum/showt...el-Cell/page10
Explanations for my original Evaluate Range one liner until I realised I messed up https://www.excelfox.com/forum/showt...s-etc-)/page58
Starting again with hindsight https://www.excelfox.com/forum/showt...s-etc-)/page59
Range Object Default Properties (Methods?) https://www.excelfox.com/forum/showt...el-Cell/page12
code line type Range2.Value = Range1.Value Question from the web https://www.excelfox.com/forum/showt...ll=1#post23192









Have another think about a solution , next post



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

This is post 23187
https://www.excelfox.com/forum/showt...ll=1#post23187
https://www.excelfox.com/forum/showt...ge59#post23187

---

The new solution
Having done all the donkey work, and now, knowing probably better than anyone what it is all about we can go straight into some solutions.

Code:

---

Sub SimplerSolutionDevelopment()    '    https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23187&viewfull=1#post23187
Rem 0 test data range
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim Rng As Range
 Set Rng = Worksheets.Item("Sheet1").Range("A1:F7")
Rem 1
Rng.Offset(0, Rng.Columns.Count + 1).Clear

'1a These do nothing, and we know why now  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
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Range("A1:F7")
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("A1:F7")
'1b(i) a trick will do it
' Let Rng.Offset(0, Rng.Columns.Count + 1) = Range("IF({1},A1:F7)") ' Run time error  '1004'
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("IF({1},A1:F7)")
 'Watch : - : Evaluate("IF({1},A1:F7)") :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
'                              https://i.postimg.cc/nCt7df7P/Evaluate-IF-1-A1-F7.jpg
'1b(ii)
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")
' Watch : + : Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))") :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
'                              https://i.postimg.cc/4H3KLxMT/Evalua...IF-1-A1-F7.jpg

'1b(iii)
 Rng.Offset(0, Rng.Columns.Count + 1).Clear
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("IF(A1:F7="""","""",A1:F7)")
' Watch : + : Evaluate("IF(A1:F7="""","""",A1:F7)") :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
'                              https://i.postimg.cc/6T6G5MQ1/Evalua...1-F7-A1-F7.jpg


'1c)
 Rng.Offset(0, Rng.Columns.Count + 1).Clear
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Range("A1:F7").Value(RangeValueDataType:=xlRangeValueDefault)
 ' Watch : + : Range("A1:F7").Value :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
 '                            https://i.postimg.cc/grh8q2wh/Range-A1-F7-Value.jpg
 Rng.Offset(0, Rng.Columns.Count + 1).Clear
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("A1:F7").Value(RangeValueDataType:=xlRangeValueDefault)
 ' Watch : + : Evaluate("A1:F7").Value :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
 '                            https://i.postimg.cc/xX5KJpXF/Evaluate-A1-F7-Value.jpg
End Sub

---

Conclusions / A final solution(s)

In the coding above, we do a very simple trick in '1b(i) to get the array of values passed to the LHS, this sort of thing
Range2 = Evaluate("IF({1},A1:F7)")

Code:

---

https://i.postimg.cc/DySgw2zn/Evaluate-IF-1-A1-F7.jpg
.
.
.
.
..
.
.
.
.
..
.
.
.
.
..
.
.
.

---

That is OK, it almost gets what we want, but we see that we have the problem of just an empty cell returning 0 in the tweaked Evaluate(" ") solution, https://i.postimg.cc/4NF4tH97/proble...d-Evaluate.jpg
: Evaluate("IF({1},A1:F7)")(2,6) : 0 : Variant/Double : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF({1},A1:F7)")(3,6) : 0 : Variant/Double : Sheet1.SimplerSolutionDevelopment
https://i.postimg.cc/Xpd06Jx4/proble...d-Evaluate.jpg
We had this problem in the original solution as well, https://eileenslounge.com/viewtopic....309137#p309137 ,….. If you have any cells in the selection that are empty, then you had better use this next version instead, or else it will change empty cells into a cell with a 0 in it …..
This is perhaps understandable because we are doing mathematical calculations in VBA in Variants, and I am taking a guess that VBA calculations default to 0. Or some similar explanation.
As in the original solution, we can remove that problem by a modification, as in '1b(ii) , so we have like
Range2 = Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")
In fact, as we often find, the extra bit does the trick, so we can remove the IF({1},____) and so simplify the solution to like as in '1b(iii)
Range2 = Evaluate("IF(A1:F7="""","""",A1:F7)")
( One small thing to note with '1b(ii) and '1b(iii) is that the Empty is replace by / shown as "" , a zero length String ""
: Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")(2,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")(3,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF(A1:F7="""","""",A1:F7)")(2,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF(A1:F7="""","""",A1:F7)")(3,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment )

Code:

---

Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")                  Evaluate("IF(A1:F7="""","""",A1:F7)")
https://i.postimg.cc/HnwQLJ9j/Evalua...IF-1-A1-F7.jpghttps://i.postimg.cc/Wpgm8Kcj/Evalua...1-F7-A1-F7.jpg
.
.
..
.
.
.
.
.
.
..
.
.
.
.
.
.
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

---

_.______________________

Well, we got as far as we did before with the original solution, but done it correctly this time.
But we can do another offering:
See next post

Another solution
Instead of relying on a trick to change the RHS Evaluate("A1:F7") to return an array of values instead of a range object, we can keep the range object, and apply the .Value(RangeValueDataType:=xlRangeValueDefault) Property / Method thing to it, which returns an array of values. (We can also do that to the usual range object, Range("A1:F7")
In '1c) we do that and get very similar results, and have the advantage that the empty cell(s) are returned as empty cells without needing to do anything further
Here the results for that compared to the very first Evaluate("IF({1},A1:F7)")

Code:

---

    Evaluate("IF({1},A1:F7)")                                Range("A1:F7").Value                            Evaluate("A1:F7").Value
https://i.postimg.cc/v85zgQFs/Evaluate-IF-1-A1-F7.jpg https://i.postimg.cc/zvwS9kSB/Range-A1-F7-Value.jpg https://i.postimg.cc/7LcV7hZx/Evaluate-A1-F7-Value.jpg


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

---

test


Excel Functions ISTEXT and ISNUMBER https://www.excelfox.com/forum/showt...el-Cell/page10
Explanations for my original Evaluate Range one liner until I realised I messed up https://www.excelfox.com/forum/showt...s-etc-)/page58
Starting again with hindsight. The new solution https://www.excelfox.com/forum/showt...s-etc-)/page59
Range Object Default Properties (Methods?) https://www.excelfox.com/forum/showt...el-Cell/page12
code line type Range2.Value = Range1.Value Question from the web https://www.excelfox.com/forum/showt...ll=1#post23192






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

Later

In support of these forum posts
https://www.excelfox.com/forum/showt...ll=1#post23981
https://eileenslounge.com/viewtopic....313971#p313971

Developing an Evaluate Range solution
In column A we have things like this: (just showing the first few words in each row for clarity )
Shell.OmitFromView -- PKEY_Shell_Omi …..
SimpleRating -- PKEY_SimpleRating // Type: UInt32 – VT…..
Size -- PKEY_Size ……….// Type: UInt64 – VT
SoftwareUsed -- PKEY_SoftwareUsed// Type: Strin …
SourceItem -- PKEY_SourceItem // Typ …….
I only want this sort of thing
Shell.OmitFromView
SimpleRating
Size
SoftwareUsed
SourceItem
, so the first character set before any spaces
Spreadsheet Formula
So lets try to get those character sets in the fourth column ( Column D ) ,.
Building up a spreadsheet formula to do that is easy. Here is an example:
_____ Workbook: WSO_PropNamesExtended.xls ( Using Excel 2013 32 bit )

Row\Col	A	B	C
2	Address.Country -- PKEY_Address_Country // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 100 DEFINE_PROPERTYKEY(PKEY_Address_Country, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 100); #define INIT_PKEY_Address_Country { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 100 }	=SEARCH(" -- PKEY",A2)	=LEFT(A2,B2-1)
3	Address.CountryCode -- PKEY_Address_CountryCode // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 101 DEFINE_PROPERTYKEY(PKEY_Address_CountryCode, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 101); #define INIT_PKEY_Address_CountryCode { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 101 }	=SEARCH(" -- PKEY",A3)	=LEFT(A3,B3-1)
4	Address.Region -- PKEY_Address_Region // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 102 DEFINE_PROPERTYKEY(PKEY_Address_Region, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 102); #define INIT_PKEY_Address_Region { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 102 }	=SEARCH(" -- PKEY",A4)	=LEFT(A4,B4-1)

Worksheet: Ext(Hidden)proph
_____ Workbook: WSO_PropNamesExtended.xls ( Using Excel 2013 32 bit )

Row\Col	A	B	C
2	Address.Country -- PKEY_Address_Country // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 100 DEFINE_PROPERTYKEY(PKEY_Address_Country, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 100); #define INIT_PKEY_Address_Country { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 100 }	16	Address.Country
3	Address.CountryCode -- PKEY_Address_CountryCode // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 101 DEFINE_PROPERTYKEY(PKEY_Address_CountryCode, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 101); #define INIT_PKEY_Address_CountryCode { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 101 }	20	Address.CountryCode
4	Address.Region -- PKEY_Address_Region // Type: String -- VT_LPWSTR (For variants: VT_BSTR) // FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 102 DEFINE_PROPERTYKEY(PKEY_Address_Region, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 102); #define INIT_PKEY_Address_Region { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 102 }	15	Address.Region

Worksheet: Ext(Hidden)proph

Now, in column D we simply paste the last working formula , ( from column C ) , and then look from the left of column C so as to get the formula only referring to the first column. That way we can do away with the columns B and C

_____ Workbook: WSO_PropNamesExtended.xls ( Using Excel 2013 32 bit )

Row\Col	D
2	=LEFT(A2,SEARCH(" -- PKEY",A2)-1)
3	=LEFT(A3,SEARCH(" -- PKEY",A3)-1)
4	=LEFT(A4,SEARCH(" -- PKEY",A4)-1)

Worksheet: Ext(Hidden)proph

VBA Evaluate Range solution.
The simple direct Evaluate Range implementation would be
Evaluate("LEFT(A2:A1055,SEARCH("" -- PKEY"",A2:A1055)-1)")
As sometimes happens we find that this needs a little trick to get all the available values, since otherwise just the first value is returned.
This final working Evaluate Range line does the trick,
Evaluate("IF({1},LEFT(A2:A1055,SEARCH("" -- PKEY"",A2:A1055)-1))")


Here is a full coding to get the property name words in column E in the uploaded file

Code:

---

Option Explicit
'  https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23979&viewfull=1#post23979
Sub ExtendedPropertiesList()
' Rem 1 Get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1)                                    ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "propkey h.txt"  '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
'Get #FileNum, , TotalFile
'  Or  http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
 Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum
' Rem 2 Split the Prophs
Dim arrProphs() As String: Let arrProphs() = Split(TotalFile, "//  Name:    System.", -1, vbBinaryCompare)
' 2a) Quick look at list
Dim LCnt As Long: Let LCnt = UBound(arrProphs())
Dim Rws() As Variant, Clms() As Variant, VertList() As Variant
 Let Rws() = Evaluate("ROW(1:" & LCnt + 1 & ")/ROW(1:" & LCnt + 1 & ")")
 Let Clms() = Evaluate("ROW(1:" & LCnt + 1 & ")")
 Let VertList() = Application.Index(arrProphs(), Rws(), Clms())
 Let Me.Range("A1:A" & LCnt & "") = VertList()
Me.Cells.WrapText = False
'' 2b) Look at some example props  using function    WtchaGot_Unic_NotMuchIfYaChoppedItOff
''    The next text is copied from cell A 350
''      "Size -- PKEY_Size
''    //  Type:    UInt64 -- VT_UI8
''    //  FormatID: (FMTID_Storage) {B725F130-47EF-101A-A5F1-02608C9EEBAC}, 12 (PID_STG_SIZE)
''    //
''    //
''    DEFINE_PROPERTYKEY(PKEY_Size, 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC, 12);
''    #define INIT_PKEY_Size { { 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC }, 12 }
''
''    "
''    The next text is copied from watch window at  arrProphs()(349)
''        : arrProphs()(349) : "Size -- PKEY_Size
''    //  Type:    UInt64 -- VT_UI8
''    //  FormatID: (FMTID_Storage) {B725F130-47EF-101A-A5F1-02608C9EEBAC}, 12 (PID_STG_SIZE)
''    //
''    //
''    DEFINE_PROPERTYKEY(PKE"
' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(arrProphs()(349), "Size349")

Rem 3 Evaluate Range to get just the property to use in like    .ExtendedProperty("System.Size")
Dim arrExtProps() As Variant  '
 Let arrExtProps() = Me.Evaluate("IF({1},LEFT(A2:A1055,SEARCH("" -- PKEY"",A2:A1055)-1))")
' 3b) Paste into a worksheet column
 Let Me.Range("E2:E1055") = arrExtProps()
'Stop
End Sub

---

https://i.postimg.cc/90Znck96/Ext-Pr...n-Column-E.jpg
Attachment 5753 https://i.postimg.cc/sGfccwwm/Ext-Pr...n-Column-E.jpg

https://i.postimg.cc/90Znck96/Ext-Pr...n-Column-E.jpg

This is post
https://www.excelfox.com/forum/showt...ll=1#post24048
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=24048&viewfull=1#post24048
https://www.excelfox.com/forum/showt...ge12#post24048
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff/page12#post24048

---

saafsfaf

this is post
https://www.excelfox.com/forum/showt...ll=1#post24050
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24050&viewfull=1#post24050
https://www.excelfox.com/forum/showt...ge60#post24050
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page60#post24050

---

Quotes in VBA

In Excel spreadsheets, within a formula, we would enclose text we wanted to appear as text, in a quote pair, as this signalises to Excel that we want a literal text, rather than that the text is a name, function or some other specific thing that the text represents in Spreadsheet syntax. That is so that we can combine in a cell text and the result of a formula.
Example, type in a cell this ="Result is " & 1+1 then you will still see that in the formula bar, but in the cell you would see Result is 2
The & is just the VBA syntax for "sticking" things together, so this formula, for example, would give the same result in the cell ="Res" & "ult is " & 1+1
It follows, perhaps, that we can put plain text in a cell with a formula, as an alternative to simply putting the text in, for example , typing ="Hello" in a cell will result in that in the formula bar, but in the cell we will see Hello


Quotes in VBA
For VBA it is the same: In other words, what is within a quote, will not be regarded directly as part of the coding, but rather refers to some actual text that will be used in some way, depending on what the coding associated with it is about. For us here, the text within the quotes is the text that we finally want to see.
Working in VBA, ( or in a spreadsheet cell), to produce a string that we want to show a quote, becomes problematic. It is not clear if anybody really knows how and why we can get over this problem. There is no real documentation, although you might find the simple rule or explanation along the lines of…. Strings in VBA are delimited with quotation marks. If you want to include quotation marks in your string, you need to double them. Otherwise, the VBA compiler will not recognize that they should not end the string….
Well, that may be a correct statement, but I am not sure if that is a good explanation and whether it works by design or accident.
For me the following ideas helps to remember and possibly have an explanation or at least a theory that can explain the results and behaviour: It’s not too distant or different to that statement, it is an idea that helps me to remember, and possibly an
explanation:
The syntax will not allow a single quote within a quote pair, as the complier requires a pair. The syntax of VBA coding would also understandably find these strange.
__________ = "___ "____"___ "
__________ = "xyz"____"xyz"
But consider what might happen when we bring the inner quote pair together
__________ = "xyz""xyz"
I am thinking that we may have some sort of strange dynamic equilibrium situation, whereby simultaneously the quote pair syntax is satisfied and we have two joined pairs of text, ( "intimately joined"), which VBA sees on the compile as something like this
xy"xyz""
xyzxy""xyz"
, but because of this intimacy of the join, the position of the two inner quotes appears in the same place, meaning that the string is joined,- a quote may be defined twice, but in the same place, resulting in the final string as seen by VBA as
xyzxyz"xyz
Another way perhaps for thinking about is that they are actually a bit further than intimately joined, a character is entwined. By definition, the actual final literal string starts or stops on the other side of the quote, either after or before the quote. If you look at some of those coloured sketches you can perhaps imagine that the stop and start of the two literal bits of literal text is in the same place, which further joins them by two quotes merged into one.


Using Debug.Print to aid in string construction
Even with a good understanding of quotes in VBA, it is very easy to make a mistake. This general rule can help:
Printing out the string you have attempted to construct in the immediate window, will show it as VBA sees it after compile, and shows what VBA, after compile, will be trying to put in a cell via a code line like
Let Range("C1") = The string you have constructed
So, that string shown in the immediate window must be syntaxly kosher for an Excel cell, in otherwords it must look like the string as you would manually write it in a cell. So for putting a formula in a cell, something like this would be OK
= "a" & "b"
, resulting in the value
ab
showing in the cell, ( and in the formula bar you will see = "a" & "b" , just as in the immediate window )
On the other hand, something like these showing for a Debug.Print of your constructed string, would likely error when used
= "a" & b"
= "a" & ""b"
= ""a"" & "b"


So finally, in ' 2a we have a string construction that we are happy with to put a formula in a cell which will be the Excel spreadsheet formula, ="a"&Char(10)&Char(13)&"b" , as indeed we see both in the formula Bar, and in the immediate window if using
Debug.Print "=" & """a""" & "&" & "Char(13)&Char(10)" & "&" & """b""" & ""
To briefly explain that final string construction in words
______ "=" & """a""" & "&" & "Char(13)&Char(10)" & "&" & """b""" & ""
The outer most quotes are those always required in VBA to signalise that we are giving literal text.
Within those outer quotes, in VBA coding we may build up the string from further string elements. (The string can also be built up from VBA things returning strings or numbers, as we will do in the next post. Here we are only using other string elements), The syntax of VBA will require further enclosing quotes for every further string elements, and furthermore, VBA syntax requires an ampersand symbol, _ & _ , to join these strings, thus
______ "__" & "¬¬¬¬____" & "asskasasaf"
In green is the literal text, as we would write it in a cell.
Excel spreadsheet syntax requires in the final written cell formula text ampersands, & , which will be here literal text, hence must be enclosed with VBA quote pairs, thus
"&"
Finally, the Excel spreadsheet syntax requires that a literal text such as the character a, must be enclosed in the cell in quotes, but as we discussed in quotes in VBA something like this would error , ""a"" , we require to effectively double quote where we want a final single quote in the final string , """a"""


The final value in the cell would be
a
b
We have there 4 characters, pseudo like, (and it’s a bit subjective/ arbitrary of me, how I show them here in this pseudo form, just for explanation purposes: )
a vbCr
vbLf b

Some final minor notes:

_ If we are in in non English Excel, as I am, we can type either of these in the Immediate window to get the English syntax.
? range("C1").Formula
Debug.Print range("C1").Formula
_ Once we have the final string, we can often simplify, mainly by removing some VBA joining bits, since they are redundant when linking only literal text – the text can simply appear together.
______ "=""a""&Char(13)&Char(10)&""b"""
However, from experience I would only do this, if ever, at a final stage, since the careful explicit constructions are usually easier to build up and debug. Indeed, with hindsight, I might separate all the final cell texts of "a" and "b", as shown within the VBA construction, into the 3 characters of a quote, an a , and a final quote, just so as to make them clearly correspond to the explanations given of the quotes in VBA
______ "=" & """" & "a" & """" & "&" & "Char(13)&Char(10)" & "&" & """" & "b" & """" & ""
In words, just as you would write in the cell ,
=
and
a quote "
and
a
and
a quote "
and
a ampersand &
and
a quote "
and
the two joined text (functions), Char(13)&Char(10)
and
a ampersand &
and
a quote "
and
b
and
a quote "


https://i.postimg.cc/j2SJH7tY/a-CHAR-13-CHAR-10-b.jpg
Attachment 5795 https://i.postimg.cc/k6L49D2T/a-CHAR-13-CHAR-10-b.jpg
https://i.postimg.cc/j2SJH7tY/a-CHAR-13-CHAR-10-b.jpg

Later

Code:

---

Option Explicit
Sub CarriageReturnLineFeedExcelVBAText() '  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24054&viewfull=1#post24054
On Error GoTo Bed
Rem 0                    A few things to make things in the demo look closer to what they are
 Call TidyCellView  '    When experimenting it may be a good idea to occaisionally do this again, as Excel occaisionally may change things if it geusses incorrrectly what we want to see
Debug.Print
Rem 1  Simple Text to put in a cell
                                                            Debug.Print "Rem 1a": Let Range("B1") = "Rem 1" & vbLf & """" & "a" & """" & " " & "&" & " vbCr " & "&" & " vbLf " & "&" & " " & """" & "b" & """"  ' I get this from the Immdiszr eindow after I wrote it in  https://i.postimg.cc/3rSpmQ94/Use-Wtcha-Got-in-Immediate-window-to-get-tricky-cell-text-in-VBA-syntax-form.jpg
                                                                                  Let Range("B1").Characters(Start:=1, Length:=6).Font.Color = -11489280: Let Range("B1").Characters(Start:=7, Length:=23).Font.ColorIndex = xlAutomatic
      Debug.Print "a" & vbCr & vbLf & "b" '          shows          a
'                                                                      b
      Debug.Print "a" & Chr(13) & Chr(10) & "b" '    shows          a
'                                                                      b
 Let Range("B3") = "a" & vbCr & vbLf & "b" '          shows in cell  a
'                                                                      b
 Let Range("B4") = Evaluate("a" & vbCr & vbLf & "b") ' In cell  #WERT!  -  it appears to be returning  Error 2015
'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A1").Value) '        "a" & vbCr & vbLf & "b"
' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff("a" & vbCr & vbLf & "b") ' "a" & vbCr & vbLf & "b"



Stop: Call TidyCellView: Debug.Print
' LHS    https://www.excelfox.com/forum/showthread.php/2956-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly
 
' 1b
                                                                    Debug.Print "Rem 1b": Let Range("C1") = "Rem 1b" & vbLf & """" & "a" & """" & " " & "&" & " Evaluate" & "(" & """" & "Char" & "(" & "13" & ")" & "&" & "Char" & "(" & "10" & ")" & """" & ")" & " " & "&" & " " & """" & "b" & """"
                                                                    Let Range("C1").Characters(Start:=1, Length:=7).Font.Color = -11489280: Let Range("C1").Characters(Start:=8, Length:=41).Font.ColorIndex = xlAutomatic
      Debug.Print "a" & Evaluate("Char(13)&Char(10)") & "b" ' shows          a
'                                                                              b
 Let Range("C3") = "a" & Evaluate("Char(13)&Char(10)") & "b" ' shows in cell  a
'                                                                              b
'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B1").Value)      '          "a" & vbCr & vbLf & "b"
 Let Range("C4") = Evaluate("a" & Evaluate("Char(13)&Char(10)") & "b")  ' In cell  #WERT!  -  it appears to be returning  Error 2015
Stop: Call TidyCellView: Debug.Print

' 1c(i)                            Note    Evaluate(vbCr & vbLf)  in watch gives    gives    Watch :  : Evaluate(vbCr & vbLf) : Fehler 2015 : Variant/Error
                                                                    Debug.Print "Rem 1c(i)": Let Range("D1") = "Rem 1c" & _
                                                                    vbLf & """" & "a" & """" & " " & "&" & " Evaluate" & "(" & "vbCr " & "&" & " vbLf" & ")" & " " & "&" & " " & """" & "b" & """"
                                                                                            Let Range("D1").Characters(Start:=1, Length:=7).Font.Color = -11489280: Let Range("D1").Characters(Start:=8, Length:=33).Font.ColorIndex = xlAutomatic
      Debug.Print "a" & Evaluate(vbCr & vbLf) & "b" ' Fatal                        Type Mismatch Error
 Let Range("D3") = "a" & Evaluate(vbCr & vbLf) & "b" ' Fatal                        Type Mismatch Error
' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff("a" & Evaluate(vbCr & vbLf) & "b") 'Fatal Type Mismatch Error
 Let Range("D4") = Evaluate("a" & Evaluate(vbCr & vbLf) & "b") ' Fatal      Type Mismatch Error
Stop: Call TidyCellView: Debug.Print

' 1c(ii)                            Note    Evaluate("""" & vbCr & vbLf & """")  Watch :  : Evaluate("""" & vbCr & vbLf & """") : "
                            '                                                                                                      " : Variant/String      if copied here, but looks like    "  "    in the watch window      https://i.postimg.cc/L6C24trX/Warch-Window-dont-display-a-line-break.jpg
                                                                    Debug.Print "Rem 1c(ii)": Let Range("E1") = "Rem 1c(ii)" & _
                                                                    vbLf & """" & "a" & """" & " " & "&" & " Evaluate" & "(" & """" & """" & """" & """" & " " & "&" & " vbCr " & "&" & " vbLf " & "&" & " " & """" & """" & """" & """" & ")" & " " & "&" & " " & """" & "b" & """"
                                                                                              Let Range("E1").Characters(Start:=1, Length:=11).Font.Color = -11489280: Let Range("E1").Characters(Start:=12, Length:=47).Font.ColorIndex = xlAutomatic
                  Debug.Print Evaluate("""" & vbCr & vbLf & """") ' This seems to take the cursor down 2 places, but as  Debug.Print  takes routinely the cursor down once, our bit must have took it down once
      Debug.Print "a" & Evaluate("""" & vbCr & vbLf & """") & "b" ' shows          a
'                                                                                  b
 Let Range("E3") = "a" & Evaluate("""" & vbCr & vbLf & """") & "b" ' shows in cell  a
'                                                                                  b
 Let Range("E4") = Evaluate("a" & Evaluate("""" & vbCr & vbLf & """") & "b") ' In cell  #WERT!  -  it appears to be returning  Error 2015
                                                                                      Let Range("E4") = "#WERT!                                        #VALUE is Excel's way of saying, ""There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing."" The error is very general, and it can be hard to find the exact cause of it"
Debug.Print Len("""" & vbCr & vbLf & """")            '                            4
Debug.Print Len(Evaluate("""" & vbCr & vbLf & """"))  '                            2
'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B1").Value)      '                "a" & vbCr & vbLf & "b"
Stop: Call TidyCellView: Debug.Print
Debug.Print
Stop: Stop: Stop

' 1d Some conclusions
                                                                    Debug.Print "Rem 1d"
Debug.Print """" & vbCr & vbLf & """"                  '                          "
                                                      '                          "
'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff("""" & vbCr & vbLf & """")    '          4 characters  quote    https://i.postimg.cc/QMPndxSn/4-characters-quote-vb-Cr-vb-Lf-quote.jpg          https://postimg.cc/gwVKMpCv

Debug.Print
Debug.Print
Exit Sub
Bed:
Debug.Print Err.Number
Debug.Print Err.Description
Resume Next
End Sub
Sub CarriageReturnLineFeedExcelVBAFormula()

---

this is post #595
https://www.excelfox.com/forum/showt...ll=1#post24055
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24055&viewfull=1#post24055
https://www.excelfox.com/forum/showt...ge60#post24055
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page60#post24055

---

Problems with vbCr & vbLf in formulas and Evaluate(" ")

Review/ Introduction of what we are doing
In this post we will consider the first parts of the demo coding from the last post, or rather Sub CarriageReturnLineFeedExcelVBAText()
This will take us up to the point of the issue of vbCr & vbLf in formulas and Evaluate(" "), and so serve as an introduction and review of relevant concepts required to be understood before tacking the issue one step further into formulas

Rem 1 Puts stuff in a cell, fairly straight forward,
Range("B3") = "a" & vbCr & vbLf & "b"
( but even that is not so well understood. I understand it better than anyone at the moment. https://www.excelfox.com/forum/showt...-Value-Anomaly ). We can best say that the simplest syntax of the LHS, as we have there, says "put in the cell, what is on the RHS, like wot we might do via writing something in a spreadsheet cell and then hitting Enter". That tends to imply putting text, (and / or numbers) in a cell. But I think if we want to learn quicker and better Excel and VBA, then it is advisable not to think like that too much, and rather to leave it a bit open in the mind at "put in the cell, what is on the RHS, as example, as you might do via writing something in and then hitting Enter". Best not tie the thinking down too much on that one, or further revelations I give may not get so easily in the brain )
As far as the RHS is concerned, it is what we put in the cell : In Excel spreadsheets, we can simply write in text
In a formula, we would enclose text we wanted to appear as text, in a quote pair, as this signalises to Excel that we want a literal text, rather than that the text is a name, function or some other specific thing that the text represents in Spreadsheet syntax.
For VBA it is the same: In other words, what is within a quote, will not be regarded directly as part of the coding, but rather refers to some actual text that will be used in some way, depending on what the coding associated with it is about. For us here, the text within the quotes is the text that we finally want to see
But already, we perhaps need to think a bit more carefully about exactly what is happening: VBA made some determination of what "a" & vbCr & vbLf & "b" is. It decided it was a 4 character piece of text. In VBA we represent that something like a vbCr vbLf b, but inside the whatever calculated/ evaluated thing, (bucket, section of pipe or bit of text string, etc) is: The character a; the character feed character; the line feed character; and the character b
' 1b Range("C3") = "a" & Evaluate("Char(13)&Char(10)") & "b"
This gives an initial indication that
_____ = ____ Evaluate("Char(13)&Char(10)")
and
_____ = ______ vbCr & vbLf
may be equivalent. The Evaluate(" ") is presumably returning into VBA what VBA also sees in vbCr & vbLf

' 1c(i) Range("D3") = "a" & Evaluate(vbCr & vbLf) & "b"
This gives an initial indication that
_____ = ____ Evaluate("Char(13)&Char(10)")
and
_____ = ______ vbCr & vbLf
may not be equivalent

' 1c(ii) Range("E3") = "a" & Evaluate("""" & vbCr & vbLf & """") & "b"
This is our first dealing with the issue this Post and the next few Posts is about: We seem to have got over the problem of ' 1c(i)
The issue of this Post and the next few Posts is about that I am trying to explain that.

But first we will do some similar experiments, putting a formula in, ( but that also requires some careful consideration of Quotes in VBA , ( or an Excel cell, for that matter )

But before we go let’s have a Results Summary Conclusion attempt
https://i.postimg.cc/pddjNp40/Sub-Ca...el-VBAText.jpg
Attachment 5830 https://i.postimg.cc/87qcMP1W/Sub-Ca...el-VBAText.jpg
https://i.postimg.cc/pddjNp40/Sub-Ca...el-VBAText.jpg

Rem 1d(i) Some conclusions
Perhaps it’s not so unexpected that Evaluate(vbCr & vbLf) gives us problems. Lets say Evaluate() looks for a valid Excel syntax, or perhaps let say Evaluate() looks for a valid Excel name initially, failing that it does the other sort of evaluation based on adding a = at the start. We can consider vbCr and vbLf as variables containing text characters. As such they are not much different from a x and a y
Now Evaluate(" ") was designed to take a string to allow us to build up a final text within the " " __ I think that is put in the cell , and if that is not recognised, then an = is put in front of it and that is tried. Unless, in this example, xy is recognised, such as for a named range, only there after it puts an = at the start. In a cell =xy wont work. ="xy" will work. The " " is a VBA string. For that to give me like in the returned Debug.Print string a leading quote, for example, I need to add a """"

Rem 1d(ii) Some advanced stab in the dark conclusions (Actually with a bit of hindsight from later)
Maybe….
It is likely that if you have got as far as reading this, then you will likely be familiar with the Range(" ")
There are some similarities with Evaluate(" ") and this more familiar Range(" ") _ So that can be a good start point.
These both return something to VBA when the text inside the " " is some recognised Excel spreadsheet syntax convention thing. I suggest there is some sort of flexible/ dynamic = feature wired in which may be used after some attempt without it. Some extra check is then perhaps added to Range(" ") to ensure that a range object is returned. Assuming this idea is correct, then Evaluate(" ") is the same but without this restriction.
( For Range(" ") this can explain the interesting feature we tripped over here , since a formula is allowed, but only if it returns a range object reference, in other words a name recognised in Excel spreadsheet syntax convention. This last bit sets the limitation of Range(" ")
However, I think the Evaluate(" ") does not share this limitation, and so allows a value, text or numeric to be returned).
Perhaps the Evaluate(" ") can be regarded as doing this pseudo coding
_ If the text is some recognised name or excel reference , Then Return the object, Expose it, run it, or some similar action doing something, Exit
Put a = in front of it.
_ If with the = it is not recognised by Excel spreadsheet syntax convention Then Error
_ If it is a closed workbook reference, then error, since it allows Alan to do things better than most complicated data base stuff, and that’s embarrassing , Error Exit
Return anything that a Variant variable will return.

Evaluate Formula Value)
We are not quite finished.

Evaluate Formula Value(s)
For the case of Evaluate(" ") effectively doing a formula in the " " we end up effective doing an Excel type evaluation, but we are not tied to a worksheet, so we are not necessarily restricted to a single values. But we do end up with value(s) in the range in the LHS. We do the same calculation as if we ended up with a formula in the cell. But it’s a false statement, often given when explaining what Evaluate(" ") is doing, to say it fundamentally does a mathematical calculation, gets a value(s) or fundamentally "evaluates". It can "do formulas"

Ref
https://eileenslounge.com/viewtopic....280997#p280997
https://www.myonlinetraininghub.com/...#comment-84892

Quotes in VBA

Rem 2 Puts formula in cell(Quotes in VBA )
' 2a
At this point we see for the first time the confusing world of quotes in VBA, and Excel in general.
In Excel spreadsheets, within a formula, we would enclose text we wanted to appear as text, in a quote pair, as this signalises to Excel that we want a literal text, rather than that the text is a name, function or some other specific thing that the text represents in Spreadsheet syntax.

Quotes in VBA
For VBA it is the same: In other words, what is within a quote, will not be regarded directly as part of the coding, but rather refers to some actual text that will be used in some way, depending on what the coding associated with it is about. For us here, the text within the quotes is the text that we finally want to see.
Working in VBA, ( or in a spreadsheet cell), to produce a string that we want to show a quote, becomes problematic. It is not clear if anybody really knows how and why we can get over this problem. There is no real documentation, although you might find the simple rile or explanation along the lines of…. Strings in VBA are delimited with quotation marks. If you want to include quotation marks in your string, you need to double them. Otherwise, the VBA compiler will not recognize that they should not end the string….
Well, that may be a correct statement, but I am not sure if that is a good explanation and whether it works by design or accident.
For me the following ideas helps to remember and possibly have an explanation or at least a theory that can explain the results and behaviour: It’s not too distant or different to that statement, it is an idea that helps me to remember, and possibly an
explanation:
The syntax will not allow a single quote within a quote pair, as the complier requires a pair. The syntax of VBA coding would also understandably find these strange.
__________ = "___ "____"___ "
__________ = "xyz"____"xyz"
But consider what might happen when we bring the inner quote pair together
__________ = "xyz""xyz"
I am thinking that we may have some sort of strange dynamic equilibrium situation, whereby simultaneously the quote pair syntax is satisfied and we have two joined pairs of text, ( "intimately joined"), which VBA sees on the compile as something like this
xy"xyz""
xyzxy""xyz"
, but because of this intimacy of the join, the position of the two inner quotes appears in the same place, meaning that the string is joined,- a quote may be defined twice, but in the same place, resulting in the final string as seen by VBA as
xyzxyz"xyz
Another way perhaps for thinking about is that they are actually a bit further than intimately joined, a character is entwined. By definition, the actual final literal string starts or stops on the other side of the quote, either after or before the quote. If you look at some of those coloured sketches you can perhaps imagine that the stop and start of the two literal bits of literal text is in the same place, which further joins them by two quotes merged into one.


Using Debug.Print to aid in string construction
Even with a good understanding of quotes in VBA, it is very easy to make a mistake. This general rule can help:
Printing out the string you have attempted to construct in the immediate window, will show it as VBA sees it after compile, and shows what VBA, after compile, will be trying to put in a cell via a code line like
Let Range("C1") = The string you have constructed
So, that string shown in the immediate window must be syntaxly kosher for an Excel cell, in otherwords it must look like the string as you would manually write it in a cell. So for putting a formula in a cell, something like this would be OK
= "a" & "b"
, resulting in the value
ab
showing in the cell, ( and in the formula bar you will see = "a" & "b" , just as in the immediate window )
On the other hand, something like these showing for a Debug.Print of your constructed string, would likely error when used
= "a" & b"
= "a" & ""b"
= ""a"" & "b"


So finally, in ' 2a we have a string construction that we are happy with to put a formula in a cell which will be the Excel spreadsheet formula, ="a"&Char(10)&Char(13)&"b" , as indeed we see both in the formula Bar, and in the immediate window if using
Debug.Print "=" & """a""" & "&" & "Char(13)&Char(10)" & "&" & """b""" & ""
To briefly explain that final string construction in words
______ "=" & """a""" & "&" & "Char(13)&Char(10)" & "&" & """b""" & ""
The outer most quotes are those always required in VBA to signalise that we are giving literal text.
Within those outer quotes, in VBA coding we may build up the string from further string elements. (The string can also be built up from VBA things returning strings or numbers, as we will do in the next post. Here we are only using other string elements), The syntax of VBA will require further enclosing quotes for every further string elements, and furthermore, VBA syntax requires an ampersand symbol, _ & _ , to join these strings, thus
______ "__" & "¬¬¬¬____" & "asskasasaf"
In green is the literal text, as we would write it in a cell.
Excel spreadsheet syntax requires in the final written cell formula text ampersands, & , which will be here literal text, hence must be enclosed with VBA quote pairs, thus
"&"
Finally, the Excel spreadsheet syntax requires that a literal text such as the character a, must be enclosed in the cell in quotes, but as we discussed in quotes in VBA something like this would error , ""a"" , we require to effectively double quote where we want a final single quote in the final string , """a"""


The final value in the cell would be
a
b
We have there 4 characters, pseudo like, (and it’s a bit subjective/ arbitrary of me, how I show them here in this pseudo form, just for explanation purposes: )
a vbCr
vbLf b

Some final minor notes:

_ If we are in in non English Excel, as I am, we can type either of these in the Immediate window to get the English syntax.
? range("C1").Formula
Debug.Print range("C1").Formula
_ Once we have the final string, we can often simplify, mainly by removing some VBA joining bits, since they are redundant when linking only literal text – the text can simply appear together.
______ "=""a""&Char(13)&Char(10)&""b"""
However, from experience I would only do this, if ever, at a final stage, since the careful explicit constructions are usually easier to build up and debug. Indeed, with hindsight, I might separate all the final cell texts of "a" and "b", as shown within the VBA construction, into the 3 characters of a quote, an a , and a final quote, just so as to make them clearly correspond to the explanations given of the quotes in VBA
______ "=" & """" & "a" & """" & "&" & "Char(13)&Char(10)" & "&" & """" & "b" & """" & ""
In words, just as you would write in the cell ,
=
and
a quote "
and
a
and
a quote "
and
a ampersand &
and
a quote "
and
the two joined text (functions), Char(13)&Char(10)
and
a ampersand &
and
a quote "
and
b
and
a quote "


https://i.postimg.cc/j2SJH7tY/a-CHAR-13-CHAR-10-b.jpg
Attachment 5795 https://i.postimg.cc/k6L49D2T/a-CHAR-13-CHAR-10-b.jpg
https://i.postimg.cc/j2SJH7tY/a-CHAR-13-CHAR-10-b.jpg


So we have done the background revision and can move on in the next post to the problem / issues with vbCr & vbLf in formulas and Evaluate(" "), well almost

this is post #597
https://www.excelfox.com/forum/showt...ll=1#post24087
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24087&viewfull=1#post24087
https://www.excelfox.com/forum/showt...ge60#post24087
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page60#post24087

---

We are not quite finished
A Final Evaluate full. Text. But it does a formula,
You should be confused at this point. If you aren’t, then you are not normal. We are putting text in a cell. The final step is to complete it with Evaluate, for what reason I have long since forgotten myself.
A recap is perhaps useful:
We figured out a tricky syntax, and in doing so came close finally to conclusions leading to an understanding of Evaluate (" ") that probably no one has…... Office is an old program, in software age, and has had an incredible number of fixes. I doubt anyone at Microsoft has any idea of the entire suite's programming. I doubt anyone at Microsoft has a handle on the coding….
We stated this by looking at putting text in cells. We were also messing with Evaluate (" ")
We spoke about a flexible = that allows a formula to be done, and in a cell, if the result is to be out in a cell, then just the value will be put in.
Our final code line so far considered was this .._

Code:

---

Range("E4") = Evaluate("a" & Evaluate("""" & vbCr & vbLf & """") & "b") ' In Cell we see      #WERT!  -  IT APPEARS TO BE RETURNING  ERROR 2015

---

_.. and it errored , (as did all our Evaluate attempts)
We had concentrated on the Evaluate("""" & vbCr & vbLf & """") stuff. But I think we can see clearly from all our discussions and in particular to the initial stab at some conclusions, that we need to have in the Put in the cell, Debug.Print seen string a quote pair around the a and the b
Let's say that again a bit differently: Currently we have what we could call the Evaluate(" or VBA code line required quotes around it. This would be like Excel trying to recognise a Name or meaning to
a
, and failing that, Evaluate(" ") tries to see if doing
=a
, gives soimethi8ng sensible. Unless you specifically have given that character, a , some meaning in Excel , that will error.
The oint that needs to be remembered over and over again is that in this situation, we can consider Evaluate(" ") to be trying to get what we finally want, via a formula in a cell, but we won’t fonally get a formula if its successful: We will get the actual final result. In other words, in this example a cell would want to see
="a"
So… what to do
And this is the crux actually, of what started this page in order to get vbCr & vbLf to do what we wanted: In that final Evaluate string we need to do something to make the a and the b be seen to be enclosed in quotes. That requires a double quote per quote you finally want to see.
Let’s do that in a separate coding, Sub CarriageReturnLineFeedExcelVBAEvaluateTextFormula( )

Code:

---

Sub CarriageReturnLineFeedExcelVBAEvaluateTextFormula()  '  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24087&viewfull=1#post24087
On Error GoTo Bed
'                        A few things to make things in the demo look closer to what they are
 Call TidyCellView  '    When experimenting it may be a good idea to occaisionally do this again, as Excel occaisionally may change things if it geusses incorrrectly what we want to see
Debug.Print
' 1f  Finally
                                                                    Debug.Print "Rem 1f": Let Range("F1") = "Rem 1f" & vbLf & _
                                                                    "Evaluate(" & """""""" & "a" & """""""" & ")" & " & " & " Evaluate" & "(" & """" & """" & """" & """" & " " & "&" & " vbCr " & "&" & " vbLf " & "&" & " " & """" & """" & """" & """" & ")" & " " & " & " & "Evaluate(" & """""""" & "b" & """""""" & ")"
                                                                                              Let Range("F1").Characters(Start:=1, Length:=6).Font.Color = -11489280: Let Range("F1").Characters(Start:=7, Length:=78).Font.ColorIndex = xlAutomatic
      Debug.Print Evaluate("""" & "a" & """") & Evaluate("""" & vbCr & vbLf & """") & Evaluate("""" & "b" & """")
 Let Range("F4") = Evaluate("""" & "a" & """") & Evaluate("""" & vbCr & vbLf & """") & Evaluate("""" & "b" & """")
Debug.Print
Debug.Print
Exit Sub
Bed:
Debug.Print Err.Number
Debug.Print Err.Description
Resume Next
End Sub

---

What have we done.
Let’s put into words what we have done, as it will help in the next post sections, when we do putting formulas into cells with Evaluate("")
We created a full string for VBA equivalent to
"a" & vbCr & vbLf & "b"

"a" was got from this Evaluate("""" & "a" & """")

vbCr & vbLf was got from this Evaluate("""" & vbCr & vbLf & """")

"b" was got from this Evaluate("""" & "b" & """")

But, and this is important,
The three Evaluates effectively did these three things:

It got the result as if we typed in a cell ="a" which resulted in a

It got the result as if we typed in a cell ="the character for a carriage return" & "the character for a carriage return" which resulted in a visible line break, or second line, or however you personally describe that

It got the result as if we typed in a cell ="b" which resulted in b

Evaluate got us the 3 results from a formula, as if I typed the formula in a cell in a cell



https://i.postimg.cc/vmSF6JBH/Column-F-results.jpg
Attachment 5827 https://i.postimg.cc/hJx3k3TH/Column-F-results.jpg
https://i.postimg.cc/vmSF6JBH/Column-F-results.jpg

this is post #598
https://www.excelfox.com/forum/showt...ll=1#post24089
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24089&viewfull=1#post24089
https://www.excelfox.com/forum/showt...ge60#post24089
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page60#post24089

---

Rem 2 Put formula in cell (Sub CarriageReturnLineFeedExcelVBAFormula() )
Having forced myself into going back to the basics, and getting a more deeper understanding of Evaluate(" ") , the problems in some syntax in putting formulas into a cell , or finding text things via an excel formula in Evaluate(" ") , which caused the excursion of this page , is easy to do, and sure to get it, as long as we are patient and cautious.
So armed with some knowledge of constructing the necessary strings, and some initial attempt at a a Results Summary Conclusion attempt we move on to get the same value in a cell as previously, but from a formula, that is to say, taking Evaluate(" ") string constructs that one step further to actually put a formula in a cell …. pseudo
Put in the cell = a formula looking like , ="Hello"
, using
Put in the cell = Evaluate( string construct to actually give a formula, not the result from it )
Note , of course, you will still see the value result in the cell, but an indication that we actually put on a formula, rather than a value, should be seen in the formula bar, just as if we manually typed the formula in, ( assuming default Excel settings )
https://i.postimg.cc/9Mf54ycw/Formul...splay-text.jpg
Attachment 5829 https://i.postimg.cc/McgrNMMz/Formul...splay-text.jpg
https://i.postimg.cc/9Mf54ycw/Formul...splay-text.jpg

An extra quote, or two, and stuff
Having battled out enough with the basics in the last few posts, it’s actually not so difficult from now on
Points to note
_1) literal text in a cell, needs to be put in initially, "in the first place" as it were, in quotes in the string construct for evaluate just to prevent Evaluate(" ") trying to interpret the text as some particular Excel thing. So far we did this explicitly like this
Evaluate("""" & "Mytext" & """")
, which you would have to do if your text was in a variable , - example if your text was in a string variable, strText, then you would need to do this
Evaluate("""" & strText & """")
, or we could consder the new line pair things we have been considering as variables, and as such also need to do like
Evaluate("""" & vbCr & vbLf & """")
For actual text, this would be the same
Evaluate("""Mytext""")
, but I would recommend always being explicit until maybe you have finished.

Based on the results and attempted explanations of Evaluate(" ") so far generally, and with a bit of hindsight again, I might have a go at a slightly modified explanation of Evaluate(" ")

If you or I type an = at the start of putting something in a cell, then that is a significant thing that calls into action deep Office innards coding which I expect is either the same, or in some way strongly related to, or very similar to, the coding that gets set off by Evaluate(" "). There will be similarities therefore in the result, where I am using the word result in its broadest sense here: Just one example of this similarity is that it could be that a text or number appearing in the cell would be that which would appear in a variable, Ex if you did something like this
Ex = Evaluate("What you put in a cell after typing in a = at the start")
But that is just part of the story, perhaps what is often given as the simplifies version of what Evaluate(" ") is about.
More fully, the string what VBA "sees" will need to be a syntax, or named thing, that Excel recognises, and its always very good practice when constructing the string in Evaluate, ( strEval, where strEval is like here, Evaluate(strEval) ) , to check what VBA sees via Debug.Print strEval

I expect there may be a priority to the order that things are done.
Calling something out
First, a reference to an object or something will have a varied effect. Maybe we could refer this to calling it out, like shouting at someone to call them to attention. It may make them do something or get them there, ready. We sometimes hear of "exposing an interface" having a similar effect to running it. I like the sound of calling it out
Do a formula to do something (even a formula!)
This is easy to get mislead and confused. If a string construct, what VBA finally "sees", can be recognised as a syntax that Excel would be happy with in a cell, A formula, then the result that would be obtained of that string with an = in front of it, in a cell, is what Evaluate(" ") will try to return. At a stretch of the imagination, this could be regarded as calling out an imaginary cell, by its name, its cell reference, where the imaginary cell has that formula in it. This should perhaps spark off someone smarter to get excited and tell me about kicking off an Excel4 macro somehow. Just now it’s a leap too far for my brain. But I think the idea is potentially sound
Having got to this level of understanding, maybe we can do close to anything , within reason, even … put a formula in a cell

So let’s have a go. Introduction to formula in a cell
Backwards working understand, to easier perhaps is this. (This is perhaps easier to understand, working backwards.)
For example , I want this formula to effectively be put in a cell ,
="Hello"
, which will mean that I see Hello in the cell, and in the formula bar I will see ="Hello"
If I was using VBA to put that formula in a cell, I would need to construct the VBA string context for that, which would be like
Range("A10") = "=""Hello"""

To achieve that from a code line like ,
Range("A10") = Evaluate("")
, then Evaluate("") will need to return "=""Hello"""

So far we have learnt that for returning "Hello" in VBA , the RHS == Evaluate("") , would need to look like this
Evaluate("""" & "Hello" & """")

In simplified terms, we can say that in that construction , the 4 quotes, """" is getting us a single quote in that "Hello" in VBA

So a simplified logic perhaps tells us we need a few more of the 4 quotes, """" bits, ( as well as an = somewhere )

Just looking carefully , and applying the simple logic tells us we want : a quote; a =; 2 quotes; the text Hello ; and finally 3 quotes.
So perhaps we want this VBA context string
Evaluate(VBA context string)
"""" & "=" & """" & """" & "Hello" & """" & """" & """"

This next simple coding shows the construction clearly breaking down each required string bit

Code:

---

Sub CallOutTextInCellFormula() '  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24089&viewfull=1#post24089
' Effectively put  ="Hello"  in  a cell ,  which will mean that I see in the cell  Hello    and in the formula bar  ="Hello"
 Let Range("A10") = "=""Hello"""
'  we need from Evaluate(" ") a quote, a =, 2 quotes, Hello , and finally 3 quotes
'                              "    =      "      "    Hello    "      "      "
 Let Range("A10") = Evaluate("""" & "=" & """" & """" & "Hello" & """" & """" & """")

End Sub

---

Having got this far, at the final stage, we can simplify quite easily, if we wish to. The thing here to remember is that the & is only required to
_ link or glue variables or other VBA things to each other
or _ to link or glue variables or other VBA things to text
It is not required to link or glue text to text.
This means that bits like " & " can be removed
Do that with our last example, and you end up with this rather impressive looking beautiful thing
"""=""""Hello"""""""
Just to confuse or impress a little further, we have noted previously the somewhat "flexible" = that Evaluate(" ") seems to posses. There is no exception here, so we could add that last bit of apparent mystical magicness to that last string construction
"=""=""""Hello"""""""

Code:

---

Sub CallOutTextInCellFormula() '  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24089&viewfull=1#post24089
' Effectively put  ="Hello"  in  a cell ,  which will mean that I see in the cell  Hello    and in the formula bar  ="Hello"
 Let Range("A10") = "=""Hello"""
'  we need from Evaluate(" ") a quote, a =, 2 quotes, Hello , and finally 3 quotes
'                              "    =      "      "    Hello    "      "      "
 Let Range("A10") = Evaluate("""" & "=" & """" & """" & "Hello" & """" & """" & """")
'

' simplified
 Let Range("A10") = Evaluate("""=""""Hello""""""")
 Let Range("A10") = Evaluate("=""=""""Hello""""""")
End Sub

---

_._________________


&
A small reminder about the & in string context both In VBA and Excel spreadsheet syntax.
As we seen already in a coupe of places ( https://www.excelfox.com/forum/showt...ll=1#post24083
https://www.excelfox.com/forum/showt...ll=1#post24050 ) we have this same "gluing together" thing in both Excel and VBA.
If the formula we are trying finally to put in a cell has in Excel a & , then that & will effectively be another bit of text to add.
Note also in the following example to get like a formula ="Hello"&" You" we cannot rely on Evaluate(" ") to somehow return us VBA connecting / gluing & bits , so we must do some simplification in such example cases.

Code:

---

Sub CallOutTextInCellFormula() '  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24089&viewfull=1#post24089
' Effectively put  ="Hello"  in  a cell ,  which will mean that I see in the cell  Hello    and in the formula bar  ="Hello"
 Let Range("A10") = "=""Hello"""
'  we need from Evaluate(" ") :  a quote; a =; 2 quotes; Hello ; and finally 3 quotes
'                              "    =      "      "    Hello    "      "      "
 Let Range("A10") = Evaluate("""" & "=" & """" & """" & "Hello" & """" & """" & """")
'

' simplified
 Let Range("A10") = Evaluate("""=""""Hello""""""")
 Let Range("A10") = Evaluate("=""=""""Hello""""""")

' Put  ="Hello"& "You"  in  a cell
 Let Range("A11") = "=""Hello""" & "&" & """ You"""
' Evaluate will not be ablk to return the two connecting/ gluing VBA things  " & "  so we will need to be at the simplified level
 Let Range("A11") = "=""Hello""&"" You"""

'  we need from Evaluate(" ") :  a quote; a =; 2 quotes; Hello ; 2 quotes ; the & ; 2 quotes ;  You ; and finally 3 quotes
 Let Range("A11") = Evaluate("""" & "=" & """" & """" & "Hello" & """" & """" & "&" & """" & """" & " You" & """" & """" & """")

' simplified
 Let Range("A11") = Evaluate("""=""""Hello""""&"""" You""""""")
End Sub

---

this is post #599
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=24132&viewfull=1#post24132
https://www.excelfox.com/forum/showt...ge60#post24132
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page60#post24132

---

Put Formulas in cells, with Evaluate(" ") , or use them in Evaluate Range type solutions if they are involved with new line Characters

The original issues that sparked off this page, can be dealt with, or rather can be almost forgotten and / or don’t occur, as we now have a much better in depth understanding of the required string constructions, so can go directly to them, armed with our new knowledge of what fundamentally the Evaluate(" ") is /does

' 2a CHAR(13)&CHAR(10)
CHAR(13)&CHAR(10) is recognised Excel syntax. The Characters of a and b are not
VBA wants to see the following string in order to put the formula = "a" & CHAR(13) & CHAR(10) & "b" into the cell
"=""a""&CHAR(13)&CHAR(10)&""b"""
In other words, if I wanted to use VBA to put this formula in cell B10,
= "a" & CHAR(13) & CHAR(10) & "b"
, ( as an alternative to me physically typing this in ), then I would need a code line like
Range("B10") = "=""a""&CHAR(13)&CHAR(10)&""b"""
So producing all those bits on the RHS using Evaluate(" ") on the RHS requires carefully producing each of those 18 things from what we have learned is how they need to look like within the Evaluate(" ") string:-

Code:

---

"""" & "=" & """" & """" & "a" & """" & """" & "&" & "CHAR (13)&CHAR(10)" & "&" & """" & """" & "b" & """" & """" & """"

---

With the previous knowledge, it’s fairly straight forward.

Here saying it again. In words what we have done is: We consider the string that VBA wants to have , in its simplest form. ( It must be in the simplest form without any of the VBA " & " bits, since Evaluate(" ") in this use if it is only going to return to us a simple string, not a code line. By the same reasoning the Evaluate(" ") won’t give any other VBA things like variables and functions, so the " & " is not needed anyway. – ( It would be needed to link string text to other things like VBA variables and functions and formulas, in which case it would have this sort of form , missing one of the quotes, __ "words " & VBA stuff & " other words" )
We then make the string for evaluate, strEval . We do this by consigöring each Character in turn of this string we need in VBA, "=""a""&CHAR(13)&CHAR(10)&""b""" – Let’s open that out just to make it easier to see all those 18 bits

Code:

---

    "      =      "      "      a      "      "    &    CHAR(13)&CHAR(10)  &      "      "      b      "      "      "                                              ' For VBA

---

For each of the 18 things there, we know the equivalent to have inside Evaluate(" ")
Each of those is then joined by a " & "
Here is that working result again

Code:

---

  """" & "=" & """" & """" & "a" & """" & """" & "&" & "CHAR(13)&CHAR(10)" & "&" & """" & """" & "b" & """" & """" & """"

---

What finally is returned from the RHS and seen by VBA and then put in the cell is, as we would physically write it
="a"&CHAR(13)&CHAR(10)&"b"

A final simplification can be made to the evaluate string by removing all the " & " bits
_"""=""""a""""&CHAR(13)&CHAR(10)&""""b"""""""
or **
"=""=""""a""""&CHAR(13)&CHAR(10)&""""b"""""""

One last things that will be useful to compare with the over next section. ' 2b(ii) , will be
the Debug.Print of the string to be used in Evaluate(" ") strEval , _____ and the formula finally put in the cell and seen in the formula bar
__ "=""a""&CHAR(13)&CHAR(10)&""b""" ________ ="a"&CHAR(13)&CHAR(10)&"b"


' 2b vbCr & vbLf
' 2b(i)
We might want to go back a bit to some of the conclusion attempts here https://www.excelfox.com/forum/showt...ll=1#post24055
We said along the lines of that vbCr & vbLf was actually a text of two Characters, not much different to like xy. Even if we say that we know that vbCr & vbLf is a VBA text construct thing, used when building up a string in VBA, then that still does not help us to get anything useful out of Evaluate(vbCr & vbLf), (and this does nothing at all for us either Evaluate("" & vbCr & vbLf & "") )
This is a consequence of our better understanding of Evaluate(" ") in that it calls out an exposition of the underlying functionality, of the fundamental Excel object to which is named. This strange looking constant, vbCr & vbLf makes no sense to Excel.
Doing this Evaluate("""" & "a" & """") gives us the same results as putting ="""" & "a" & """" in a cell and the result from that world be ,
"a"
, ( regarding the extra = see ** ) That "a" is the required string to present in VBA on the RHS to put an a in a cell
To this consideration of the character a is a parallel to the Characters vbCr & vbLf - Doing this Evaluate("""" & vbCr & vbLf & """") gives us the same results as pseudo putting ="""" & "NewLineCharacters" & """" in a cell, although that pseudo syntax, NewLineCharacters , is not recognised, but the underlying shared innards working does the same, and the result from that world be, … well a bit difficult to show, but something pseudo like ,
"
_"
That is effectively the required string to present in VBA on the RHS to put those two Characters in a cell.

' 2b(ii) vbCr & vbLf
What perhaps the last section, ' 2b(i) , has told us, or as interpreted to be saying , is that the two VBA things, vbCr & vbLf return in a string construct in VBA "NewLineCharacters" , so that will be fine to apply to , (put in ) , a cell to on the RHS of a code line like pseudo ,
Range("xxx") = "NewLineCharacters"
, so the same as
Range("xxx") = vbCr & vbLf
, which will effectively put in two "invisible" characters, (although if you fiddle with the cell or formula bar format and size, select the cell or formula bar and move around with the arrow keys, then you may see the cursor moving giving the indication of something there )
So here we go, very similar to ' 2a except we consider that rather than having available a recognised Excel syntax/Name CHAR(13)&CHAR(10) we have something which we can consider pseudo like an xy or a or b literal character(s) , and that is pseudo like NewLineCharacters
The Characters of a and our pseudo NewLineCharacters and b are not recognised Excel syntax/names
VBA wants to see the following string in order to put the formula = "a" & "NewLineCharacters" & "b" into the cell
"=""a""&""NewLineCharacters""&""b"""
So producing all those bits from Evaluate(" ") requires carefully producing each of those Characters

Code:

---

"""" & "=" & """" & """" & "a" & """" & """" & "&" & """" & """" & "NewLineCharacters" & """" & """" & "&" & """" & """" & "b" & """" & """" & """"

---

Code:

---

  """" & "=" & """" & """" & "a" & """" & """" & "&" & """" & """" &  vbCr  &  vbLf  & """" & """" & "&" & """" & """" & "b" & """" & """" & """"

---

What finally is returned from the RHS and seen by VBA and then put in the cell is, as we would physically write it, ( if we could, - remember we have a pseudo characters, NewLineCharacters
="a"&"NewLineCharacters"&"b"

A final simplification can be made to the evaluate string by removing all the " & " bits
"""=""""a""""&""""" & vbCr & vbLf & """""&""""b"""""""
or
"=""=""""a""""&""""" & vbCr & vbLf & """""&""""b"""""""

One last thing interesting to compare with the first formula section. ' 2a , will be
the Debug.Print of the string to be used in Evaluate(" ") strEval

, and the formula finally put in the cell and seen in the formula bar

"=""a""&""
""&""b"""

="a"&"
"&"b"
A subtle difference perhaps, is that we are not passing a Excel known name / syntax that will do the new line, but rather perhaps the actual characters. Not sure of the significance if that, if any, but there may be some hidden revaluations in it at some later date….

Edit. Actually I have found a few remarkable things come out when including some of the "invisible" characters, as well as other VBA things, in the string construction. But based on my previous experience of revealing some of my lesser controversial discoveries I have serious decided to not openly discus them until/ if I become very rich and famous so that I can afford a small army of the best body guards. I am happy to say that here as I am sure most people won’t believe a word of it yet, so I am fairly safe.




(** Toleranceual redundancy , akin to adding the obstruction in a path of an open door, that door being the one closed on a cell and opened by the inclusion of an = at the first textual position thereof . )

this is post #600
https://www.excelfox.com/forum/showt...ll=1#post24133
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24133&viewfull=1#post24133
https://www.excelfox.com/forum/showt...ge60#post24133
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page60#post24133

---

Evaluate(" ")

This is often seen to be allowing Excel spreadsheet things to be done in VBA, and therefore a simplified explanation and some Blogs on Evaluate(" ") , the Evaluate method, will concentrate on this feature of it. That is not necessarily misleading, and is not a bad simplified start point.
After a lot of head breaking , I think I have a statement(s)/ ideas from which I am happy to expand and add flesh to in order to explain away both the accepted conventional things done with it, and to some extent the more advanced interesting and not always accepted unconventional things I have done, and also some more recent little discoveries, (which because of previous reactions I am never going to make public until when/if I become very rich and famous, so that I can afford a small army of the best body guards)

Here we go , it's like this:
This would be my entry in a simple dictionary, or the very first bit in an explanation:
___Evaluate _ (Callout an Excel object by referring thereto)
Call out is in the sense of bringing it to attention via some sort of reference string or name , and this is sometimes referred to as exposing an interface. A Run or a Call of something are a similar thing. We often experience that using a recognised text will make something happen . An Excel in built function and a User Defined Function, (UDF), can reasonably be considered to be similar things, even an Excel object, if Object is used in its very broadest sense, as is reasonably acceptable and certainly not conflicting with the somewhat bodged together form of apparent object orientated programming that VBA is. Don’t be confused that I went over to VBA there - what I am suggesting there is that the wrapper for the function coding has the name for referral to be Called of the routine name, which is recognised by Excel – in other words the name of a UDF is recognised by Excel, - as most of us will know, that is true of a Public Function in a normal code module, which is what a UDF is.