VBA ActiveWorkbook vs. Set wb = ActiveWorkbook

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
143
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
ws refers to specific worksheet in a specific workbook, so qualifying it with a workbook makes no sense. So just

Code:
ws.Sort.SortFields.Clear
 

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
143
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

Code:
Set ws = ActiveSheet

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

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
143
Thank you, yes, A hierarchical explanation is easy for me to get my mind around. I appreciate the patience.

Ron
 

Watch MrExcel Video

Forum statistics

Threads
1,123,133
Messages
5,599,910
Members
414,346
Latest member
mmoose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top