Tests and Notes on Range objects in Excel Cell

Some extra notes and experiments related this forum Page
https://www.excelfox.com/forum/showt...s-etc-)/page58
https://www.excelfox.com/forum/showt...cel-Cell/page8
https://www.excelfox.com/forum/showt...ll=1#post21923
https://www.excelfox.com/forum/showt...ge58#post21923

---

Some quirks with Excel Worksheet things ISTEXT and ISNUMBER https://support.microsoft.com/en-au/...1-f2d869135665

Only by chance I noticed a while later that I was lucky that my solution worked here https://eileenslounge.com/viewtopic....309137#p309137
My answer in that Thread was an attempt to get rid of that Number stored as Text warning thing, https://i.postimg.cc/X7hZDNDg/Number-stored-as-text.jpg
https://i.postimg.cc/5NjY3Qp0/Number-stored-as-text.jpg https://i.postimg.cc/ppfXmvv9/Number-stored-as-text.jpghttps://i.postimg.cc/3d5xrRs5/Number-stored-as-text.jpg

In the second worksheet in the uploaded file we have the main test data range from Chris, but to simplify this post I will just use a small section, A6:B7 , and do some simple Excel formulas initially involving ISTEXT and ISNUMBER
Initially results are as expected, or at least in a variation of one of the possibilities that we might have expected: The problem cell, A6, is seen as TEXT or not as a NUMBER
https://i.postimg.cc/6psVSh8q/ISTEXT...-initially.jpg
Attachment 5202https://i.postimg.cc/0bfJKDd1/ISTEXT...-initially.jpg
https://i.postimg.cc/6psVSh8q/ISTEXT...-initially.jpg

The next set of results however show a slight anomaly. It would appear that in the larger formula pairs, the one of the pair which would effectively be doing just =A6 makes some change, bit only half way: The warning thing is gone, but the alignment has not changed ( The formula in the pair contain the *2 , I just did to check what of the two options after the IF was being selected. The results there confirm that the involving ISTEXT and ISNUMBER is behaving inside the n as it did on its own )
https://i.postimg.cc/7YJMmRYj/ISTEXT...iour-in-IF.jpg
Attachment 5203 https://i.postimg.cc/zV5gf0hk/ISTEXT...iour-in-IF.jpg
https://i.postimg.cc/7YJMmRYj/ISTEXT...iour-in-IF.jpg


In fact a simple cell reference exhibits a similar behaviour https://i.postimg.cc/3Nf7LBYx/A6.jpg

So a simple reference seems to be doing something strange, maybe getting sort of half way there.

Before going any further, we should perhaps try the same experiments in Evaluate(" ") , since things do not always work exactly the same in worksheet cells and in Evaluate(" ") : There are sometimes minor differences.

How does the function formulas behave in Evaluate(" ")
The spreadsheet looks similar, (the small difference is explained by VBA working in English, recognising the text I used as a Boolean at some point, possibly, perhaps at point of insertion, which my German Excel then displays in the appropriate German syntax word)
https://i.postimg.cc/26JkxYYK/Evalua...preadsheet.jpg
https://i.postimg.cc/Q9QZXRHQ/Evalua...preadsheet.jpg
https://i.postimg.cc/26JkxYYK/Evalua...preadsheet.jpg
_... continued in next post


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=ySENWFIkL7c
https://www.youtube.com/watch?v=ySENWFIkL7c&lc=UgyqIYcMnsUQxO5CVyx4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg. 9k3ShckGnhv9k89LsaigoO
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg. 9k-vfnj3ivI9k8B2r_uRa2
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg
https://www.youtube.com/watch?v=vXyMScSbhk4
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgynOSp1dleo-Z8L_QN4AaABAg.9jJLDC1Z6L-9k68CuL4aTY
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwV5N_ulFXYMNbyQG54AaABAg. 9itCkoVN4w79itOVYVvEwQ
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

_... from last post


The Watch Window from coding is also not showing any surprises from Evaluate(" ") yet
I mean the Evaluate(" ") has not shown any great surprises yet.

Code:

---

