Tests and Notes on Range objects in Excel Cell

**DocAElstein** · 08-24-2019, 01:01 PM

Nov 11, 2014
https://www.mrexcel.com/board/thread.../#post-3990978

Hi,
. Getting very frustrated as a VBA Beginner on some fundamental stuff. Please Help with a simple to understand explanation.
. The problem is An anomaly I found by chance, that is to say through occasional Typos in various codes I had written which assigned range values to a cell or range of cells in a column…
. As I investigated I hit on some fundamental curiosities, the answer to which I could not find by googling or Books etc. Therefore I ask the experts here for help:
.
. A simple case to demonstrate:
. Assume in Column A I have some arbitrary values and I copy them into Column C. For the purposes of this experiment I am using Range to do this

. For one row any of the following 4 code lines work:

Code:

Sub RangeRangeDotValueAnomalySingleCells()
  Let Range("C1").Value = Range("A1").Value 'Works: returns value in cell A1 in Cell C1
 Let Range("C1") = Range("A1") 'Works: returns value in cell A1 in Cell C1
 Let Range("C1") = Range("A1").Value 'Works: returns value in cell A1 in Cell C1
  Let Range("C1").Value = Range("A1") 'Works: returns value in cell A1 in Cell C1
 End Sub

. The first line working ties up with everything one learns and sees. That the second line works I am not too sure about. The third and forth lines I expected to error with a type mismatch, that is to say comparing an Object with a Variable

………………………

Taking the experiment one stage further to consider multiple cell Ranges the results puzzle me even further.
. For Rows 3 to 10 the corresponding 4 code lines give the results indicated in the green comments alongside each line

Code:

Sub RangeRangeDotValueAnomalyMultipleCells()
 Dim rngA As Range, rngC As Range
Set rngA = Range("A3:A10")
Set rngC = Range("C3:C10")
 Let rngC.Value = rngA.Value 'Works! Returns values form First column in Third column
 Let rngC = rngA ' Returns empty column in Third column
 Let rngC = rngA.Value 'Works! Returns values form First column In Third Column
 Let rngC.Value = rngA ' Returns empty column in Third column
 End Sub

I realize on the face of it this seems a somewhat simple question, but a full understanding could be very helpful in some more difficult anomalies I have been faced with in codes evaluating various ranges.
. I very much appreciate any helpful explanations easy for a VBA novice to understand.
Alan.

(P.s. I realize that Let is unnecessary, it is just a throw back to 25 years ago – the last time I did any programming and as it “Lets” me do it I find it helpful as a beginner in VBA with distinguishing the 3 cases whereby Let or Set or nothing is allowed or required!)

' Nov 13, 2014 https://www.mrexcel.com/board/thread...nomaly.817965/

Code:

Sub RangeArrayDirect()
'Dim RangeArray() As Variant  '. As you can see I have “Commented out” the first Dim statement as if I use this instead I get a Type Mismatch error ( I prefer to use such a Dim statement to be as explicit as possible in dimensioning which I believe is good practice.)
Dim RangeArray As Variant
Let RangeArray = Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3)) '
'                                Watch : + : Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3)) :  : Variant/Object/Range : Tabelle6.RangeArrayDirect
End Sub

'. I can overcome the problem by adding an in between step (which is probably good practice anyway) as follows:

Code:

Sub RangeArrayIndirect()
Dim RangeArray() As Variant
Dim rngRangeArray As Range
Set rngRangeArray = Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3))
Let RangeArray = rngRangeArray ' Watch : + : rngRangeArray :  : Range/Range : Tabelle6.RangeArrayIndirect
End Sub

Code:

Option Explicit
'    https://www.mrexcel.com/board/threads/range-equivalent-to-range-value-sometimes-range-range-value-anomaly.817446/                                                                                                                                  https://fastexcel.wordpress.com/2017/03/13/excel-range-valuevaluetype-what-is-this-parameter/
'  Nov 11, 2014
Sub RangeRangeDotValueAnomalySingleCells()
 Let Range("C1").Value = Range("A1").Value 'Works: returns value in cell A1 in Cell C1
 Let Range("C1") = Range("A1") 'Works: returns value in cell A1 in Cell C1
 Let Range("C1") = Range("A1").Value 'Works: returns value in cell A1 in Cell C1
 Let Range("C1").Value = Range("A1") 'Works: returns value in cell A1 in Cell C1

' Let Range("C1").Value = Range("A1").Range ' Complie error,  argument not optional
End Sub
Sub RangeRangeDotValueAnomalyMultipleCells()
 Dim rngA As Range, rngC As Range
Set rngA = Range("A3:A10")
Set rngC = Range("C3:C10")   '  .Range ' If you want to explicitly tell Excel to use the range object, then you can force it.  ' Complie error,  argument not optional
 Let rngC.Value = rngA.Value 'Works! Returns values form First column in Third column
 Let rngC = rngA ' Returns empty column in Third column
 Let rngC = rngA.Value 'Works! Returns values form First column In Third Column
 Let rngC.Value = rngA ' Returns empty column in Third column
 End Sub
