A1:B2 Application.Range reference bit quirky...

[Doc.AElstein]

A1:B2 type Application.Range referencing don’t quite work like U might think..

Hello
I sort of figured out what I was having a problem with whilst preparing this post to ask for help..
So I thought I would post anyway, for future reference .

I got some strange results whilst trying to explain to someone all about VBA Range Referencing, in particular about Application.Range(“ “) type range referencing.

The crux of the problem was I have ( wrongly as it turns out ) thought for a long time, that I was being very explicit when using this sort of Range Referencing.
Application.Range("='C:\Folder\[Book1.xlsm]Sht2'!A2:B2")

In fact, I was fooling myself, as mostly that works. But actually, as I just realised, it does not always work, and to do it properly, you need to do this sort of thing:
Application.Range("='C:\Folder\[Book1.xlsm]Sht2'!A2:'C:\Folder\[Book1.xlsm]Sht2'!B2")

In other words , don’t be fooled as I was, into thinking you are being very explicit with this
Application.Range("='C:\Folder\[Book1.xlsm]Sht2'!A2:B2")

But it is very easy to make the mistake of thinking you are being very explicit, as I will try to explain…..


There is a demo code below, but in words the summary conclusions are
_ (i) when I , ( and probably a lot of other people ) , thought they were being very explicit in their range referencing, then in fact, I ( and they ) weren’t quite doing it right, and by chance they did not notice…
and
_ something in Range referencing for a contiguous area via a
Range("Top left:Bottom right")
cell pair type reference is a bit quirky, … and I doubt anyone can really explain what is going on.. _

_.. in words this is what happens

‘ _ .. If you do not want Application Range to rely on taking the active worksheet, then the top left must be explicitly referenced. – this first bit is what most people would think. .. the next bits are not all as you might expect..

_ (ii) .. If Top left is not a named range, then you can be un explicit with the Bottom right , and the worksheet referenced by Top left will be taken also for the bottom right – this second bit you probably never thought about. The next bit is the strange bit, which caught me out, and made me realise that up until now, I had not quite been doing it as explicitly as I had thought…

_ (iii) .. If Top left is a named range , then you must be explicit with the bottom right

I don’t find either ' _ (ii) or ' _ (iii) taken on their own strange. What I do find strange is that the behaviour should be different in the two cases, in terms of what you need to do with the bottom right reference.

Excel VBA is defaulting differently in the two cases. I doubt anyone can explain why that should be. I doubt many people ever noticed.. ( I doubt many people care either :) )




_._______________

The demo macro below can be put in any code module. The results will be the same . The important part is section Rem 4 , which demos the points _ (ii) and _ (iii)
I get similar results in Excel 2003, 2007, and 2010 – ( there is a small anomaly whereby I must be a bit more explicit in Excel 2003. I have not quite figured out why yet… )

Code: Select all

Option Explicit
Sub ApplicationRangeReferrencingTopLeftColonBottomRight() ' like  Range("A1:B2") type referencing
Rem 1 worksheets info
Dim Ws1 As Worksheet, Ws2 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets.Item(1): Set Ws2 = ThisWorkbook.Worksheets.Item(2)
 Let Ws1.Name = "Sht1": Let Ws2.Name = "Sht2"
 Ws2.Cells.Clear: Ws1.Cells.Clear
Rem 2 Assign a named range object to the worksheets Names collection object of second worksheet
    If Ws2.Names.Count = 0 Then Ws2.Names.Add Name:="NameWkSht2Scoped", RefersTo:=Ws2.Range("A2")
Rem 3 Assign a named range object to the workbooks Names collection object of this workbook
    If ThisWorkbook.Names.Count < 2 Then ThisWorkbook.Names.Add Name:="NameWkBkScoped", RefersTo:=Ws2.Range("A2")
