Nov 14 2018 02:36 PM - edited Nov 14 2018 02:37 PM
I have a few UDFs' that i have written for practice since i'm just getting into the programming portion of VBA. I have 3 Projects in my vba window. 1 for macros, 1 for my UDF, and the last one is whatever workbook i have opened.
My question is how can i get my functions to show up when typing "=". I have searched google and the only answer that i came up with is i have to attach the module that has the function into the current workbook. My functions do work and show up when using "=" but it is a little annoying to have to attach them to every workbook i create since im dealing with new and multiple workbooks everyday. I also know that the "insert function" is under the formula tab and that works as well but still an annoyance.
Just seems like there is an easier better way.
Yes the functions are public, No the module names are not the same as the function name.
Nov 14 2018 06:32 PM
Hi Katrina,
You can save the UDFs in the Personal Macro Workbook in order to make them available to all open workbooks.
For more info, please check out this link.
Hope that helps
Nov 15 2018 08:20 AM
Nov 15 2018 09:27 AM - edited Nov 15 2018 09:32 AM
Hi Katrina,
I'm sorry about that!
I forgot that this only works in the subroutines (Subs), not in the functions!
With regards to the functions, you need to create a new workbook and save the function in a module in this workbook, then you have to save this workbook as Excel Add-in (*.xlam) file extension.
After that, you need to copy this workbook into the XLSTART folder.
To locate this folder, please follow this path:
C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART
But please note that the code you attached seems to be wrong!
It's Option Explicit (Option without s).
Also, you have to remove the period (.) after the End Function!
Hope that helps
Nov 15 2018 12:42 PM
Apr 12 2019 02:33 PM
Feb 20 2020 05:45 AM - edited Feb 20 2020 05:46 AM
Hi@katrina bethea
Read this: https://stackoverflow.com/questions/22561422/excel-udf-not-appearing-in-drop-down-menu
Worked for me like a charm!
Regards
Apr 27 2020 06:46 AM
You can use Insert -> Function -> (selet a category) User Defined. The you will get list of functions defined in personal.xls or other .xla(m) file which is in xlstart directory.
Aug 30 2022 06:00 AM