Sub IstTextIstNumber() '  https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21970&viewfull=1#post21970
Dim Ws12 As Worksheet: Set Ws12 = ThisWorkbook.Worksheets.Item("Sheet1 (2)")
Dim Rng As Range: Set Rng = Ws12.Range("A6:B7")
Dim vTemp As Variant
 Let vTemp = Evaluate("=IF({1},ISTEXT(A6:B7))") ' https://i.postimg.cc/jWQ0QHvt/ISTEXT.jpg
 Let Ws12.Range("A16:B17").Value = vTemp
 Let Ws12.Range("A16:B17").Value = Evaluate("=IF({1},ISTEXT(A6:B7))")
 
 Let vTemp = Evaluate("=IF(ISTEXT(A6:B7),""True"",""False"")") ' https://i.postimg.cc/FYS2NMfT/IF-ISTEXT.jpg
 Let Ws12.Range("A18:B19").Value = vTemp
 Let Ws12.Range("A18:B19").Value = Evaluate("=IF(ISTEXT(A6:B7),""True"",""False"")")
 
 Let vTemp = Evaluate("=IF({1},ISNUMBER(A6:B7))") ' https://i.postimg.cc/VN5tDYMq/ISNUMBER.jpg
 Let Ws12.Range("A20:B21").Value = vTemp
 Let Ws12.Range("A20:B21").Value = Evaluate("=IF({1},ISNUMBER(A6:B7))")
 
 Let vTemp = Evaluate("=IF(ISNUMBER(A6:B7),""True"",""False"")") ' https://i.postimg.cc/SRpFgxXj/IF-ISNUMBER.jpg
 Let Ws12.Range("A22:B23").Value = vTemp
 Let Ws12.Range("A22:B23").Value = Evaluate("=IF(ISNUMBER(A6:B7),""True"",""False"")")


'_________________




End Sub

---

Now let’s move on to the more practical use
To recap:
We had the idea that we might be able to use the in-built Excel stuff ISTEXT or ISNUMBER to sort out our Number stored as text issue.
So far it looks unlikely………………, (but as a spoiler – maybe I don’t need to!)

Now, in a spare range, C12:D13, in the spreadsheet I did this CSE type 2 thing {=IF(ISNUMBER(A6:B7),1*A6:B7,A6:B7)}, and got this https://i.postimg.cc/PfWKPgtj/ISNUMB...iour-in-IF.jpg
Attachment 5204https://i.postimg.cc/yWN0GrgQ/ISNUMB...iour-in-IF.jpg https://i.postimg.cc/PfWKPgtj/ISNUMB...iour-in-IF.jpg
It appeared that we got the 1* done on the 55, but not on the 44, which was also thought a possibility, so making the ISNUMBERless useful to recognise that 44 as a number. But we noted it went half way, it’s doing half the job, sort of. We further noted that this rather means that the simple = A6 is doing half the job, sort of. This half the job, that is to say the half not yet finished, is something going on in the spreadsheet. Maybe that tells us what is going to happen next
So here we go:

Next post








.
.
.
.



.

.



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=ySENWFIkL7c
https://www.youtube.com/watch?v=ySENWFIkL7c&lc=UgyqIYcMnsUQxO5CVyx4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg. 9k3ShckGnhv9k89LsaigoO
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg. 9k-vfnj3ivI9k8B2r_uRa2
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg
https://www.youtube.com/watch?v=vXyMScSbhk4
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgynOSp1dleo-Z8L_QN4AaABAg.9jJLDC1Z6L-9k68CuL4aTY
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwV5N_ulFXYMNbyQG54AaABAg. 9itCkoVN4w79itOVYVvEwQ
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Now let’s move on to the more practical use
To recap:
We had the idea that we might be able to use the in-built Excel stuff ISTEXT or ISNUMBER to sort out our Number stored as text issue.
So far it looks unlikely………………, (but as a spoiler – maybe I don’t need to!)

Now, in a spare range, C12:D13, in the spreadsheet I did this CSE type 2 thing {=IF(ISNUMBER(A6:B7),1*A6:B7,A6:B7)}, and got this https://i.postimg.cc/PfWKPgtj/ISNUMB...iour-in-IF.jpg
Attachment 5204https://i.postimg.cc/yWN0GrgQ/ISNUMB...iour-in-IF.jpg https://i.postimg.cc/PfWKPgtj/ISNUMB...iour-in-IF.jpg
It appeared that we got the 1* done on the 55, but not on the 44, which was also thought a possibility, so making the ISNUMBER less useful to recognise that 44 as a number. But we noted it went half way, it’s doing half the job, sort of. We further noted that this rather means that the simple = A6 is doing half the job, sort of. This half the job, that is to say the half not yet finished, is something going on in the spreadsheet. Maybe that tells us what is going to happen next
So here we go:
'_________________

