none
Using a Custom Function in Multiple Workbooks - Reference Being Lost RRS feed

  • Question

  • I've written a custom function for Excel using VBA.  I need to use it across multiple workbooks and followed the instructions outlined  in the link below.  When initially creating the workbooks, it works as described.  However, when I reopen any of the workbooks, the reference to the original workbook is gone and, not surprisingly, uses of  the custom function trigger a #Name? error. 

    http://support.microsoft.com/kb/141288/en-us

    To recap what I've done:

    -- A function called "MyFunction" is coded in "Module1" of a spreadsheet called "MyFunction Template.xlsm" which shows in a project named "MyFunctionProject" in the VBA Developer window.

    -- For each additional workbook,

        -- I add a reference to MyFunction using Tools -> References, resulting in a reference to MyFunction Template.xlsm within the "VBAProject" for the new workbook as shown in the VBA Developer window.

        -- I add "=MyFunction(..)" to one or more cells in one of the worksheets, yielding the result I expect.

        -- I save the workbook (I've tried saving as both as an xlsx and an xlsm)

    When I reopen the workbook, the added reference to MyFunction/MyFunction Template.xlsm is not showing in the VBA Developer window.  The values in the cells that call MyFunction are still correct, ostensibly since MyFunction was not re-invoked on open for some reason.  As soon as I copy and paste a range of cells from another spreadsheet into a part of the worksheet that isn't accessed directly by MyFunction (but does indirectly impact cells MyFunction accesses), all the MyFunction references trigger a #Name? error, which isn't surprising since there's no longer a reference to MyFunction.  Even if I re-add the MyFunction reference at that point, I can't get the #Name? error to go away.

    It seems the key issue is that the reference I add using Tools -> References isn't being saved.  Any idea what I'm missing?

    Thx.  Steve




    • Edited by Cincy Steve Wednesday, August 27, 2014 4:54 PM
    Wednesday, August 27, 2014 4:45 PM

Answers

  • Any idea what I'm missing?
    As Jim Cone said, the workbook that contains the UDF must be open, it is not possible to execute code within a closed file.

    But there are other ways:

    a) You can copy the UDF into your PERSONAL.XLSB file

    That is the same as your way, only the file is different and the PERSONAL.XLSB file is opened automatically when Excel is opened.

    But there is an issue (with both ways), when you write a formula into a cell: Your UDF is not listed when you type the name!

    b) You can write an AddIn (that is the usual way)

    When you place the UDF into an AddIn, the UDF is listed when you enter a formula. And you did not see a file name in front of the UDF name in the function wizard window.

    But the AddIn must also be loaded, otherwise the formula in the other workbook is not calculated!

    Do as follows:

    - Make a new file, insert a regular module, place your UDF in there and save the workbook as .XLAM at a appropriate place.

    - Close and Reopen Excel, open your AddIn (double click the XLAM file), in a new file enter a formula to call the UDF, save the Workbook anywhere.

    - For a test:
      - Close and Reopen Excel, open the saved workbook.
      - When you try to recalculate the UDF, you'll see that it shows the #NAME? error.
      - Open the AddIn, as you see the UDF is called automatically and you get the result.
      - When you open the AddIn before the workbook, the UDF is calculated.

    - Click File\Options\Add-Ins\Go To\Browse and select your AddIn, click Ok and Ok. The AddIn is now loaded automatically when Excel is loaded.

    The VBA reference to the AddIn (or other files as you did) is only needed when you want to call the UDF (sub or function) directly from a macro from the workbook.

    Andreas.

    • Edited by Andreas Killer Thursday, August 28, 2014 10:02 AM
    • Marked as answer by Cincy Steve Thursday, August 28, 2014 8:53 PM
    Thursday, August 28, 2014 10:01 AM
  • Click File\Options\Add-Ins\Go To\Browse and select your AddIn, click Ok and Ok. The AddIn is now loaded automatically when Excel is loaded.

    The VBA reference to the AddIn (or other files as you did) is only needed when you want to call the UDF (sub or function) directly from a macro from the workbook.

    Andreas.


    I don't believe there is a difference between the above techniques....when you make a VBA Reference to the addin, it is essentially loaded.
    • Marked as answer by Cincy Steve Thursday, August 28, 2014 8:53 PM
    Thursday, August 28, 2014 1:06 PM
  • Re:   user designed function

    Some guidelines for a UDF...
    The function should be a Public function in a standard module (not a sheet module)
    The module must not use "Option Private Module"
    The workbook it is in must be open.
    (putting the function in an add-in helps, as add-ins are loaded when Excel starts)
    A reference to the workbook containing the function in Tools | Reference is not necessary.
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Cincy Steve Thursday, August 28, 2014 8:53 PM
    • Edited by James Cone Monday, October 31, 2016 11:13 PM
    Thursday, August 28, 2014 1:41 AM

