Search code examples
worksheet-functionvba

Excel cell formula scope rules for selecting VBA modules

Suppose I have a macro-enabled Excel workbook Q.xlsm containing two user-defined modules A and B, and each module has a public function named xyz() where A::xyz() returns a STRING object, and B::xyz() returns an INTEGER value.

Q.xlsm
`-- Modules
    |-- A
    |   `-- Public Function xyz() As String
    `-- B
        `-- Public Function xyz() As Integer

Q1) What is the worksheet cell formula scoping syntax that allows me to invoke either A::xyz() or B::xyz()?

Another case would be two macro-enabled workbooks P.xlsm and Q.xlsm, each with a user-defined module A having a public function xyz(). Suppose workbook P is saved as an add-in module P.xlam, and that this add-in is in fact "added in" to Excel (e.g., File > Options... > Add-Ins > ...). Now when I open workbook Q.xlsm I have two public functions P.xlam::A::xyz() and Q::A::xyz(),

P.xlam (add-in)
|-- Modules
|   `-- A
|       `-- Public Function xyz() As String
Q.xlsm
`-- Modules
    `-- A
        `-- Public Function xyz() As Integer

Q2) If I enter =xyz() into a worksheet cell, how do I know which function is invoked (i.e., what are the scope rules for VBA function names within worksheet cells)?

Q3) What syntax do I use in the worksheet cell formula to invoke either P.xlam::A::xyz() or Q.xlsm::A::xyz()?

Solution

  • Q1) What is the worksheet cell formula scoping syntax that allows me to invoke either A::xyz() or B::xyz()?

    A1) The worksheet cell formula scoping syntax seems to be MODULE_NAME.FUNCTION_NAME(), e.g.,

    =A.xyz()   // Returns the String object
=B.xyz()   // Returns the Integer value


    Q2) If I enter =xyz() into a worksheet cell [in workbook Q.xlsm] , how do I know which function is invoked (i.e., what are the scope rules for VBA function names within worksheet cells)?

    A2) After some testing with Excel 2016, invoking =xyz() within a worksheet cell in workbook Q.xlsm invokes the add-in version P.xlam!A.xyz() and not Q.xlsm!A.xyz().


    Q3) What syntax do I use in the worksheet cell formula [in workbook Q.xlsm] to invoke either P.xlam::A::xyz() or Q.xlsm::A::xyz()?

    A3) The scoping syntax seems to be WORKBOOK_FILE_NAME!MODULE_NAME.FUNCTION_NAME(),

    =P.xlam!A.xyz()     // returns STRING, becomes `=A.xyz()`
=Q.xlsm!A.xyz()     // returns INTEGER

    If function xyz() is not overloaded within any other modules within the workbook Q.xlsm, then the shorthand scoping syntax WORKBOOK_FILE_NAME!FUNCTION_NAME() also works, e.g.,

    =P.xlam!xyz()       // returns STRING, becomes `=xyz()`
=Q.xlsm!xyz()       // returns INTEGER

    EDIT 2017-11-27

    Another useful scope syntax that can be used in cell formulas:

    '[workbook_file_path]worksheet_name'!range_within_worksheet_name

    Examples:

    '[C:\Temp\Workbook1.xlsx]Scores'!$B$10
'[Workbook2.xlsx]Roster'!Teams

    If anyone knows of a built-in, shortcut syntax that describes "this workbook's full path" and is suitable for use as the "[workbook_file_path]" field, please comment. (n.b., I'm not talking about creating an INDIRECT() kludge that uses =CELL("filename",A1) or some variant thereof.)