Code:

---

'_________________
' THE MORE PRACTICAL USE.....  IT APPEARED THAT WE GOT THE 1* DONE ON THE 55, BUT NOT ON THE 44, WHICH WAS ALSO THOUGHT A POSSIBILITY, SO MAKING THE ISNUMBERLESS USEFUL TO RECOGNISE THAT 44 AS A NUMBER. BUT WE NOTED IT WENT HALF WAY, IT’S DOING HALF THE JOB, SORT OF. WE FURTHER NOTED THAT THIS RATHER MEANS THAT THE SIMPLE = A6 IS DOING HALF THE JOB, SORT OF. THIS HALF THE JOB, THAT IS TO SAY THE HALF NOT YET FINISHED, IS SOMETHING GOING ON IN THE SPREADSHEET. MAYBE THAT TELLS US WHAT IS GOING TO HAPPEN NEXT
DIM STREVAL AS STRING
 LET STREVAL = "=IF(ISNUMBER(A6:B7),1*A6:B7,A6:B7)"
 LET VTEMP = EVALUATE("" & STREVAL & "")  '  Attachment 5121https://i.postimg.cc/McK3CHRC/Ahh-it...at-in-cell.jpg
 LET RANGE("C12:D13").VALUE = VTEMP
'  AHH, ITS WORKING SOMEHOW UNEXPECTIDLY  IN EVALUATE ( IF(ISNUMBER(  ),  ,)  THEN PUT THAT IN CELL

---

https://i.postimg.cc/tgh0VVg4/Ahh-it...at-in-cell.jpg

Strange , it works fully.
, and a quick check with strEval = "=IF(ISNUMBER(A6:B7),1*A6:B7,2*A6:B7)" confirms we are selecting the “ else ” part from the IF( , , else ) for the 44
https://i.postimg.cc/nC81B2FR/It-is-...g-else-bit.jpg



So what is going on, next post is ….' Text strings in and out of spreadsheet and effect on Text held as number thing

---

Code:

---

Option Explicit
Sub IstTextIstNumber() '  https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21970&viewfull=1#post21970
Dim Ws12 As Worksheet: Set Ws12 = ThisWorkbook.Worksheets.Item("Sheet1 (2)")
Dim Rng As Range: Set Rng = Ws12.Range("A6:B7")
Dim vTemp As Variant
 Let vTemp = Evaluate("=IF({1},ISTEXT(A6:B7))") '
 Let Ws12.Range("A16:B17").Value = vTemp
 Let Ws12.Range("A16:B17").Value = Evaluate("=IF({1},ISTEXT(A6:B7))")
 
 Let vTemp = Evaluate("=IF(ISTEXT(A6:B7),""True"",""False"")") '
 Let Ws12.Range("A18:B19").Value = vTemp
 Let Ws12.Range("A18:B19").Value = Evaluate("=IF(ISTEXT(A6:B7),""True"",""False"")")
 
 Let vTemp = Evaluate("=IF({1},ISNUMBER(A6:B7))") '
 Let Ws12.Range("A20:B21").Value = vTemp
 Let Ws12.Range("A20:B21").Value = Evaluate("=IF({1},ISNUMBER(A6:B7))")
 
 Let vTemp = Evaluate("=IF(ISNUMBER(A6:B7),""True"",""False"")") '
 Let Ws12.Range("A22:B23").Value = vTemp
 Let Ws12.Range("A22:B23").Value = Evaluate("=IF(ISNUMBER(A6:B7),""True"",""False"")")


'_________________
' The more practical use.....  It appeared that we got the 1* done on the 55, but not on the 44, which was also thought a possibility, so making the ISNUMBERless useful to recognise that 44 as a number. But we noted it went half way, it’s doing half the job, sort of. We further noted that this rather means that the simple = A6 is doing half the job, sort of. This half the job, that is to say the half not yet finished, is something going on in the spreadsheet. Maybe that tells us what is going to happen next
Dim strEval As String
 Let strEval = "=IF(ISNUMBER(A6:B7),1*A6:B7,A6:B7)"
 Let vTemp = Evaluate("" & strEval & "")  '  https://i.postimg.cc/qhS9F61w/Ahh-it...at-in-cell.jpg
 Let Range("C12:D13").Value = vTemp
