Range= equivalent to Range.Value= sometimes. Range Range.Value Anomaly

[DocAElstein]

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:

[color=darkblue]Sub[/color] RangeRangeDotValueAnomalySingleCells()
 
[color=darkblue]Let[/color] Range("C1").Value = Range("A1").Value [color=green]'Works: returns value in cell A1 in Cell C1[/color]
 
[color=darkblue]Let[/color] Range("C1") = Range("A1") [color=green]'Works: returns value in cell A1 in Cell C1[/color]
 
[color=darkblue]Let[/color] Range("C1") = Range("A1").Value [color=green]'Works: returns value in cell A1 in Cell C1[/color]
 
[color=darkblue]Let[/color] Range("C1").Value = Range("A1") [color=green]'Works: returns value in cell A1 in Cell C1[/color]
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

. 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

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

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

 

[adam087]

Not sure if I can help but I'll try.

Basically if you omit to specify what you want Excel to work with there, it'll guess. And normally guesses that you want the 'Value' so that accounts for all the things it's doing there. Especially if you're using LET (or nothing) which can only apply to non-object variables, as all object variables would require SET.


If you want to explicitly tell Excel to use the range object, then you can force it, in the same way you use VALUE.

Set rngA = Range("A3:A10").Range

Does that help at all?


/AJ

 

[RoryA]

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
    vData = ActiveSheet.Range("A1:A10").Value    ' works
    vData = ws.Range("A1:A10").Value    ' works

    vData = Range("A1:A10")    ' works
    vData = ActiveSheet.Range("A1:A10")    ' gives Type Mismatch error
    vData = ws.Range("A1:A10")    ' works

End Sub

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.
Note that if you change the declaration of vData to simply:

Dim vData

all the subsequent lines work, so there is an issue with the casting in the former version.

 

[DocAElstein]

Hi, Thanks very much for the Reply

….Basically if you omit to specify what you want Excel to work with there, it'll guess. And normally guesses that you want the 'Value' so that accounts for all the things it's doing there. Especially if you're using LET (or nothing) which can only apply to non-object variables, as all object variables would require SET….

I understand those points but thanks for re-iterating them – helps me a beginner to be certain. Maybe that could explain the single cell results. But I am not too sure. It would be great if anyone really knows how VBA does these things!! Lol!

…….If you want to explicitly tell Excel to use the range object, then you can force it, in the same way you use VALUE.

Set rngA = Range("A3:A10").Range

Does that help at all?......

…I am doing exactly that in my codes in the Multiple cells example, so that is why I might expect a mismatch type error in the last 2 lines., which appears to not be the case.

. Still puzzled, but thanks anyways
Alan

 

[RoryA]

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.

 

[DocAElstein]

Hi Rory,
. Thanks for your replies. Letting them run through my head now!

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

. So in my cases are you suggesting I always include .Value to be on the safe side? (Surprisingly in codes given to me or in literature often that .Value is omitted and just Range is used. Up until now I have encountered no codes where adding .Value changes anything in those. ) So maybe a good Tip for everyone always to include .Value. Thanks.

…….. As another example:

Sub huh()
    Dim vData()
    Dim ws                    As Worksheet
    
    Set ws = ActiveSheet

    vData = Range("A1:A10").Value    ' works
    vData = ActiveSheet.Range("A1:A10").Value    ' works
    vData = ws.Range("A1:A10").Value    ' works

    vData = Range("A1:A10")    ' works
    vData = ActiveSheet.Range("A1:A10")    ' gives Type Mismatch error
    vData = ws.Range("A1:A10")    ' works

End Sub

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.
Note that if you change the declaration of vData to simply:

Dim vData

all the subsequent lines work, so there is an issue with the casting in the former version.

. The “bounding” again and now “casting” ideas! Thanks. -Need to run it through my Beginner’s Brain a bit!

. I think I can follow that all (vaguely for now) and also the extra info in #5.

From the VBA language spec, this I think is the most relevant section:

Thanks.

. I am puzzled that those two lines of mine produce an empty column rather than an error such as type mismatch. I have not seen an “empty cells” as a result spring up in any explanation yet.

. As I feared, this seemingly simple question has some tricky stuff behind it to do with exactly wot VBA is doing!
. And Microsoft are not explaining it too well Again.

Alan

 

[DocAElstein]

