CODEWITHSUNDEEP
excelvba
Igor Rivin
Igor Rivin

Reputation: 4864

VBA thisWorkbook usage

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?

Upvotes: 1

Views: 1733

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

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.

Upvotes: 5

Related Questions