10

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?

enter image description here

Community
  • 1
  • 1
Mehper C. Palavuzlar
  • 10,089
  • 23
  • 56
  • 69

2 Answers2

13

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.

Anirudh Ramanathan
  • 46,179
  • 22
  • 132
  • 191
  • When I said "private module", I didn't mean that its functions and methods would be inaccessible from outside. Just that that module belongs to the WorkSheet or Workbook object. Accessibility is set for methods and subroutines only using Public/Private. – Anirudh Ramanathan Oct 18 '12 at 13:42
  • You win, my comment is unhelpful and I'm removing it... and I'll remove this one soon. – Daniel Oct 18 '12 at 13:53
  • @DanielCook No. Don't remove them. I have added this to the answer. It isn't truly a "private module", you make a valid point :) – Anirudh Ramanathan Oct 18 '12 at 13:54
  • 1
    Well, you can't unremove comments. But anyway... a [Private Module](http://msdn.microsoft.com/en-us/library/aa266185(v=vs.60).aspx) in VBA terms doesn't mean exactly what I thought it meant anyway. A module being private just means it's not accessible from "outside its project". Technically any method is accessible within the current Excel Application via [Application.Run](http://msdn.microsoft.com/en-us/library/office/ff197132.aspx) unless the method is declared as private or the module is declared as Private (which can only be done with standard modules.) – Daniel Oct 18 '12 at 14:03
  • `Option Private Module` isn't allowed in an Object module. Adding that to the answer. Thanks. – Anirudh Ramanathan Oct 18 '12 at 14:05
  • 1
    Yes, that's what I meant when I said it only applied to standard modules. Anyway, I'm upvoting now. :) – Daniel Oct 18 '12 at 14:06
  • Sorry to bother you, but could you also answer the question of what difference it makes which module you put it in? At least partial answer: Events will only be triggered as expected if the module for the event is placed within the correct object module. User Defined Functions, must be placed in a Standard Module to be used in a cell. Constants, fixed-length strings, arrays, user-defined types, and Declare statements must be placed in a Standard module if they should be accessible outside the current module, in other modules they must be used as private. – Daniel Oct 18 '12 at 14:21
  • Edit my answer if you feel it can be improved in any way :) – Anirudh Ramanathan Oct 18 '12 at 14:25
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/18234/discussion-between-daniel-cook-and-cthulhu) – Daniel Oct 18 '12 at 14:30
0

I know atleast that Modules have to be run and ThisWorkBook is used for events such as SheetChange event etc.

Common code spit out in ThisWorkBook:

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

this question is also already answered here:

http://www.pcreview.co.uk/forums/macro-module-function-sub-and-workbook-and-sheets-t980275.html

Mike Kellogg
  • 1,168
  • 5
  • 15
  • 34