……….
If you want to explicitly tell Excel to use the range object, then you can force it, in the same way you use VALUE.

Set rngA = Range("A3:A10").Range

………
………………………Does that help at all?
/AJ

Hi Adam, (and anyone else looking in!)
. I am “warming up” just now, checking through some old Threads re-learning my VBA after a break…
. I noticed something in parsing which helped me start thinking again..
. The exact syntax of your suggestion does not quite work. You may have realized that and were just demonstrating the idea of being more explicit as it were and “forcing” excel to use the range object. (That was a helpful suggestion, thanks again for that)
. Anyways I was starting again to think about the ideas discussed in this thread..
……I notice that modifying your idea slightlys as follows “works” fullys

[color=darkblue]Sub[/color] AdamJamesRangeSuggestionLookingAtRangeArgument()
 
[color=green]'Looking at Range Argument[/color]
 
[color=darkblue]Dim[/color] rngA [color=darkblue]As[/color] Range, rngA2 [color=darkblue]As[/color] Range, rngA4 [color=darkblue]As[/color] Range, rngCrazyAlan [color=darkblue]As[/color] Range
[color=green]'     Set rngA = Range("A3:A10").Range  ' >>>> these two lines error with...[/color]
[color=green]'     Set rngA = Range("A3:A10").Range()'  .... "argument not optional"[/color]
[color=darkblue]Set[/color] rngA = Range("A3:A10") [color=green]'   Works[/color]
[color=darkblue]Set[/color] rngA2 = Range("A3:A10").Range("A3:A10") '    Works
[color=darkblue]Set[/color] rngA4 = Range("A3:A10").Range("A3:A10").Range("A3:A10").Range("A3:A10") [color=green]'   Works[/color]
[color=darkblue]Set[/color] rngCrazyAlan = Range("A34:A100").Range("A356:A10789").Range("A107").Range("A3:A10") '... ?..Works????
[color=darkblue]Set[/color] rngCrazyAlan = Range("A34:A100").Range("A356:A10789").Cells(107, 1).Range("A3:A10") [color=green]'... ?..Works????[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'AdamJamesRangeSuggestionLookingAtRangeArgument()[/color]

