VBA ActiveWorkbook vs. Set wb = ActiveWorkbook

[Shiseiji]

I don't think this a question specific to single script, but rather a general "Why?" is this so question. I can write code that will work, but I don't know why the code I think should work, won't and I don't know the correct terms to do a deep search for an answer . . .

I've run into making the declarations (I think that's the correct term) shown below trying to keep code generic so I can use it without having to code in sheet names:

Dim ws As Worksheet
Dim wb As Workbook
'
Set ws = ActiveWorkbook
Set wb = ActiveSheet

"but"

wb.ws.Sort.SortFields.Clear

will error.

Why? Do you have to use ws =sheets("name") ?

TIA

Ron

 

[shg]

ws refers to specific worksheet in a specific workbook, so qualifying it with a workbook makes no sense. So just

ws.Sort.SortFields.Clear

 

[Shiseiji]

Thanks, and my apologies for the delay response. I must have missed hitting [Post].
And my apologies for not stating that "Activeworkbook.Activesheet.Sort.Sortfields.Clear" works, but not "wb.ws.Sort.SortFields.Clear"

From your explanation I would think that both would fail. Or that neither are necessary

I've never seen a concise explanation of when specifying the Active Workbook or worksheet is necessary, yet I see it in enough code and recalled reading that it was good though I have also read that Excel VB will always treat the workbook and sheet the code is called from as the active. So is it true then Active Workbook is required only when another workbook is going to be accessed?

Thanks again,

R

 

[RoryA]

It is always best to specify the workbook and sheet you are referring to.

As regards your wb.ws issue, you need to consider what that syntax means. The syntax you are using is Object.Property and in this case the object is wb, which you have declared as a Workbook object. A Workbook object does not have a "ws" property, which is why wb.ws fails. wb.Activesheet will work since a Workbook does have an ActiveSheet property.

ws does not generically refer to whatever sheet is active any time you use the ws variable. It refers specifically to the sheet that was active when you issued this assignment:

Set ws = ActiveSheet

and thus, as shg said, it does not need qualifying with a workbook object. Does that make sense?

 

[Shiseiji]

Thank you, yes, A hierarchical explanation is easy for me to get my mind around. I appreciate the patience.

Ron