1

I have defined a bunch of methods in "thisWorkbook", but I cannot access them as =foo(17.0) from the spreadsheet. Searching on the interwebs suggests that for functions you want to use directly from the spreadsheet, you should create a new Module first. So, this is a two (three?) part question: The first is: how is creating a new module different from putting things in thisWorkbook. The second is: what SHOULD I put in thisWorkbook? The third is: is there any way to access the functions from thisWorkbook (or some specific sheet module from the Excel UI?

Igor Rivin
  • 4,632
  • 2
  • 23
  • 35
  • I usually use ThisWorkbook for event triggered code. – cybernetic.nomad Nov 22 '19 at 15:44
  • @cybernetic.nomad So if you have a Sub in thisWorkbook, will your functions (used by the Sub) live in modules? Do they then have to be declared Public? – Igor Rivin Nov 22 '19 at 15:50
  • ThisWorkbook like Sheet1 etc. is the class module belonging to the workbook resp. the sheet. https://stackoverflow.com/questions/12955461/what-difference-does-it-make-if-one-runs-a-vba-code-in-sheets-in-thisworkboo – Storax Nov 22 '19 at 15:51
  • As cybernetic.nomad said, keep your events in ThisWorkbook. But I'd also add that you should use modules to help organize your code. Yes, you can dump everything into one module, but using separate modules to organize your code makes it far easier to support. If I have UDFs, they ALWAYS go in their own module named "Functions". Don't go overboard on creating modules, but you should have enough that your code is organized into logical top level groupings. – Frank Ball Nov 22 '19 at 15:59

1 Answers1

5

how is creating a new module different from putting things in thisWorkbook?

ThisWorkbook is the host document. It represents the Workbook object that contains the VBA project. Like every Workbook object, it responds to Workbook events.

ThisWorkbook, along with every "SheetX" module, is a special kind of class module that VBA understands as "document modules" - these special modules can't be added or removed. The host application (here Excel) is responsible for those: To add a worksheet module, you must add a worksheet to the workbook. VBA code cannot New up a document module; you have to use the library-provided factory methods to create them (that's [ApplicationObject].Workbooks.Open, [ApplicationObject].Workbooks.Add, [WorkbookObject].Worksheets.Add).

Being objects, you can't invoke their public members without having an instance of it. ThisWorkbook defines an object that's conveniently named ThisWorkbook, so you can invoke its members by qualifying the member call with ThisWorkbook:

ThisWorkbook.SomePublicProcedure "arg1", arg2, 42

Same with worksheet modules, except these ones define an object that's named after the module's (Name) property - by default the (Name) property of sheet "Sheet1" is Sheet1, so you can qualify it like this:

Sheet1.SomePublicProcedure "arg1", arg2, 42

ThisWorkbook exposes public user procedures, plus every member inherited from Workbook; Sheet1 exposes public user procedures, plus every member inherited from Worksheet.

what SHOULD I put in thisWorkbook?

Anything that belongs to workbook-level. That is, code that affects the host document. That would be mostly event handler procedures, for example a handler for the Open event, gets invoked when the workbook is opened (with macros enabled) in Excel.

You want to have as little code as possible in any document's code-behind, so your event handlers simply invoke procedures in response to events:

Private Sub Workbook_Open()
    DoSomething
End Sub

Where DoSomething would be a Public Sub in a standard module.

is there any way to access the functions from thisWorkbook (or some specific sheet module from the Excel UI?

You don't want to do that. But you can if you want, assign a macro to a Public Sub defined in ThisWorkbook:

assign macro dialog

Note that the macro is qualified with the ThisWorkbook object name.

Normally you would want your macros defined in standard modules. Standard modules are not objects, and cannot be instantiated. A public procedure in a standard module is accessible from anywhere in the project, and unless it says Option Private Module at the top, its public members will be readily available to Excel.

If you mean "available to Excel" as "that I can use in a formula", then you mean "exposed as a UDF", and these, like macros, belong in a standard module, not ThisWorkbook.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • But are functions/subs/globals (yes, I know globals are evil :)) defined in a module by default public or private? Obviously, public is problematic, for namespace pollution reasons. – Igor Rivin Nov 22 '19 at 17:32
  • @IgorRivin in VBA, members are always `Public` unless specified otherwise (same with `ByRef` for parameters). That's why you want explicit modifiers everywhere. – Mathieu Guindon Nov 22 '19 at 17:34