' There is no hard documentation that I am aware of that will tell you why some of those don't work. Suffice to say, when you mean .Value, write .Value. Never use implicit defaults because it will bite you when you don't expect it if you do. As another example
Sub huh()
    Dim vData()
    Dim Ws                    As Worksheet
    Set Ws = ActiveSheet
    vData() = Range("A1:A10").Value  ' works  Watch : + : Range("A1:A10").Value :  : Variant/Variant(1 to 10, 1 to 1) : Tabelle6.huh
    vData() = ActiveSheet.Range("A1:A10").Value    ' works  Watch : + :     ActiveSheet.Range("A1:A10").Value :  : Variant/Variant(1 to 10, 1 to 1) : Tabelle6.huh
    vData() = Ws.Range("A1:A10").Value    ' works  Watch : + :     ActiveSheet.Range("A1:A10").Value :  : Variant/Variant(1 to 10, 1 to 1) : Tabelle6.huh

    vData() = Range("A1:A10")       ' works  Watch : + :     Range("A1:A10") :  : Object/Range : Tabelle6.huh
                                        ' Watch : - : vData() :  : Variant/Variant(1 to 10, 1 to 1) : Tabelle6.huh
    vData() = ActiveSheet.Range("A1:A10")    ' gives Type Mismatch error
 '                                          Watch : + : ActiveSheet.Range("A1:A10") :  : Variant/Object/Range : Tabelle6.huh
    vData() = Ws.Range("A1:A10")    ' works Watch : + : ws.Range("A1:A10") :  : Object/Range : Tabelle6.huh
End Sub
' https://www.mrexcel.com/board/threads/range-equivalent-to-range-value-sometimes-range-range-value-anomaly.817446/#post-3991004
' The only real difference between the line that fails and the one that follows is that
'   ActiveSheet returns a generic Object so you are effectively late bound.
'From the VBA language spec, this I think is the most relevant section:
' 5.6.2.3 Default Member Recursion Limits
' Evaluation of an object whose default Property Get or default function returns another object
' can lead to a recursive evaluation process if the returned object has a further default member.
' Recursion through this chain of default members may be implicit if evaluating to a simple data value
' and each default member has an empty parameter list
' , or explicit if index expressions are specified that specifically parameterize each default member.
' An implementation may define limits on when such a recursive default member evaluation is valid.
' The limits may depend on factors such as the depth of the recursion
' , implicit vs. explicit specification of empty argument lists
' , whether members return specific classes vs. returning Object or Variant
' , whether the default members are functions vs. Property Gets
' , and whether the expression occurs on the left-hand-side of an assignment.
' The implementation may determine such an evaluation to be invalid statically
' or may raise error 9 (Subscript out of range) or 13 (Type mismatch) during evaluation at runtime.
'
'
'_


'  Nov 13, 2014      https://www.mrexcel.com/board/threads/dimension-array-range-anomaly.817965/
Sub RangeArrayDirect()
'Dim RangeArray() As Variant  '. As you can see I have “Commented out” the first Dim statement as if I use this instead I get a Type Mismatch error ( I prefer to use such a Dim statement to be as explicit as possible in dimensioning which I believe is good practice.)
Dim RangeArray As Variant
Let RangeArray = Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3)) '
'                                Watch : + : Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3)) :  : Variant/Object/Range : Tabelle6.RangeArrayDirect
End Sub
'. I can overcome the problem by adding an in between step (which is probably good practice anyway) as follows:
Sub RangeArrayIndirect()
Dim RangeArray() As Variant
Dim rngRangeArray As Range
Set rngRangeArray = Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3))
'                                Watch : + : Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3)) :  : Variant/Object/Range : Tabelle6.RangeArrayIndirect
Let RangeArray() = rngRangeArray ' Watch : + : rngRangeArray :  : Range/Range : Tabelle6.RangeArrayIndirect
End Sub

**DocAElstein** · 08-24-2019, 01:01 PM

This Post is #21995 #12
https://www.excelfox.com/forum/showt...age2#Post21995

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

That perhaps makes some sense, as it could be impractical to get lots of values there. Maybe never the less they are there , which might lend some tail shirt sense to it being held in an array, of string types, although then that contradicts the fact that other objects have a string value there, but that does not appear to get put n with a warning of the Number held as Text thing
So I am not much further with this Answer

Rem 2
For the case of a formula, Excel takes a similar alignment convention, but there in not that Number held as Text warning thing . Possibly that could be because in most cases a formula would be more complicated and the result would be less ambiguous.
But, the very last code line suggests that Excel is still knowing if it has a number stored as text, and will once again let you know at least from if you do the stuff from Rem 1

**DocAElstein** · 08-24-2019, 01:01 PM

Code:

'  https://www.excelfox.com/forum/showthread.php/2914-Tests-and-Notes-on-Range-objects-in-Excel-Cell/page2#Post21995   #12
Sub RangeOnlyRHS()
Rem 0 test data
Dim Ws As Worksheet
 Set Ws = ThisWorkbook.Worksheets("RangeValuevalues")
 Ws.Range("A1:D5").Clear