'  Ahh, its working somehow unexpectidly  in Evaluate ( IF(ISNUMBER(  ),  ,)  then put that in cell
'  https://i.postimg.cc/kGgqBx80/Ahh-it...at-in-cell.jpg
'
 Let strEval = "=IF(ISNUMBER(A6:B7),1*A6:B7,2*A6:B7)"
 Let vTemp = Evaluate("" & strEval & "")  '
 Let Range("C12:D13").Value = vTemp
'  https://i.postimg.cc/nC81B2FR/It-is-...g-else-bit.jpg
End Sub

---

.
.
.
.
.
..
.
.
.
.
.
.
..

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=ySENWFIkL7c
https://www.youtube.com/watch?v=ySENWFIkL7c&lc=UgyqIYcMnsUQxO5CVyx4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg. 9k3ShckGnhv9k89LsaigoO
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg. 9k-vfnj3ivI9k8B2r_uRa2
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg
https://www.youtube.com/watch?v=vXyMScSbhk4
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgynOSp1dleo-Z8L_QN4AaABAg.9jJLDC1Z6L-9k68CuL4aTY
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwV5N_ulFXYMNbyQG54AaABAg. 9itCkoVN4w79itOVYVvEwQ
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Text strings in and out of spreadsheet and effect on Text held as number thing

The following macro in conjunction with the spreadsheet range, in this post in picture, (in the next post in more detail), experiments with putting the value of 44 in a spreadsheet in different ways, mostly as a string, and makes some attempt to see how the thing is then seen.
It is a bit too boring to explain every code line and everything in great detail. If and when you have the time then best go through the macro in the VB Editor in step F8 debug mode and follow through the various 'comments as you go along.
I will just give my conclusions at the end of the next post

Code:

---

'  https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21975&viewfull=1#post21975
Sub Putting44HeldAsStringTypeInSpreadsheet_ThatStringIsInVariousVariablesAndArrays() ' https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21975&viewfull=1#post21975
    Dim Ws12 As Worksheet: Set Ws12 = ThisWorkbook.Worksheets.Item("Sheet1 (2)")
    Ws12.Range("A30:C42").Clear
30  ' Simple string variable
    Dim StrNmbr As String
    Let StrNmbr = "44": Ws12.Range("B30").Value = VarTyp(VarType(StrNmbr))
    Let Ws12.Range("A30").Value = StrNmbr: Let Ws12.Range("C30").Value = VarTyp(VarType(Ws12.Range("A30").Value))
31  ' Single Element of Single Element String type Element array
    Dim StrNbr(1 To 1) As String
    Let StrNbr(1) = "44": Ws12.Range("B31").Value = VarTyp(VarType(StrNbr(1)))
    Let Ws12.Range("A31").Value = StrNbr(1): Let Ws12.Range("C31").Value = VarTyp(VarType(Ws12.Range("A31").Value))
32  ' Single Element String type Element array
                          Ws12.Range("B32").Value = VarTyp(VarType(StrNbr(1)))
    Let Ws12.Range("A32").Value = StrNbr(): Let Ws12.Range("C32").Value = VarTyp(VarType(Ws12.Range("A32").Value))
33  ' Single Element of 2 Element String type Element array
    Dim StrNbrs(1 To 2) As String
    Let StrNbrs(1) = "44": Ws12.Range("B33").Value = VarTyp(VarType(StrNbrs(1)))
    Let StrNbrs(2) = "45"
    Let Ws12.Range("A33").Value = StrNbrs(1): Let Ws12.Range("C33").Value = VarTyp(VarType(Ws12.Range("A33").Value))
34  ' 2 Element String type Element array
                          Ws12.Range("B34").Value = VarTyp(VarType(StrNbrs()))
    Let Ws12.Range("A34").Value = StrNbrs(): Let Ws12.Range("C34").Value = VarTyp(VarType(Ws12.Range("A34").Value))

35  ' Referrrence from a cell that is exhibiting the  Number stored as text
                                                                                                                    Let Ws12.Range("A35").Value = "=A34": Ws12.Range("B35").Value = "** A35  is got from  =A34": Ws12.Range("B35").Characters(Start:=21, Length:=5).Font.Name = "Courier New"
                                              Let Ws12.Range("C35").Value = VarTyp(VarType(Ws12.Range("A35").Value))
   
