Difference between Workbooks(1) and ThisWorkbook ?

[Andrew XJ]

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.

[Dave Hawley]

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 ) 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


[ This Message was edited by: Dave Hawley on 2002-03-12 03:34 ]

[Andrew XJ]

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?

[Andrew XJ]

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.

[Dave Hawley]

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

[Andrew XJ]

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?

[Dave Hawley]

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.

[Andrew XJ]

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

[Dave Hawley]

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.