Menu
What difference does it make if one runs a VBA code in "Sheets" ("Sheet1", "Sheet2", etc.), in "ThisWorkbook", and in "Modules" ("Module1" etc.)?
In other words, which one should be used in which cases?
846
Use this property to refer to the workbook that contains your macro code. ThisWorkbook is the only way to refer to an add-in workbook from inside the add-in itself.
VBA module is a “. bcf” extension file that holds the code in the visual basic editor. Each module has its own code window where you can write. You can insert a new module, delete, backup, and import it.
The difference between Sheets and WorksheetsWorksheet – the sheet with the gridlines and cells. Chart – the sheet which contains a single chart. DialogSheet – an Excel 5 dialog sheet. These are effectively defunct as they have been replaced by VBA UserForms.
Modules. Modules, like macros, are objects you can use to add functionality to your database. Whereas you create macros in Access by choosing from a list of macro actions, you write modules in the Visual Basic for Applications (VBA) programming language.
A module is a collection of similar functions and sub-routines, grouped usually in terms of their functionality.
In a module subroutine/function, Private : Functions and Sub-routines are available only within that module. Public : They can be accessed from anywhere, directly. (Another module, different macro etc) It is common practice to store utility functions in modules.
Option Private Module, which makes the module itself private can be added to the top of any standard module, but is not permitted on an object module, like ThisWorkbook, or Sheet1, etc.
ThisWorkbook is a private module of the Workbook Object. For example, Workbook_Open(), Workbook_Close() routine, reside within this module. (Workbook Object Reference)
Similarly, Sheet1, Sheet2 are private modules of the individual sheets. In them, you would put in functions specific to that sheet. Worksheet_Activate, Worksheet_Deactivate, Workbook_SheetChange are default events provided to you, so that you can handle them, within the respective private sheet modules. (Worksheet Object Reference)
As @Daniel Cook said in the comments, while ThisWorkbook and the WorkSheet's modules aren't available for direct use as subName() or functionName() outside the module, it is still possible to call them using ThisWorkbook.subName() or ThisWorkbook.functionName()
A class module is the closest you can get to OOP in VBA. They have constructors, destructors, and can be instantiated to give you class objects.
99
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