36  ' A Variant variable holding a String
    Dim vStrNmbr As Variant
    Let vStrNmbr = "44": Ws12.Range("B36").Value = VarTyp(VarType(vStrNmbr))
    Let Ws12.Range("A36").Value = vStrNmbr: Let Ws12.Range("C36").Value = VarTyp(VarType(Ws12.Range("A36").Value))
37  ' Single Element (String type) of a Single Element Variant type Element array
    Dim vStrNbr(1 To 1) As Variant
    Let vStrNbr(1) = "44": Ws12.Range("B37").Value = VarTyp(VarType(vStrNbr(1)))
    Let Ws12.Range("A37").Value = vStrNbr(1): Let Ws12.Range("C37").Value = VarTyp(VarType(Ws12.Range("A37").Value))
                          Ws12.Range("B38").Value = VarTyp(VarType(vStrNbr()))
38  ' Single Element Variant type array (holding String type)
    Let Ws12.Range("A38").Value = vStrNbr(): Let Ws12.Range("C38").Value = VarTyp(VarType(Ws12.Range("A38").Value))
39  ' Single Element(A String type) from a Variant 2 Element Array
    Dim vStrNbrs(1 To 2) As Variant
    Let vStrNbrs(1) = "44": Ws12.Range("B39").Value = VarTyp(VarType(vStrNbrs(1)))
    Let vStrNbrs(2) = "45"
    Let Ws12.Range("A39").Value = vStrNbrs(1): Let Ws12.Range("C39").Value = VarTyp(VarType(Ws12.Range("A39").Value))
                          Ws12.Range("B39").Value = VarTyp(VarType(vStrNbrs()))
40  ' Variant 2 Element Array (holding string types)
    Let Ws12.Range("A40").Value = vStrNbrs(): Let Ws12.Range("C40").Value = VarTyp(VarType(Ws12.Range("A40").Value))
   
                                             
41  ' Referrence from a referrrence from a cell that is exhibiting the  Number stored as text
                                                                                                                    Let Ws12.Range("A41").Value = "=A35": Ws12.Range("B41").Value = "** A41  is got from  =A35": Ws12.Range("B41").Characters(Start:=21, Length:=5).Font.Name = "Courier New"
                                              Let Ws12.Range("C41").Value = VarTyp(VarType(Ws12.Range("A41").Value))
   
                                                                                                                   
                                                                                                                   
                                                                                                                   
                                                                                                                   
'    https://i.postimg.cc/8z8NGnQ9/Text-strings-in-and-out-of-spreadsheet-and-effect-on-Text-held-as-number-thing.jpg
'    Attachment 5123 https://i.postimg.cc/BXCdcpvB/Text-s...mber-thing.jpg
'    https://i.postimg.cc/8z8NGnQ9/Text-s...mber-thing.jpg
                                                                                                                   
                                                                                                                    Let Ws12.Range("A42").Value = "=IF(1=1,A41)"
End Sub
Public Function VarTyp(ByVal Nr As Long) As String
    Select Case Nr
    Case 0
      Let VarTyp = "Empty" ' (uninitialized)
    Case 1
      Let VarTyp = "Null"  '  (no valid data)
    Case 2
      Let VarTyp = "Integer"
    Case 3
      Let VarTyp = "Long integer"
    Case 4
      Let VarTyp = "Single-precision floating-point number"
    Case 5
      Let VarTyp = "Double-precision floating-point number"
    Case 6
      Let VarTyp = "Currency value"
    Case 7
      Let VarTyp = "Date value"
    Case 8
      Let VarTyp = "String"
    Case 9
      Let VarTyp = "Object"  'If an object is passed and has a default property, VarType(object) returns the type of the object's default property.
    Case 10
      Let VarTyp = "Error value"
    Case 11
      Let VarTyp = "Boolean value"
    Case 12
      Let VarTyp = "Variant" '  (used only with arrays of variants)
    Case 13
      Let VarTyp = "A data access object"
    Case 14
      Let VarTyp = "Decimal value"
    Case 17
      Let VarTyp = "Byte value"
    Case 20
      Let VarTyp = "LongLong integer (valid on 64-bit platforms only)"
    Case 36
      Let VarTyp = "Variants that contain user-defined types"
     
      'vbArray 8192    Array (always added to another constant when returned by this function)
      'The VarType function never returns the value for vbArray by itself. It's always added to some other value to indicate an array of a particular type. For example, the value returned for an array of integers is calculated as vbInteger + vbArray, or 8194.
    Case 8192 + 8
      Let VarTyp = "Array of String type Elements"
    Case 8192 + 12
      Let VarTyp = "Array of Variant type Elements"
    Case Else
      Let VarTyp = "Bollox to  " & Nr & "  because I don't recognise that number"
    End Select
   
