_(ii)
An answer Idea to the main question of the Thread.
It’s a bit of a gut Layman feeling based on a lot of stuff in those links. Just an idea.
I am not considering copying and pasting. Just because I am not and wasn’t. What I have spent some time looking at on and off for a week or two, is range objects,
Evaluate(" ") , putting things in cells, and in particular what this typical pseudo code line is all about
Let Range2.Value = Range1.Value
The LHS of that pseudo code line “writes stuff in a cell”, and
most often using that will not get the
Number stored as text thing, even if you use it to put in a string or string variable, or even an element from a sting type array. Excel will
mostly do what it does when you manually type stuff in and it will decide what type it is and how to show it. If it looks like a number, that is what you will get - a number, in the cell even if it was a string that got put in. The main exception to that I found is if you try to put in an array of string type elements, and it must be the array you put in, even if it is just one cell. If you put in a single element you won’t get that
Number stored as text thing.
So, What is going on
This is an idea, a wild theory:
Low level computer stuff does not understand what a single value is. That is a human concept it don’t understand. Excel is a high level thing to interface with us humans and a lot of effort is put into making things appear in a box in different formats. Similarly, VBA , unlike more low level , more efficient computer stuff, is quite tolerant when you put a text that looks like a number in something wanting a number.
I am thinking that a computer lower down thinks in arrays all the time, so it works more efficiently with lots of data at once, not one value at a time. I am thinking that string type arrays are maybe most of what computers are.
We can use other things either external to Excel or things that were developed from more fundamental low level stuff. That could be the case with Text box. Perhaps when it puts stuff in a cell it uses more low level stuff. You might think you are putting a value in a cell from it. But I am suggesting you are putting an array in, but perhaps just restricting what you see of it by virtue of what you do on the LHS of that code line.
I think a lot of low level stuff in computers is about String manipulation. I often heard smarter people tell me string functions are old, fundamental, and therefore efficient. Chris told me once computers are just strings. I wonder perhaps if we could advance that a bit and say computers are just arrays of strings, or string arrays, possibly one dimensional**, interacting. (** What we think are two dimensional arrays are just 1 dimensional arrays of 1 dimensional array elements, or it’s all just a lot of complex offsets, which is the same thing just said a bit differently, perhaps)
This simple macro will demo some of my thinking
Code: Select all
Sub ComputerStuff() ' https://eileenslounge.com/viewtopic.php?p=309144#p309144
Dim Ws As Worksheet: Set Ws = ActiveSheet
Dim Computer1() As String, Computer2(1 To 1) As String, Excl(1 To 1) As Variant, Str As String
Let Computer1() = Split("1 2"): Let Computer2(1) = "3": Let Excl(1) = "4": Let Str = "5"
' These next lines all get you that Number stored as text thing This is doing something at a low level that you as a human can't do, and Excel probably does not try to do so much with it and it gets put in quite quickly and efficiently
Let Ws.Range("A20") = Computer1()
Let Ws.Range("A21:B21") = Computer1()
Let Ws.Range("A22") = Computer2()
' These don't get you that Number stored as text thing This is putting a value in a cell in a similar way to what you as a human can, and Excel probably takes a look at it and has a go at doing something with it before it finally gets put in
Let Ws.Range("A23") = "6"
Let Ws.Range("A24") = Str
Let Ws.Range("A25") = Computer1(0)
Let Ws.Range("A26") = Computer2(1)
Let Ws.Range("A27") = Excl(1)
Let Ws.Range("A28") = Excl()
End Sub
Computer String Stuff.JPG
Perhaps the reason why the makers of Excel decided to give you that warning is that if you are in a habit of using things that put things in a cell using more low level stuff, then maybe you will also take that data and put into other more low level stuff, or use it in more low level programming, in which case you might get the type mismatch problems that you experience more often in more low level coding.
That’s it, just a layman idea, I thought I would post it and run,
:-)
Alan
_.__________________________________________________________________
Number stored as text, alignment of numeric values in cells.xls https://app.box.com/s/ruodtsrly19ru3dv2d1404bykvhjrdsw
VBA Evaluate Range Value values Aug 2023.xls https://app.box.com/s/puxvbuyjz1s4fyu9r3bsjtdtr8qijph6 You do not have the required permissions to view the files attached to this post.
Chris
) when I suggested that I "could apply formatting" in VBA.
