Can UDF's be made available globally?

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • In my previous thread:

    I was delighted to find and use the UDF but I would like very much to be able to call on this User Defined Function in any workbook/spreadsheet.

    Can this be done?

    Additionally, is 'User defined' supposed to be available in Excel 2K? I am presently on a machine that has Excel 97 adn it is there but on my work's Excel 2K I did not see it within 'Paste Function'.

  • You could place the UDF in Personnal.xls as long as it is open it will be available to all Workbooks.

    Make sure it is a Public Function
    not Private Function.

    Dont have Option Private Module in declarations.

    I save as an add-in (.xla) for this, you can save the workbook anywhere just place a shortcut to it in your XLStart directory and it will open with Excel.

    If you want stand alone workbooks to give to others its probably best to copy it to those workbooks.

  • Hi in line with the suggestions above I tend to dislike Personal.xls at work, on network or imager / remote installations and data drive swooping, and line stripping read write profiles ad all the rest Personal.xls is not so good [sorry extremely technical scenarios listed, but just for the technical readers to enjoy]

    I would as has been suggested looking at a location to store XLA files and have this access unrestricted to read access but locked to write access, and all that needs to be done is load the XLA in the correct fashion and this add in will load as Excel fires on all work stations via network access.

    The same scenario will use this utilisation of XLA on a local install / work station just the same, a good habit to get into for the future.

    My vote ? XLA.

    BTW its worth adding if the administrator is cleaver the out of hours update can be done and just a reload can be made for the updates if the case is necessary.

    Just as you can rename old XLA and name new XLA to link with current settings, nut in the passed this can fail for some odd reason.

    Hope this helps

    Jack in the UK

  • Thank you for the suggestions.

    Prior to reading them I had already saved the module within a Template .xlt and suspect it will work if in fact Excel2K has User Defined in the Paste Function dialog box.

    Even if Excel2K does not have it then I suspect I will still be able to type the formula into the formula bar and use the module from within the template.

    I will not know for sure until I try it on the my work machine with the Excel2K on Monday.

    Thanks again.

  • I am pleased to say that opening the template makes the User Defined available within the Paste Function box in Excel2K.

    Thanks to everyone for helping me.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!