none
What are Worksheet Objects considered to be when writing code? RRS feed

  • Question

  • When writing Subs, Functions, Properties, etc. I notice that it makes a difference whether you place the code in Module, Class-Module, or Worksheet Object.  Why will functions, for example, not appear as an option when you type their name on the worksheet, unless you have the code in a module, and not a worksheet object?

    Sorry if this is confusing, I just don't remember the documentation making a distinction between these items.

    Tuesday, April 9, 2019 10:45 PM

Answers

  • Worksheet modules are a special type of class modules. Procedures (Subs) in such a module aren't listed in the Macros dialog, and functions in such a module are not available as functions in a cell formula. Worksheet modules are intended for so-called event procedure that react to worksheet-related events such as activating or deactivating the worksheet, selecting a range in the worksheet, and editing the contents of cells on the worksheet.

    Similarly, the ThisWorkbook module is also a class module. It is intended for event procedures that react to workbook related events such as opening, closing and saving the workbook.

    Standard modules (the type that you create when you record a macro or by selecting Insert > Module in the Visual Basic Editor) are intended for macros, custom functions and other 'general' code.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, April 10, 2019 3:09 PM

All replies

  • Worksheet modules are a special type of class modules. Procedures (Subs) in such a module aren't listed in the Macros dialog, and functions in such a module are not available as functions in a cell formula. Worksheet modules are intended for so-called event procedure that react to worksheet-related events such as activating or deactivating the worksheet, selecting a range in the worksheet, and editing the contents of cells on the worksheet.

    Similarly, the ThisWorkbook module is also a class module. It is intended for event procedures that react to workbook related events such as opening, closing and saving the workbook.

    Standard modules (the type that you create when you record a macro or by selecting Insert > Module in the Visual Basic Editor) are intended for macros, custom functions and other 'general' code.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, April 10, 2019 3:09 PM
  • Thank you for the help!
    Sunday, April 14, 2019 10:15 PM