End Function
'  https://learn.microsoft.com/en-us/of...rtype-function
'Either one of the following constants or the summation of a number of them is returned. ( These constants are specified by Visual Basic for Applications. The names can be used anywhere in your code in place of the actual values. )
'
'Constant    Value  Description
'vbEmpty 0  Empty (uninitialized)
'vbNull  1  Null (no valid data)
'vbInteger  2  Integer
'vbLong  3  Long integer
'vbSingle    4  Single-precision floating-point number
'vbDouble    5  Double-precision floating-point number
'vbCurrency  6  Currency value
'vbDate  7  Date value
'vbString    8  String
'vbObject    9  Object
'If an object is passed and has a default property, VarType(object) returns the type of the object's default property.
'vbError 10  Error value
'vbBoolean  11  Boolean value
'vbVariant  12  Variant (used only with arrays of variants)
'vbDataObject    13  A data access object
'vbDecimal  14  Decimal value
'vbByte  17  Byte value
'vbLongLong  20  LongLong integer (valid on 64-bit platforms only)
'vbUserDefinedType  36  Variants that contain user-defined types
'vbArray 8192    Array (always added to another constant when returned by this function)
'The VarType function never returns the value for vbArray by itself. It's always added to some other value to indicate an array of a particular type. For example, the value returned for an array of integers is calculated as vbInteger + vbArray, or 8194.
'



---

https://i.postimg.cc/8z8NGnQ9/Text-s...mber-thing.jpg
Attachment 5205 https://i.postimg.cc/BXCdcpvB/Text-s...mber-thing.jpg




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

Text strings in and out of spreadsheet and effect on Text held as number thing

In the following spreadsheet range, the range A30:C41 is filled in by the macro from the last post, ( also included in the file attached to this post )
What we are basically doing is storing the value of 44 in string format in various ways,
_ in simple variables and also
_ as elements in an array
, and then we look at putting
_ those variables,
_ or an element in an array,
_ or the whole array
, into a spreadsheet using the _ Range("G45").Value = __ type of way


https://i.postimg.cc/8z8NGnQ9/Text-s...mber-thing.jpg
Attachment 5205 https://i.postimg.cc/BXCdcpvB/Text-s...mber-thing.jpg
https://i.postimg.cc/8z8NGnQ9/Text-s...mber-thing.jpg

Code:

---

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

Row\Col	A	B	C	D
29		Variable or array Element Type	Type got from cell via  .Value  after puting vaiable in cell with  .Value , ( ** or from cell referrence )
30	44	String	Double-precision floating-point number	Simple string variable
31	44	String	Double-precision floating-point number	Single Element of Single Element String type Element array
32	44	String	String	Single Element String type Element array
33	44	String	Double-precision floating-point number	Single Element of 2 Element String type Element array
34	44	Array of String type Elements	String	2 Element String type Element array
35	44	** A35  is got from  =A34	String	Referrrence from a cell that is exhibiting the  Number stored as text    thing
36	44	String	Double-precision floating-point number	A Variant variable holding a String
37	44	String	Double-precision floating-point number	Single Element (String type) of a Single Element Variant type Element array
38	44	Array of Variant type Elements	Double-precision floating-point number	Single Element Variant type array (holding String type)
39	44	Array of Variant type Elements	Double-precision floating-point number	Single Element(A String type) from a Variant 2 Element Array
40	44		Double-precision floating-point number	Variant 2 Element Array (holding string types)
41	44	** A41  is got from  =A35	String	Referrence from,  a referrrence from a cell that is exhibiting the    Number stored as text  thing
42	44	Formula in A42 is  =IF(1=1,A41)

