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

 

[DocAElstein]

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

Hmm... and I thought I had it sussed
.....thought rng and Worksheet was an Object in the last 3 codes of mine??

 

[RoryA]

They are. To be honest, the two explanations come to the same thing really, since a variable can't be a property.

 

[DocAElstein]

They are. To be honest, the two explanations come to the same thing really, since a variable can't be a property.

Sorry to be a pain(again)....why are you saying my rng and Thisworkbook are variables in my last 3 codes

 

[RoryA]

Because they are - rng is a variable, ThisWorkbook is an auto-instancing variable, which is why you can use it as an object (like using a userform name or a sheet codename). Not really important though - your original explanation amounts to the same thing.

 

[DocAElstein]

Because they are - rng is a variable, ThisWorkbook is an auto-instancing variable, which is why you can use it as an object (like using a userform name or a sheet codename). Not really important though - your original explanation amounts to the same thing.

Ah...... think I got it - rng is a variable of a type, that is to say one with the Dimension of.. , an Object.

Subtle small points, but invaluable to grasp when learning.

.... my explanation should have been..
.... "don't work because I have
RangeObjectVariable.RangeObjectVariable. ........."

Thanks

 

[RoryA]

Yes, though as I said, it works out the same because you really can't use Object.Object - though you can use Object.PropertyThatReturnsAnObject

 

[DocAElstein]

........ though you can use Object.PropertyThatReturnsAnObject

Great.. got the explanation of why my last code works sort of "more explicitly" correct.. (I hope!)

 

[DocAElstein]

Hi,
. I hope the answer is a simple yes to this one. Then I think I understand as much as there is to Range referencing and will not come back here again!!

. The question is the correct reason, (which I think I have), to why this particular code will work and give the correct answer (that is to say gives me the value in cell S13 in worksheet “CrazyRanges” ) if placed in the Worksheet module “CrazyRanges” or any normal macro module AND regardless of wot I have as the active sheet.

[COLOR=darkblue]Sub[/COLOR] ReferencingNotVeryGoodWay()
[COLOR=darkblue]Dim[/COLOR] outputRange [COLOR=darkblue]As[/COLOR] Range
[COLOR=darkblue]Dim[/COLOR] rngvalue [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
[COLOR=darkblue]Dim[/COLOR] wks [COLOR=darkblue]As[/COLOR] Worksheet
[COLOR=darkblue]Set[/COLOR] wks = Worksheets("CrazyRanges") [COLOR=green]'(Change Sheet reference to suit your sheet)[/COLOR]
[COLOR=darkblue]Set[/COLOR] outputRange = [COLOR=red]Range[/COLOR](wks.Cells(13, 19), wks.Cells(13, 19))
[COLOR=darkblue]Let[/COLOR] rngvalue = outputRange.Value
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=green]'ReferencingNotveryGoodWay()[/COLOR]

. >> my suggested answer is: In a sheet module Range will automatically be referenced to that sheet causing a conflict with wks is a different sheet, as A Range object can only refer to cells on one worksheet. In a macro module range will take on as it were the reference to wks (not necessarily the Active sheet as I have sometimes seen as the explanation here…..that is the only reason I ask as I thought I had it all sussed but got some Google answers saying that Range would be taken here as the Active sheet. Which my experiments contradict and suggest my answer is correct! (I realize It would of course be taken as the active sheet if I did

[COLOR=darkblue]Set[/COLOR] wks = ActiveSheet

)

Thanks
Alan

P.s I realize the correct way to do it would be the following that I think always works regardless of where the macro is (I mean in a macro module).

[COLOR=darkblue]Sub[/COLOR] ReferencingGoodWay()
 
[COLOR=darkblue]Dim[/COLOR] outputRange [COLOR=darkblue]As[/COLOR] Range
[COLOR=darkblue]Dim[/COLOR] rngvalue [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
[COLOR=darkblue]Dim[/COLOR] wks [COLOR=darkblue]As[/COLOR] Worksheet
[COLOR=darkblue]Set[/COLOR] wks = Worksheets("CrazyRanges") [COLOR=green]'(Change Sheet reference to suit your sheet)[/COLOR]
[COLOR=darkblue]Set[/COLOR] outputRange = wks.Range(wks.Cells(13, 19), wks.Cells(13, 19))
[COLOR=darkblue]Let[/COLOR] rngvalue = outputRange.Value
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=green]'ReferencingGoodWay()[/COLOR]

 

[RoryA]

Correct. In a normal module, an unqualified Range call equates to Application.Range, not ActiveSheet.Range.

 

[DocAElstein]

Correct. In a normal module, an unqualified Range call equates to Application.Range, not ActiveSheet.Range.

Hi,
Thanks for the reply (again!)

.. Phew! great - Glad I (almost ) got it right (I think .... - so "Application" is the sort of "happening" going on at the time and this "happening" here in my example only sort of saw the wks reference, so sort of stuck with it. )

. I think (hope) I have the whole range referencing stuff sorted now (at least enough to satisfy my over greedy lust to learn). It was a great learning experience here.
. Thanks again
Alan.