Fully qualify a range / Excel object model / Still not getting this right

[andydtaylor]

Hi,

I'm trying to use best practice on a workbook whose name I know will get regularly changed as new versions are created. So I want to make sure it doesn't break and so have been sure to fully qualify my ranges, and make them workbook name change-proof.


However, I competely suck at understanding Excel's object model, and I still don't undersdtand when you should qualify things. So my 2-part query is -

1. Can anytone set me right on the best way to do the below? I have a version of this that works, but this doesn't look fully qualified to me.
2. Where's the best place to read an explanation of this qualification that's easy enough to digest so I have this down going forwards?

Thanks


Andy




Good

Range("Gen_Sum_Old_bottom_side").Copy Destination:=Sheets(start_sheet).Range("A13")

Bad # 1

Workbooks(ThisWorkbook.Name).Worksheets(start_sheet).Range("Gen_Sum_Old_bottom_side").Copy _
    Destination:=Workbooks(ThisWorkbook.Name).Worksheets("Delta_Gen").Range("A13")

Bad #2
I saw this thread and tried the following:
http://www.mrexcel.com/forum/excel-questions/304182-range-problem.html

Workbooks(ThisWorkbook.Name).Range("Gen_Sum_Old_bottom_side").Copy _
    Destination:=Worksheets("Delta_Gen").Range("A13")

 

[Jerry Sullivan]

Hi Andy,

This article is fairly easy to digest...

Daily Dose of Excel » Blog Archive » Beginning VBA: Navigating the Object Model

I'm not completely following your question about the 3 examples that you gave.

I'll offer a few thoughts... please clarify if I miss the mark of what you are asking.

IMO, the key to understanding the use of fully qualified references is to ask yourself whether there could be a scenario in which Excel might interpret a reference differently than you intended. If so, you need to further qualify the reference so Excel does not misinterpret your intent.

You're correct that your first example isn't a fully qualified reference.

Range("Gen_Sum_Old_bottom_side").Copy Destination:=Sheets(start_sheet).Range("A13")

If you have two workbooks open, and run a macro with this statement, Excel will assume that you are referencing the ActiveSheet of the ActiveWorkbook (of the Active Application of Excel). If your intent is copy the cells within the Workbook that has the macro code, but the other workbook is Active, then Excel will try to copy the wrong cells.

If you qualify the reference by being explicit about which Workbook and Worksheet you are referring to, then the desired range will be copied.

Here is one way to do that...

ThisWorkbook.Sheets("MyData").Range("Gen_Sum_Old_bottom_side").Copy _
        Destination:=ThisWorkbook.Sheets(start_sheet).Range("A13")

Using a With block gives the same result and it is more efficient and readable...

With ThisWorkbook
    .Sheets("MyData").Range("Gen_Sum_Old_bottom_side").Copy _
        Destination:=.Sheets(start_sheet).Range("A13")
End With

 

[andydtaylor]

Thank you for this Jerry