Right Hand Side Range Range Value values Range=Range.Value only sometimes. Range Range.Value Anomaly

**DocAElstein** · 08-14-2023, 12:17 PM

This Post is #23192 #2
https://www.excelfox.com/forum/showt...ll=1#post23192

Right Hand Side Range Range Value values
Range= equivalent to Range.Value= only sometimes. Range Range.Value Anomaly

Question from the web (https://www.mrexcel.com/board/ ( forum) )
https://www.excelfox.com/forum/showt...age2#Post21994
That was a good question, Alan.

Answer
I want to limit the discussion mainly to a general ranges code lines of these pseudo form
Let Range2.Value = Range1.Value
Let Range2 = Range1.Value
Let Range2.Value = Range1
Let Range2 = Range1
, which in words is something like,
Writing in Cells = Reading text and/ or numbers
LHS
Also to limit the discussion here, I want to say, and please Let me, for now that the LHS variations all do the same, which is Writing in a Cells, and consequence of that is that anything to do with number values, text values in most## cases doing something very similar to how us Human’s would physically write things in cells. A consequence of that is that whether we have a formula or not is determined by whether the first character is an = , and this means that all these sort of "things" do the same. (I would tend to call these "things" values )
FormulaR1C1Local
FormulaR1C1
FormulaR1C1Local
Value2
Value(RangeValueDataType:=xlRangeValueDefault)
Formula
(These things are handled different to other things such that we can write in a single value or all values in a multi cell rectangular range of values all in one go)
So I would like to take the liberty of leaving the LHS as this
Let Range2 =

A further simplification for the sake of clarity that I would like to make is to stay with ranges / cells, on the LHS and not get into variables too much there. Such considerations lead to further issues and complications such as whether things coerce or Excel is just written to do things in certain conditions, and possible Default Member Recursion Limits, and discussions of whether entrails should be pulled out of the LHS when sacrificing a virgin…etc, etc. I want to avoid that.

RHS
This is what it is all about, here.
I think it is an over simplification, and arguably wrong to say that the .Value is generally a Property and the default property of a range object.
For one thing the LHS writing .Value and RHS read .Value seem to be different/ only loosely related
We already discussed briefly the LHS, and we might at a stretch of the imagination be able to say that default property is value. I might argue that it is the other way around. Something like, on the writing LHS, the default of these values things,
Range.FormulaR1C1Local =
Range.FormulaR1C1 =
Range.FormulaR1C1Local =
Range.Value2 =
Range.Value(RangeValueDataType:=xlRangeValueDefaul t) =
Range.Formula =
, is
Range =
I can’t even see an Range.Value Property there. I can see a Range.Value(RangeValueDataType:=xl________) thing. That looks like a method to me. It acts like one as well, doing quite different things depending on the argument you give it.
Anyway back to the RHS
It does not appear to me to have a default property. You are reading stuff, and you had better be sure you tell the Range1 object what you want, or else people like RoryA will start confusing you with rubbish like Default Member Recursion Limits and god knows what else he either dreamt up, doesn’t understand or can’t or won’t explain.
Now if you don’t use something such as
.Value( RangeValueDataType:=xl________ )
( , or
.Value
, if you only feel like being semi – Explicit ),
, then something else happens….
I still don’t know for sure what happens, but a couple of guesses…
_ The Range1 if used on the RHS is an object. Some objects have a "value" that is a text and/or number string that is returned if you try to assign that object somewhere expecting to have some text and/or number string given to it. This "value" might be that thing you see, for example, in the Watch Window.
https://i.postimg.cc/y83k0dH3/range-...ate-Window.jpg

Example macro in next post:
Here a brief description of what is going on in it
In '0a) I have found some ways empirically to get that Number held as Text thing . The thing that they have in common is putting an element of an array in a cell ( which we can’t manually do ) , and that element must be of a string type. As a Layman, I am thinking a couple of things
_ that low level computer stuff is all about arrays of strings. I am thinking that concepts of single values, variables and the such is high level stuff that helps us Humans to interface with computers, which is what spreadsheets are about: Tables and boxes with things in them.
_ once in a while, deliberately or by accident, perhaps often deliberately for efficiency, some processes by pass some of the high level Excel stuff which results in a number not getting in a spreadsheet as a number, but as a text that looks like one. For a lot of Excel and VBA stuff that is less important since things are often obliging, as a high level thing like Excel arguably should be. So often numbers held as text is handled as if they were normal numbers. But because we can use stuff external to Excel through VBA, some of which can be more intermediate level computer stuff, Microsoft give us an indication of when a number is held in the more low level string form, as in some things you could get problems such as type mismatch, as more intermediate level stuff may not have that extra wiring to convert/ coerce that VBA does.

In '0b) , for comparison, I believe VBA may be doing something very similar to what we can do manually: It is putting a number in a cell. Excel handles it, as it does, when we do it, and even of you are pretty sure it is a string, you won’t get the Number held as Text thing
'0c) demonstrates that putting an array generally into a cell, won’t necessarily get us that Number held as Text thing .

Rem 1 This is the Answer, at least as far as I have got. Some objects, when assigned to somewhere or assigned to a variable, other than an Object variable, give a string. It might be this thing, it might not.
https://i.postimg.cc/y83k0dH3/range-...ate-Window.jpg

One thing that supports the idea is that if we repeat the experiment with a multi cell range object we get nothing in the cell and see nothing in the value place in the Immediate Window
https://i.postimg.cc/BvBQ2KRn/Multi-...ate-Window.jpg