Partial qualifying references

[Aussiebear]

Open discussion regarding the Pro's and Con's of partial qualifying Excels' Object references. Whilst this could apply to all of Microsoft's suite of programs, for the moment I'd like to focus on Excel

Since we know that Excel consists of a number of objects, when is it appropriate to use the shortened (partial qualification) and when should you not? Obviously when one is new or relatively inexperienced in VBA, one should try to use full qualification wherever possible.

Full Qualification

Application.Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("D1").Activate

An alternative to Full Qualification could be

Partial Qualification 1

Application.ThisWorkbook(Book1").Worksheets(Sheet1").Range("D1").Activate

Assuming that Object is Excel perhaps this could apply

Partial Qualification 2

Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("D1").Activate

If we know that Book1.xlsm is the active workbook, then

Partial Qualification 3

Worksheets(Sheet1").Range("D1").Activate

If we know that Sheet1 is the active worksheet then

Partial Qualification 4

Range("D1").Activate

Anyone care to comment any further?

[Aflatoon]

None of those will work unless the specified sheet is the active sheet. (Partial qualification 1 won't ever work as it's invalid syntax)

General comment: if you mean to refer to a specific workbook and worksheet, do so explicitly - even if that's just Activesheet. In my opinion, you should never use Range (or Cells) without a qualifier, since the code may or may not work properly depending on where it is and which sheet is active.

[Paul_Hossler]

My $0.02 ---

1. PQ1 and PQ3 are missing a quote

2. While there is a (subtile) difference between .Activate and .Select for the Range object, I've never had occasion to use .Activate and I'm afraid I'd get confused since that makes the .Activate cell the ActiveCell and I never trust the ActiveCell

https://learn.microsoft.com/en-us/office/vba/api/excel.range.activate


Activates a single cell, which must be inside the current selection. To select a range of cells, use the Select (https://learn.microsoft.com/en-us/office/vba/api/excel.range.select) method.

3. The one time that I ALWAYS qualify is when there are two or more workbooks open. Too easy for me to make a coding assumtion error



Set wb1 = Workbooks("Jan.xlsx")
Set wb2 = Workbooks("Feb.xlsx")

wb2.Worksheets("Summary").Range("B2") .Value = wb1.Worksheets("Totals").Range("Z26").Value


4. I don't leave the default property blank, e.g. I add .Value to ranges, etc. -- Why? Just cuz

5. I would think that we're in Excel, the "Application. " is not required UNLESS using other applications



Set xl = Excel.Application
Set wd = Word.Application

Set wb1 = xl.Workbooks ("Book1.xlsx')

wd.ActiveDocument.Tables(1).Cell(2,1).Text = wb1.Worksheets(Sheet1").Range("A1").Valuse


6. I always qualify up to at least the worksheet, even if I have to explictly use Activesheet.Cells(1,1)

[Aussiebear]

Hmmm... I have now found where I went wrong. I had changed the .select to .activate because I'm trying not to use .select. My discussion points arose from the following example code



Sub ReferencesDemo()
' Demonstrate how to reference objects using Dot operator "."
' to navigate down the Excel Object Hierarchy


'1: Fully qualified reference
Application.Workbooks("Module 4 Lesson 2 Excel Object Model References.xlsm"). _
Worksheets("Hierarchy").Range("B18").Select

'2: Alternative with "ThisWorkbook"
' "ThisWorkbook" always returns the workbook
' in which the code is running (this one)
Application.ThisWorkbook.Worksheets("Hierarchy").Range("C18").Select


'3: Partially qualified reference v1
' The application object is always assumed to be Excel
Workbooks("Module 4 Lesson 2 Excel Object Model References.xlsm").Worksheets("Hierarchy").Range("D18").Select

'4: Partially qualified reference v2
' If we know that "M4L2-Excel-Object-Model-References.xlsm" is the active workbook we can omit that reference
Worksheets("Hierarchy").Range("E18").Select

'5: Partially qualified reference v3
' If we know that "Hierarchy" is the active worksheet then ... you guessed it ... we can omit that reference
Range("F18").Select
End Sub

I changed the Names of the Workbook and Worksheet to obscure the initial reference but this should not matter.

@Aflatoon is it still your opinion that Partial Qualification 2 is invalid?

[Aflatoon]

I never said PQ2 was invalid? I said PQ1 was, as written.

[Paul_Hossler]

I like to see other peoples' techniques, as well as comments on any of mine

[Aussiebear]

Okay my drive on the discussion is now aimed at, just how far back up the parent tree does one need to go to be relatively secure when coding? As you have indicated Paul, you like to bring it up to the worksheet level. All programmers (typical humans) tend to get a little lazy and shorten up code structures over time. Are there times when you can get away with shortening up the qualifying references?

[Aflatoon]

In a worksheet code module, I can rarely be bothered to type Me.Range when referring to ranges on that sheet for example, so I do tend to just use Range for that as it works the same - but I always make sure to check any code that I move to/from a worksheet code module.

For a specific worksheet, I'd tend to use a variable set using workbook.worksheet syntax. I've never felt the need to go back up to the Application level unless I'm automating another instance.

[Paul_Hossler]

I've had to debug too many "WTF happened?" because someone assumed that Sheet1 was the active sheet, but the user (can never trust them) switched to Sheet2 to look at something; so I always include the WS parent.

If there is absolutely no possibility of ambiguity (e.g.Aflatoon's WS code module example) I might forgo the Me. reference, but typically don't. I'd rather type too much by habit instead of too little and then have to spend time and effort debugging and fixing

Someone called it "Defensive Programming"

For the same reason, I prefer to use worksheet code names for sheets that won't get changed (e.g. Lookup values, config parameters, etc.) so that if the user (still can't trust them) renames the WS, the code still works. However, if I think the user is less experienced, I'll leave it as Worksheets("Data").Cells(1,1).Value and not Data.Cells(1,1).Value since they are most likely more familar with the former systax.

Finally, FWIW the only time I've ever seen Application specified was as a property to Excel, i.e.



Dim xl1 As Object, xl2 As Object

Set xl1 = Excel.Application
Set xl2 = Excel.Application