UDFs - strange behaviour

[RatExcel]

Hello,

I've created several UDFs, they were working just fine, but after I saved and reopened the workbook all of them have been returning #NAME error just like Excel could not locate them. They were saved in standard code module. I've played a little bit with Excel trust settings, reopened the workbook again and everything was working fine. Then I changed those settings back to previous ones and UDFs are working as expected. I'm afraid that this situation may happen again when I distribute this workbook to other users. Is there any way to ensure that this UDFs will be working? Have any one of you ever experienced similar problem? Any help or tips will be appreciated

 

[Andrew Poulsom]

What was your Macro Setting. To use a UDF Macros must be enabled (unless they are in an Add-In).

 

[RatExcel]

When I started writting UDFs my macro setting was Disable all macros with notification, then I saved the workbook and got #NAME error on reopening. I changed settings to Enable all macros, saved the workbook and when reopened UDFs were working just fine. Then I changed settings back to Disable all macros with notification, saved the workbook and reopened once again - UDFs were working fine. That looks quite strange to me. So now I have Disbale all macros with notification settings turned on and UDFs are working. I have one question regarding add-in. Is there possibility to somehow embed the addin in the workbook or it has to be installed separatelly on every computer on which the workbook will be distributed?

 

[Andrew Poulsom]

An Add-In is a workbook that needs to be installed by the user.

 

[RatExcel]

So if I distribute the .xlsm workbook with module in it containing all my UDFs they will work but only if user has macros enabled, yes? Is there any way to ensure that this UDFs will be working? I know about the method using let's say splashscreen informing user that macros have to be enabled - otherwise all sheets are being hidden. But is there any other way to make sure that UDFs will be doing their jobs regardless users' settings?

 

[Andrew Poulsom]

You need to save the workbook as an Excel Add-In (xlam) and the user needs to install it.

 

[RatExcel]

Ok, thanks for your help