Rem 4 use Application Range object to refer to ranges  '
'4a) These variations will always work to referrence the second worksheet, regardless of what worksheet is active
 Ws1.Activate
 Ws2.Cells.Clear
 Let Application.Range("=Sht2!A2:Sht2!B2").Value = "A2 - B2" ' _ (i) Truly very explicit
 Ws2.Cells.Clear
 Let Application.Range("='" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" & "Sht2'!A2:'" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" & "Sht2'!B2").Value = "A2 - B2"   ' _ (i) Truly very very explicit
 Ws2.Cells.Clear
 Let Application.Range("=Sht2!A2:B2").Value = "A2 - B2" ' _ (ii) ' This does NOT rely on Sht2 being active. -----*****
 Ws2.Cells.Clear
 Let Application.Range("=Sht2!NameWkSht2Scoped:Sht2!B2").Value = "A2 - B2" ' _ (iii)a  For Excel 2007 and 2010
 ' Let Application.Range("=[ApplicationRangeBugFeature.xls]Sht2!NameWkSht2Scoped:[ApplicationRangeBugFeature.xls]Sht2!B2").Value = "A2 - B2" ' _ (iii)a  For Excel 2003
 Ws2.Cells.Clear
 Let Application.Range("=Sht2!NameWkBkScoped:Sht2!B2").Value = "A2 - B2" ' _ (iii)b

'4b) These variations will only work to referrence the second worksheet if the second worksheet is active
 Ws2.Activate ' _ - If you remove this code line, then _ (i) will reference Sht1 , and the other 3 range referrencing code lines will error.
 Ws2.Cells.Clear
 Let Application.Range("=A2:B2").Value = "A2 - B2" ' _ (i) very unexplicit - as we expect it is re3ferrencing the active worksheet
 Ws2.Cells.Clear
 Let Application.Range("=A2:Sht2!B2").Value = "A2 - B2" '       '  This DOES rely on Sht2 being active.      -----*****
 Ws2.Cells.Clear
 Let Application.Range("=Sht2!NameWkSht2Scoped:B2").Value = "A2 - B2" ' _ (iii)a
 Ws2.Cells.Clear
 Let Application.Range("=NameWkBkScoped:B2").Value = "A2 - B2" ' _ (iii)b
End Sub

[HansV]

I never refer to ranges this way. I always use

Range("A1:B2") if I want to refer to A1:B2 on the active sheet.
Worksheets("Sheet1").Range("A1:B2") if I want to refer to range A1:B2 on Sheet1 in the active workbook, regardless of whether Sheet1 is the active sheet.
Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1:B2") if I want to refer to range A1:B2 on Sheet1 in Book1.xlsm, regardless of whether Book1.xlsm is the active workbook or not.

In reality, I most often define and set variables referring to the worksheet and workbook:

Code: Select all

    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim rng As Range
    Set wbk = Workbooks("Book1.xlsm")
    Set wsh = wbk.Worksheets("Sheet1")
    Set rng = wsh.Range("A1:B2")

[Doc.AElstein]

I sometimes do it in those alternative ways also.

But I often reference closed workbooks as a convenient way to get at values in cells, so I already have some of those long strings already in a variable, like
strClsdRef = ”='C:\Folder\[Book1.xlsm]Sht2'!A2”

So then it is convenient sometimes to do like
Dim rng As Range
Set Rng=Application.Range(strClsdRef)

It never seems to do any harm to use the full reference.
Sometimes it saves on a few variables.

I can’t say I have a strong preference one way or the other.

With the exception of Range("A1:B2") used in a normal code module, which defaults in a normal code module to Application Range(„ „) , your ways are using the Range Property applied to an object.

I am using A Range object, in some sort of direct reference, I think.. maybe... sort of...

There might be in some situations a particular advantage of using one or the other. What I mean is there may be some subtle difference in the way one or the other works that might have some effect in a specific application. I don’t know enough about the deep down workings of VBA to comment on the pros and cons.


Clearly there are some weird things going on, resulting in the strange quirks I reported…