Excel VBA function name resolution, add-in vs local module

Question

Consider the following situation. I have an Excel add-in file named 'X.xlam' with a module named 'Y' containing a function named 'Z'. The add-in file X.xlam is enabled within Excel via File > Options > Add-Ins ...

X.xlam     ; Excel add-in
`-- MODULES
    `-- Y
        `-- Z()

Now assume I open a macro-enabled Excel workbook M.xlsm that contains a module named 'A' that contains its own function named 'Z'. (I'll call this the 'local' version of function 'Z'.)

M.xlsm     ; Excel macro-enabled workbook
`-- MODULES
    `-- A
        `-- Z()   ; local function Z()

Question 1) Within workbook M.xlsm, if I invoke "=Z()" from within a worksheet cell, which function 'Z' does Excel invoke by default? Does Excel invoke function Z() in X.xlam, or function Z() in M.xlsm? (If someone can point me to a document that does a good job of describing name scope and name resolution within Excel VBA, that'd be appreciated.)

Question 2) When invoking a VBA macro/function within a worksheet cell, e.g., "=Z()", is there a scope-resolution syntax I can use to explicitly invoke one or the other function 'Z' (i.e., the "local" Z or the add-in "Z")?

Question 3) Within the macro-enabled workbook M.xlsm, when a worksheet cell invokes function '=Z()' Excel invokes the add-in version of 'Z' by default. Within file M.xlsm, if I "break the link" to the add-in file X.xlam (Excel RIBBON bar > DATA tab > CONNECTIONS gallery > EDIT LINKS button), this breaks all of the worksheet cells that invoke '=Z()' => '#NAME?'. Is there any way to have Excel break the link to function Z() within X.xlam and instead invoke Z() within M.xlsm without breaking all of the worksheet cells that invoke '=Z()'?

Answer 1

Answer 2) Given the macro-enabled worksheet's file name is 'k.xlsm', function name resolution can be enforced within a worksheet cell by prefixing the function invocation '=Z()' with the file name (or full path to) of the macro-enabled workbook:

='k.xlsm'!Z()

---

:: EDIT 2::

If a function named Z() is defined within multiple modules within the same Excel file, the syntax for disambiguation is

MODULE_NAME.FUNCTION_NAME
-or-
'FILE_NAME'!MODULE_NAME.FUNCTION_NAME    ; fully-qualified

EXAMPLE

k.xlsm
`-- MODULES
    |-- A
    |   `-- Z()   ; returns "Hello"
    `-- B
        `-- Z()   ; returns "World"

Within k.xlsm's worksheet cells:

=A.Z()            ; returns "Hello"
=B.Z()            ; returns "World"
='k.xlsm'!A.Z()   ; returns "Hello"
='k.xlsm'!B.Z()   ; returns "World"

See also: Avoiding naming conflicts (Microsoft)

---

:: EDIT 1 ::

To call a macro routine_name that is defined within a macro-enabled Excel add-in file (e.g., MyAddin.xlam), ensure the add-in file is added and enabled (see note 1.1) and then see @RAK_da_Pira's answer to the question running a macro from an add-in posted to Stack Overflow. The important bit is this: within the calling workbook one must expressly enable the add-in module as an available reference; otherwise, the calling workbook cannot call the routines in the add-in module.

Call MyAddin!routine_name(args)

Note 1.1. Excel: File > Options > Add-ins > Manage: Excel Add-ins > Go...