none
Project window and code/macro affiliation - knewbee question. RRS feed

  • Question

  • Hi,

    I've just started to work my way through some online VBA for Excel educational material and I've encoundered this bit of information:

    To add the code window to the setup, you just have to double click on the name
    of a component in the Project window (Sheet1, Sheet2, Sheet3 or ThisWorkbook)
    and its code window appears within the gray rectangle.

    But what is the difference between those four choices?  For example, does clicking Sheet1 mean that the code/macro will only be available to Sheet1? 

    I know that it's possible to have VBA macros which are available to all .xls files.  Do you create one of those macros by editing personal.xls?  And if you are in personal.xls and click Sheet1 what does that mean?  Does it mean Sheet1 (but only Sheet1) of all files?

    Thanks,  Bob

    Monday, February 20, 2012 9:11 PM

Answers

  • "Ordinary" macros go into "standard" modules; you create a standard module by selecting Insert | Module.

    The items listed under Microsoft Excel Objects are not standard modules, but have a special purpose.

    The ThisWorkbook module is for so-called workbook-level events. For example, a procedure called Workbook_Open will be executed automatically each time the workbook is opened.

    The Sheet1 etc. modules are for workbook-level events. For example, a procedure named Worksheet_Activate will be executed automatically each time you activate the worksheet.

    You can have different "event procedures" for each sheet, hence there is a module for each sheet.

    These event procedures have a very specific, required format. To create one, select Workbook (or Worksheet) from the Object dropdown in the upper left corner of the module window, then select an event from the Procedure dropdown in the upper right corner.

    For example, code to be executed when the user changes the value of one or more cells in a worksheet has this format:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    End Sub
    If you don't use this format, the code won't work. In this example, Target will be the range consisting of all cells that have been (directly) modified by the user; you can use this to decide what to do.

    Code that should be available in all Excel workbooks can be stored in Personal.xls (or Personal.xlsb for Excel 2007 and later), but not in ThisWorkbook or Sheet1. Instead, single-click on one of these, then select Insert | Module to create a standard code module.


    Regards, Hans Vogelaar

    • Marked as answer by eBob.com Tuesday, February 21, 2012 2:41 PM
    Monday, February 20, 2012 9:38 PM
  • File can have Code into sheets modules, general module (thisworksheet), uniwersal modules, forms, classes.

    Sheets module you can see by default, because youre file hase one on start.

    More of them you can add by the Developer interface. Try to look at Menu/Insert/...

    In sheets modules you can have code working with ActiveX objects assign to this sheets (developer/formant).

    More of code (procedures and functions), usually falls to universal module.

    Form can have code too, becouse in Forms you drow objects.

    Thisis more complicatedto describein a few sentences. Take look on that.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by eBob.com Tuesday, February 21, 2012 2:41 PM
    Monday, February 20, 2012 9:58 PM
    Answerer

All replies

  • "Ordinary" macros go into "standard" modules; you create a standard module by selecting Insert | Module.

    The items listed under Microsoft Excel Objects are not standard modules, but have a special purpose.

    The ThisWorkbook module is for so-called workbook-level events. For example, a procedure called Workbook_Open will be executed automatically each time the workbook is opened.

    The Sheet1 etc. modules are for workbook-level events. For example, a procedure named Worksheet_Activate will be executed automatically each time you activate the worksheet.

    You can have different "event procedures" for each sheet, hence there is a module for each sheet.

    These event procedures have a very specific, required format. To create one, select Workbook (or Worksheet) from the Object dropdown in the upper left corner of the module window, then select an event from the Procedure dropdown in the upper right corner.

    For example, code to be executed when the user changes the value of one or more cells in a worksheet has this format:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    End Sub
    If you don't use this format, the code won't work. In this example, Target will be the range consisting of all cells that have been (directly) modified by the user; you can use this to decide what to do.

    Code that should be available in all Excel workbooks can be stored in Personal.xls (or Personal.xlsb for Excel 2007 and later), but not in ThisWorkbook or Sheet1. Instead, single-click on one of these, then select Insert | Module to create a standard code module.


    Regards, Hans Vogelaar

    • Marked as answer by eBob.com Tuesday, February 21, 2012 2:41 PM
    Monday, February 20, 2012 9:38 PM
  • File can have Code into sheets modules, general module (thisworksheet), uniwersal modules, forms, classes.

    Sheets module you can see by default, because youre file hase one on start.

    More of them you can add by the Developer interface. Try to look at Menu/Insert/...

    In sheets modules you can have code working with ActiveX objects assign to this sheets (developer/formant).

    More of code (procedures and functions), usually falls to universal module.

    Form can have code too, becouse in Forms you drow objects.

    Thisis more complicatedto describein a few sentences. Take look on that.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by eBob.com Tuesday, February 21, 2012 2:41 PM
    Monday, February 20, 2012 9:58 PM
    Answerer
  • Thank you for a great answer Hans.

    Bob

    Tuesday, February 21, 2012 2:41 PM
  • Thank you Oskar.  The referenced document looks very helpful.

    Bob

    Tuesday, February 21, 2012 2:42 PM