none
Using .xla add-in functions in VBA RRS feed

  • Question

  • I am running Excel 2010 under 64-bit Windows 7.  I have several custom functions defined in an .xla I created using VBA.  The add-in is installed, and the functions are always available from within any worksheet.  I would like to call those same functions from within VBA functions and subroutines in new workbooks without having to reproduce the source code in every new workbook.  The obvious methods - simply calling the function or calling it as a worksheet function - do not work.  Is there a way to do this so that I can distribute the .xla and allow it to be used by others without disclosing my function source code?

    Friday, December 21, 2012 12:10 AM

All replies

  • Option 1:

    Use Application.Run. Let's say you have created a function MyFunc with one numeric argument. You can then use

        Dim x
        x = Application.Run("MyFunc", 32)

    Option 2:

    Set a reference to the VB project in the add-in. This works best if you give the VB project in the add-in a unique name.

    When the add-in is loaded, you'll see the name of its VB project listed in Tools | References... below the ones that have already been set.

    Tick its check box and click OK. You can then call your function directly, prefixing it with the name of the VB project if necessary:

        Dim x
        x = MyFunc(32)

    or

        Dim x
        x = MyAddin.MyFunc(32)


    Regards, Hans Vogelaar

    Friday, December 21, 2012 12:39 AM