All replies

  • Re:   user designed function

    Some guidelines for a UDF...
    The function should be a Public function in a standard module (not a sheet module)
    The module must not use "Option Private Module"
    The workbook it is in must be open.
    (putting the function in an add-in helps, as add-ins are loaded when Excel starts)
    A reference to the workbook containing the function in Tools | Reference is not necessary.
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Cincy Steve Thursday, August 28, 2014 8:53 PM
    • Edited by James Cone Monday, October 31, 2016 11:13 PM
    Thursday, August 28, 2014 1:41 AM
  • Any idea what I'm missing?
    As Jim Cone said, the workbook that contains the UDF must be open, it is not possible to execute code within a closed file.

    But there are other ways:

    a) You can copy the UDF into your PERSONAL.XLSB file

    That is the same as your way, only the file is different and the PERSONAL.XLSB file is opened automatically when Excel is opened.

    But there is an issue (with both ways), when you write a formula into a cell: Your UDF is not listed when you type the name!

    b) You can write an AddIn (that is the usual way)

    When you place the UDF into an AddIn, the UDF is listed when you enter a formula. And you did not see a file name in front of the UDF name in the function wizard window.

    But the AddIn must also be loaded, otherwise the formula in the other workbook is not calculated!

    Do as follows:

    - Make a new file, insert a regular module, place your UDF in there and save the workbook as .XLAM at a appropriate place.

    - Close and Reopen Excel, open your AddIn (double click the XLAM file), in a new file enter a formula to call the UDF, save the Workbook anywhere.

    - For a test:
      - Close and Reopen Excel, open the saved workbook.
      - When you try to recalculate the UDF, you'll see that it shows the #NAME? error.
      - Open the AddIn, as you see the UDF is called automatically and you get the result.
      - When you open the AddIn before the workbook, the UDF is calculated.

    - Click File\Options\Add-Ins\Go To\Browse and select your AddIn, click Ok and Ok. The AddIn is now loaded automatically when Excel is loaded.

    The VBA reference to the AddIn (or other files as you did) is only needed when you want to call the UDF (sub or function) directly from a macro from the workbook.

    Andreas.

    • Edited by Andreas Killer Thursday, August 28, 2014 10:02 AM
    • Marked as answer by Cincy Steve Thursday, August 28, 2014 8:53 PM
    Thursday, August 28, 2014 10:01 AM
  • Click File\Options\Add-Ins\Go To\Browse and select your AddIn, click Ok and Ok. The AddIn is now loaded automatically when Excel is loaded.

    The VBA reference to the AddIn (or other files as you did) is only needed when you want to call the UDF (sub or function) directly from a macro from the workbook.

    Andreas.


    I don't believe there is a difference between the above techniques....when you make a VBA Reference to the addin, it is essentially loaded.
    • Marked as answer by Cincy Steve Thursday, August 28, 2014 8:53 PM
    Thursday, August 28, 2014 1:06 PM
  • Thank you, Jim and Andreas.  I shutter to think how long it would have taken me to discover the steps to do this.  That includes some of the VBA specifics since I'm a C# guy, not VB.  I have made my UDF a full Add-In.  It all seems to be working as you described.  Very clean, once I followed the very clear steps you described to make it happen.

    Steve


    • Edited by Cincy Steve Thursday, August 28, 2014 8:54 PM
    Thursday, August 28, 2014 8:53 PM