'0a) This puts an array into a cell.  VBA can do this. We cant
 Let Ws.Range("A1") = Split("44")    ' This puts a 1 element string array in, which causes the   Number held as Text thing   https://i.postimg.cc/CK1bnk7G/Number-held-as-Text-thing.jpg
 '                    Watch : - :  Split("44") :  : Variant/String(0 to 0) : Tabelle6.RangeOnlyRHS
 '                              :  Split("44")(0) : "44" : String : Tabelle6.RangeOnlyRHS
 Let Ws.Range("A1") = Split("44 55") ' This does the same thing, it puts the two element array in, (but we will only see the first value as we are just filling in the first cell
'                     Watch : + :  Split("44 55") :  : Variant/String(0 to 1) : Tabelle6.RangeOnlyRHS
'                               :  Split("44 55")(0) : "44" : String : Tabelle6.RangeOnlyRHS
'                               :  Split("44 55")(1) : "55" : String : Tabelle6.RangeOnlyRHS
Dim StrS(0 To 0) As String
 Let StrS(0) = "44"
 Let Ws.Range("A1") = StrS()
'                     Watch : + :  StrS() :  : String(0 to 0) : Tabelle6.RangeOnlyRHS
'                               :  StrS()(0) : "44" : String : Tabelle6.RangeOnlyRHS
 
'0b) For comparison, VBA is putting a single string value into a cell. We can also do that. When this happens I think Excel decides what the thing is, a number or text, so if it looks like a number, then it will make it a number
 Let Ws.Range("B1") = "44"
 Let Ws.Range("B1") = Split("44")(0) '
 Let Ws.Range("B1") = Split("44 55")(0)
 Let Ws.Range("B1") = StrS()(0)
'0c) Putting an array generally into a cell will not necerssarily get you that  Number held as Text thing
Dim VarVals() As Variant: Let VarVals() = Ws.Range("A1:B1") '         ( When assigning to a variable, other than a Range object, Excel probably has a different set of rules, which some people might regard as coercing to suit the variable )
'                     Watch : + : VarVals() :  : Variant/Variant(1 to 1, 1 to 2) : Tabelle6.RangeOnlyRHS
'                             + : VarVals()(1) :  : Variant(1 to 2) : Tabelle6.RangeOnlyRHS
'                               : VarVals()(1,1) : "44" : Variant/String : Tabelle6.RangeOnlyRHS
'                               : VarVals()(1,2) : 44 : Variant/Double : Tabelle6.RangeOnlyRHS
 Let Ws.Range("B1") = VarVals()
Rem 1 ' I am not fully sure what is going on here, but I might be returning this     https://i.postimg.cc/y83k0dH3/range-...ate-Window.jpg
 Let Ws.Range("A2") = Ws.Evaluate("=IF(1=1,A1)") ' Watch : + : Ws.Evaluate("=IF(1=1,A1)") : "44"  : Variant/Object/Range : Tabelle6.RangeOnlyRHS
 Let Ws.Range("A2") = Ws.Range("=IF(1=1,A1)") '       Watch : + : Ws.Range("=IF(1=1,A1)") : "44"  : Object/Range : Tabelle6.RangeOnlyRHS
 Let Ws.Range("A2") = Ws.Range("A1")                        ' Watch : + :  Ws.Range("A1") : "44"  : Object/Range : Tabelle6.RangeOnlyRHS
 Let Ws.Range("C2") = Ws.Range("A1:B2") ' This puts nothing in the cell , and there is nothing
 '   in the Immediate window   value   place either         Watch : + : Ws.Range("A1:B2") :       : Object/Range : Tabelle6.RangeOnlyRHS         https://i.postimg.cc/BvBQ2KRn/Multi-...ate-Window.jpg
Rem 2
 Let Ws.Range("A3") = "=A1"  ' Put a formula into a cell with  VBA  or  manually and I think maybe Excel decides what allignment to give you.
 '                                                          Watch : + : Ws.Range("A3") : "44" : Object/Range : Tabelle6.RangeOnlyRHS
 Let Ws.Range("B3") = "=B1"  '                              Watch : + : Ws.Range("B3") : 44  : Object/Range : Tabelle6.RangeOnlyRHS
 Let Ws.Range("C4:D4").FormulaArray = "=A1:B1"
 Let Ws.Range("A4:B4") = "=A1:B1" ' Works because of   Interception and Implicit Intersection    https://www.excelfox.com/forum/showt...on-and-VLookUp
  
 Let Ws.Range("A5") = Ws.Range("A3") ' Watch : + : Ws.Range("A3") : "44" : Object/Range : Tabelle6.RangeOnlyRHS
End Sub

**DocAElstein** · 08-24-2019, 01:01 PM

This post https://www.excelfox.com/forum/showt...age2#post21997 Post #14

**DocAElstein** · 08-24-2019, 01:01 PM

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

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(" ")
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 wtihin the " " what you do after the = on a cell. (So perhaps its understandable that you can omit the = within the " " in Evaluate(" "), although it is accepted syntaxly: Perhaps it is then just redundant)

**DocAElstein** · 08-24-2019, 01:01 PM