Results 1 to 9 of 9

Difference between Workbooks(1) and ThisWorkbook ?

This is a discussion on Difference between Workbooks(1) and ThisWorkbook ? within the Excel Questions forums, part of the Question Forums category; In VBA , we have commands Workbooks("aBook"), ActiveWorkbook, Workbooks(1), ThisWorkbook. What are the differences between them if Windows("aSheet") is the ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77

    Default

    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.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77

    Default

    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?

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77

    Default

    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.

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77

    Default

    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?

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582

    Default

    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.

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77

    Default

    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

  9. #9
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582

    Default

    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.



Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com