Worksheet: Sheet1 (2)

---

Conclusions:
_1) If the thing we try to put in a cell via like Range("G45").Value = is a simple single value variable, there does not appear to be a way to put "44" in a cell which would result in getting that _ Number held as text thing _
_2) If the thing we try to put in a cell via like Range("G45").Value = is an element in an array, then the situation is slightly more varied. In order to get that Number held as text thing ,
_2)(i) the array type must be String. (It can even be an array of just one element as in the experiment example line 32)
, but _2)(ii) It must be applied as a an array – if you apply a single specific element from the array, then you will not get that Number held as text thing

So that is the case when we put in a cell or cells via like Range("G45").Value = , a variable or element of an array, or an array, and it seems that only an array of string type will work to get that Number held as text thing thing

In the next post we will look at consequences from these conclusions on Evaluate Range type solutions.

---

A few extra rough notes here
https://www.excelfox.com/forum/showt...ll=1#post11210
https://www.excelfox.com/forum/showt...age2#post11210


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=ySENWFIkL7c
https://www.youtube.com/watch?v=ySENWFIkL7c&lc=UgyqIYcMnsUQxO5CVyx4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg. 9k3ShckGnhv9k89LsaigoO
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg. 9k-vfnj3ivI9k8B2r_uRa2
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg
https://www.youtube.com/watch?v=vXyMScSbhk4
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgynOSp1dleo-Z8L_QN4AaABAg.9jJLDC1Z6L-9k68CuL4aTY
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwV5N_ulFXYMNbyQG54AaABAg. 9itCkoVN4w79itOVYVvEwQ
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

hkjh


.
.
.
.
.
..
.
.
.
.
.
.
..

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=ySENWFIkL7c
https://www.youtube.com/watch?v=ySENWFIkL7c&lc=UgyqIYcMnsUQxO5CVyx4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg. 9k3ShckGnhv9k89LsaigoO
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg. 9k-vfnj3ivI9k8B2r_uRa2
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg
https://www.youtube.com/watch?v=vXyMScSbhk4
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgynOSp1dleo-Z8L_QN4AaABAg.9jJLDC1Z6L-9k68CuL4aTY
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwV5N_ulFXYMNbyQG54AaABAg. 9itCkoVN4w79itOVYVvEwQ
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

öjjj

.
.
.
.
.
..
.
.
.
.
.
.
..

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=ySENWFIkL7c
https://www.youtube.com/watch?v=ySENWFIkL7c&lc=UgyqIYcMnsUQxO5CVyx4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg. 9k3ShckGnhv9k89LsaigoO
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg. 9k-vfnj3ivI9k8B2r_uRa2
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg
https://www.youtube.com/watch?v=vXyMScSbhk4
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgynOSp1dleo-Z8L_QN4AaABAg.9jJLDC1Z6L-9k68CuL4aTY
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwV5N_ulFXYMNbyQG54AaABAg. 9itCkoVN4w79itOVYVvEwQ
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

khkjhh

.
.
.
.
.
..
.
.
.
.
.
.
..

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=ySENWFIkL7c
https://www.youtube.com/watch?v=ySENWFIkL7c&lc=UgyqIYcMnsUQxO5CVyx4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg. 9k3ShckGnhv9k89LsaigoO
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg. 9k-vfnj3ivI9k8B2r_uRa2
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg
https://www.youtube.com/watch?v=vXyMScSbhk4
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgynOSp1dleo-Z8L_QN4AaABAg.9jJLDC1Z6L-9k68CuL4aTY
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwV5N_ulFXYMNbyQG54AaABAg. 9itCkoVN4w79itOVYVvEwQ
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

öjlkjj


.
.
.
.
.
..
.
.
.
.
.
.
..

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=ySENWFIkL7c
https://www.youtube.com/watch?v=ySENWFIkL7c&lc=UgyqIYcMnsUQxO5CVyx4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg. 9k3ShckGnhv9k89LsaigoO
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg. 9k-vfnj3ivI9k8B2r_uRa2
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg
https://www.youtube.com/watch?v=vXyMScSbhk4
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgynOSp1dleo-Z8L_QN4AaABAg.9jJLDC1Z6L-9k68CuL4aTY
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwV5N_ulFXYMNbyQG54AaABAg. 9itCkoVN4w79itOVYVvEwQ
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA