Difference between Workbooks(1) and ThisWorkbook ?

Andrew XJ

Board Regular
Joined
Feb 21, 2002
Messages
77
In VBA, we have commands Workbooks("aBook"), ActiveWorkbook, Workbooks(1), ThisWorkbook. What are the differences between them if Windows("aSheet") is the activeWindow ? Also there are commands WorkSheet("aSheet"), ActiveWorkSheet, Sheets(1),Sheets("aSheet"), What are the differences?
Thx for any explanation.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi

Workbooks("aBook.xls")
would refer to ONLY the workbook of that name.


ActiveWorkbook
Refers to the Workboook which is active (the one you see)

Workbooks(1)
Is a Index number being in the order that the open Workbooks were opened.

ThisWorkbook
Always refers to the Workbook that houses the code.


WorkSheet("aSheet")
Refers to a Worksheet by it's sheet tab name. One of the most popular ways but also the worst and unsafest way.

ActiveWorkSheet
As the name suggests

Sheets(1)
Is the Index number going from left to rigt

Sheets("aSheet")
Nearly the same a Worksheets but could also be used for another type of sheet, eg Chart Sheet

The one you haven't mentioned is the most important one and that is
Sheet1
This is a sheets CodeName and is the safest and best way to refer to a sheet (it cannot be changed) Unless deleted :eek:) You can see a Sheets CodeName in the Project Explorer (it's the one NOT in brackets).




_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training
0zGrid.BusApp.170x60.gif

This message was edited by Dave Hawley on 2002-03-12 03:34
 
Upvote 0
Thank you, Dave.
Could i ask you two more questions? One is about the scope of the variables in a VBAProject ?
I find that i can't call a function from a UserForm if the function is defined in a standard module even if the function is defined as Public.
How about the variables defined as public? Can you explain in detail? How can i make a function and a variable available for the whole projects?
 
Upvote 0
Follow up on the Workbooks(1), ActiveWorkbook:
Dave, could you give some example to show the difference between them? For the last CodeName you mention, i feel very strange since i can often see CodeName such as Sheet1, Sheet11, ..., Sheet1111111111111, but i seldom see Sheet11111111111111 in Office XP.
 
Upvote 0
Hi

To have a variable available throughout your project you must place it at the very TOP of a Standard Module like:

Public iNum as Integer

This would then retain it's value until such time as the Workbook closes, It's changed via VBA or you use the End Statement in your code (End will destroy all variables).

It would be available to both Public modules (Standard) and Private eg UserForm, Workbook, Worksheet etc.

The next level down is the Module level. This means your variable is dimensioned at the top of a Module, but without the Public Statement, eg

Dim iNum as Integer

This means iNum is available to all Procedures within the same Module and also retains it's value much the same as above.


After this is the Procedure level (most common) this is dimmed in the Procedure itself and is only available to the specific Procedure. Once the Procedure has finished the variable value is destroyed.

Hope this help
 
Upvote 0
Thx. in fact i defined the variable in Module1. Does it make any difference that i define a public variable at the very top of Module4? Same effect?
About the Auto_open(), i can put it in a standard module and also can put it in ThisWorkbook. Is it? What's the difference externally and internally?
 
Upvote 0
Hi

It makes no diff what the Module is called so long as you use "Public iNum as Integer" placed at the very top.

Auto_Open is old hat! It's only for backward compatability with pre Excel 97. You should use:

Private Sub Workbook_Open()

In the Private Module of the Workbook Object. In fact it will only work here.
 
Upvote 0
I used Auto_open() in my projects. Are you sure Workbook_open() can only workbook in private module? I once put Workbook_open() in Module1, which is not a private module? What do you mean that? I want to put Workbook_open() in ThisWorkbook. Is it ok?
Thx
 
Upvote 0
Hi

"Workbook_Open" could no doubt be used a 'standard' procedure name in a 'standard' module but it wouldn't run automatically whenever the Workbook opened. It would have to be in the Private module of the Workbook itself to do that.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top