……starting to wonder wot is actually happening here. I guess you basic idea is still appropriate… The First range is the only object..and wot comes after “works” as it were on that object returning another object so sort of making doubly (or more in the further lines I included!!) that the first range is a range object. As Andrew Poulsom told me a few times now, for example here (http://www.mrexcel.com/forum/excel-questions/785490-add-new-rows-visual-basic-applications-2.html )

…….. it's important to grasp that that code uses properties to return objects. Workbooks returns a workbook object, Worksheets returns a worksheet object and Range returns a Range object. All three of those properties are read-only. …..
…..There are objects and properties that share the same word. For example the Range property returns a Range object.
….It's coincidence that their names happen to be similar to the names of the objects that they return. ………..

. Wot makes the Range a difficult one here to grasp is that he name are exactly the same. The above codes are examples of the relative referencing idea and the codes are valid as we start with the only object there which is the First Range. That gets “worked on” by the next Range property which returns a different range object. That in turn gets worked on again by the next range (or cells) property which again returns another range:

… So I went a bit “crazy” with ranging things in a spreadsheet. Just a small sample of wot I think helps to get the point across (I mean for beginner’s like me – I am sure all the Profi’s out there no it and are fed up trying to get it across to people like me!):-

. In this crude example the I break up one line of commands…

[color=darkblue]Set[/color] rngA = wks.Range("A1").Range("B14:C15").Cells(-3, 2).Range("B1:D1").Cells(0, 0).Cells(-5, -1).Cells(-1, 2)

(along with adding a few other identical ones along the way) with a few Wait commands. So if you run the crude program below it does a crazy zig zag loop around the spreadsheet demonstrating how each property returns a new range. (It may be a bit easier for anyone wanting to follow this to have The file and codes. Here it is and should come up in the relavent sheet. The macro is “RangeObject_AndRangePropertyAndCellsProperty” in Module “ForMrExcelFredCrazyRanging”
https://app.box.com/s/6j9vt55zbbnd0u805tx6

[color=darkblue]Sub[/color] RangeObject_AndRangePropertyAndCellsProperty() [color=green]'   'allo , ' allo[/color]
 
[color=green]'Lookin at "crazy" arguments in cells and Range property of a Range Object.[/color]
[color=green]'A cells or Range property with a different argument is continually applied to a[/color]
[color=green]'Range object resulting in the assigned range taking a Zig-zag "crazy" path[/color]
[color=green]'as well as resizing it along the way,[/color]
[color=green]'before eventually coming back to almost the same point.[/color]
[color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet: [color=darkblue]Set[/color] wks = ActiveSheet: wks.Cells.Clear
 
[color=darkblue]Dim[/color] rngA [color=darkblue]As[/color] Range
 
[color=darkblue]Set[/color] rngA = wks.Range("A1")
[color=darkblue]Set[/color] rngA = wks.Cells(1, 1)
[color=darkblue]Let[/color] rngA.Value = "Allo 1"
Application.Wait (Now + TimeValue("0:00:03"))
[color=darkblue]Set[/color] rngA = wks.Range("A1").Range("B14:C15")
[color=darkblue]Let[/color] rngA.Value = "Allo 2"
Application.Wait (Now + TimeValue("0:00:03"))
[color=darkblue]Set[/color] rngA = wks.Range("A1").Range("B14:C15").Cells(-3, 2)
[color=darkblue]Let[/color] rngA.Value = "Allo 3"
Application.Wait (Now + TimeValue("0:00:03"))
[color=darkblue]Set[/color] rngA = wks.Range("A1").Range("B14:C15").Cells(-3, 2).Range("B1:D1")
[color=darkblue]Let[/color] rngA.Value = "Allo 4"
Application.Wait (Now + TimeValue("0:00:03"))
[color=darkblue]Set[/color] rngA = wks.Range("A1").Range("B14:C15").Cells(-3, 2).Range(wks.Range("A1").Cells(1, 2), wks.Range("A1").Cells(1, 4))
[color=darkblue]Let[/color] rngA.Value = "Allo 4 and 5"
Application.Wait (Now + TimeValue("0:00:03"))
[color=darkblue]Set[/color] rngA = wks.Range("A1").Range("B14:C15").Cells(-3, 2).Range(wks.Cells(1, 2), wks.Cells(1, 4))
[color=darkblue]Let[/color] rngA.Value = "Allo 4,5 and 6"
Application.Wait (Now + TimeValue("0:00:03"))
[color=darkblue]Set[/color] rngA = wks.Range("A1").Range("B14:C15").Cells(-3, 2).Range("B1:D1").Cells(0, 0) [color=green]'Note property cell(0,0) takes range to 1 left and 1 up, effectively in the negative directions[/color]
[color=darkblue]Let[/color] rngA.Value = "Allo 7"
Application.Wait (Now + TimeValue("0:00:03"))
[color=darkblue]Set[/color] rngA = wks.Range("A1").Range("B14:C15").Cells(-3, 2).Range("B1:D1").Cells(0, 0).Cells(-5, -1)
[color=darkblue]Let[/color] rngA.Value = "Allo 8"
Application.Wait (Now + TimeValue("0:00:03"))
[color=darkblue]Set[/color] rngA = wks.Range("A1").Range("B14:C15").Cells(-3, 2).Range("B1:D1").Cells(0, 0).Cells(-5, -1).Cells(-1, 2)
[color=darkblue]Let[/color] rngA.Value = "Allo 9"
 
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'RangeObject_AndRangePropertyAndCellsProperty()[/color]

.. it should end up looking like this:

Book1
	A	B	C	D	E	F
1	Allo 1	Allo 9
2
3	Allo 8
4
5
6
7
8
9			Allo 7
10			Allo 3	Allo 4,5 and 6	Allo 4,5 and 6	Allo 4,5 and 6
11
12
13
14		Allo 2	Allo 2
15		Allo 2	Allo 2
CrazyRanges

. It re iterated to me that there is not a great difference between the Cells property and the Range property (at least as used here). Here they both do a similar job to the resize and offset property. I did notice / learn that you cannot really go backwards with the Range property. Also noticed that the cells property can take zeros and –ve arguments which helps distinguish it from the cells Object which must have arguments >=1. (And mote Cells(0 , 0) property takes you one space to the left and one space up***). .. to demonstrate those points..
. here the cells property will take you from the first cell to B2 then back again, putting “Allo There” into the first cell.

[color=darkblue]Set[/color] rngA = wks.Range("B2").Cells(0, 0)
[color=darkblue]Let[/color] rngA.Value = "Allo There"

. this will error as you are trying to fool the range property to backwards.

[color=darkblue]Set[/color] rngA = wks.Range("B2").Range(wks.Range("A1").Cells(0, 0), wks.Range("A1").Cells(0, 0))

. This does not error and may fool you into thinking that you went backward with the range property (as cells(0, 0) property is one left, one up***).

[color=darkblue]Set[/color] rngA = wks.Range("B2").Range(wks.Range("B2").Cells(0, 0), wks.Range("B2").Cells(0, 0))
[color=darkblue]Let[/color] rngA.Value = "Allo There"

.. But in fact the range property effectively did not go anywhere. The arguments of that second range property went from B2 to C3 then back to B2 so that “Allo There” ends up being put by the range property into

. just to complete this little transgression or regression, worth noting wot RoryA often has to remind me..

.. do not use the short cut of this

[color=darkblue]Set[/color] rngA = wks.Range("B2").Range(Range("B2").Cells(0, 0), Range("B2").Cells(0, 0))

Or this

[color=darkblue]Set[/color] rngA = wks.Range("B2").Range(Cells(0, 0), Cells(0, 0))

Instead of this

[color=darkblue]Set[/color] rngA = wks.Range("B2").Range(wks.Range("B2").Cells(0, 0), wks.Range("B2").Cells(0, 0))

.. as it may catch you out depending on where your macro may be in relation to which Worksheet you are wanting to be working in..

. Alan………


P.s. Just some further codes which I think help to distinguish in this case between the Range Property and the Range Object.. At First glance they look very similar. But The first two codes do not work, the third does..

[color=darkblue]Sub[/color] ObjectObject()
[color=darkblue]Dim[/color] rng [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
[color=darkblue]Dim[/color] Arr [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] Arr = rng.rng [color=green]'Maybe does not work as rng is object[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'ObjectObject()[/color]
 
[color=darkblue]Sub[/color] ObjectObject2()
[color=darkblue]Dim[/color] rng [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
[color=darkblue]Dim[/color] Arr [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] Arr = rng.ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6") [color=green]'Maybe does not work as Worksheets("CrazyRanges").Range("C4:E6") is object[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'ObjectObject2()[/color]
 
[color=darkblue]Sub[/color] ObjectProperty()
[color=darkblue]Dim[/color] rng [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
[color=darkblue]Dim[/color] Arr [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] Arr = rng.Range("C4:E6") [color=green]' Dorrect Syntax: RangeObject.RangeProperty[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'ObjectProperty()[/color]

……. I think the explanation is that I have RangeObject.RangeObject which is rubbish. In the third code I have

 

[RoryA]

A couple of key points:

Range("A3:A10").Range("A3:A10")

does return a range object, but it's a reference to A5:A12 because you have used the range property of a range, which returns a range relative to the top left cell of the original range.

In your last code samples, rng is a Range object. A Range object doesn't have a 'rng' property, it has a 'Range' property, which is why version one fails. (Declaring a variable as type Range doesn't mean you can simply replace the word Range anywhere in your code with the variable name). Equally, a Range object doesn't have a ThisWorkbook property.

 

[DocAElstein]

. Hi Rory, happy New Year
. I was actually just about to post a SMALL EDIT ON LAST BIT of Post # 7 THAT GOT CHOPPED OFF THAT POST……..
……
……. I think the explanation is that I have RangeObject.RangeObject which is rubbish. In the third code > I have RangeObject.RangeProperty

. So I think that ties up with wot you said about the rng…
. And the

Range("A3:A10").Range ("A3:A10")

. I was just about getting that point as I rambled with the “Relative referencing thing”. It is very helpful to me when someone that really knows for sure confirms it . Many Thanks.
.
…..

.........Equally, a Range object doesn't have a ThisWorkbook property.

. – Thanks, I was a bit off with my thinking there, naively thinking that if
rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
then these lines are equivalent
rng.ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
and
rng.rng
. I see now they both fail, but the second specifically as …..

......... a Range object doesn't have a ThisWorkbook property.

, though my Object.Object explanation is still valid, (I think?), just the second Objects are different in each case, which I overlooked.
. I have almost really got it all now.
. Thanks for your patience on this one!!!

Alan Elston

 

[RoryA]

I think the explanation is that I have RangeObject.RangeObject which is rubbish.

Not so much that as that you have variable.variable which makes no sense.