-1
  1. Initially I have 1 function: CusFun in an Add-in1
  2. I copy all code in Add-in1 into module in Excel file, save file 1.xlsm
  3. I open 1.xlsm to work, now I have 2 functions CusFun
  4. I changed the name (and only the name) of the functions in macro in module1 in 1.xlsm as CusFun_Port
  5. Now I have 2 UDF: CusFun and CusFun_Port when I work with 1.xlsm

But CusFun_Port (all of them) always return blank while CusFun work normally.

Why use macro-enabled file instead of add-in? Because I need to send the file to many users back and forth. I initially thought only I use them, using add-in save me the trouble of clicking "Enable content". With add-in, every computer use it must install it. And when the file is sent to another computer, all the custom functions in the file must be re-targeted to that computer's add-in location. My users are not proficient in any of those tasks. And those tasks takes far more time than clicking "Enable content".

What can I do to make sure that both functions in add-in and file-specific macro work?

Community
  • 1
  • 1
Tam Le
  • 360
  • 3
  • 17
  • If you have an IT Network admin, you can request to include an update to all users connected to the network. Another is have a routine that automates the copying and loading of add-in from a share source. If both are not possible, include all functions needed in the file you distribute. – L42 Apr 03 '18 at 09:16
  • Is there another way for me to "include all functions needed in the file"? I am using macro-enabled file to achieve that. Regarding what you ask, there isn't a network, this is not a corporate environment. – Tam Le Apr 03 '18 at 09:25
  • Yeah that should work except, you need to make sure that the users enables macro, otherwise that will fail. – L42 Apr 03 '18 at 09:28
  • @TamLe Just a note but might be of your interest: If it is not a corporate environment I suspect that other companies would run a macro enabled file at all. Many companies don't allow macro enabled files at all, because of security reasons. – Pᴇʜ Apr 03 '18 at 09:28
  • @L42 The problem is that when I use the function(s) includes in the macro, the result is always blank. It does not return any error. But if there is an real error (like having wrong kind of variable), the function return error as normal. – Tam Le Apr 03 '18 at 10:16
  • @Pᴇʜ Normally, you are right. But if they cannot even install an Excel add-in, they probably don't have the strongest technical ability. In this case, security is a very distance concern. – Tam Le Apr 03 '18 at 10:20

1 Answers1

2

Finally, I found it. Because I change only the name at the top of the function, it doesn't work. Because all of them are recursive, the function's name inside did not change, hence it doesn't work. There is no conflict here.

Tam Le
  • 360
  • 3
  • 17
  • Had you included a sample function code, that would've been spotted right on. But still glad you had it figured. – L42 Apr 04 '18 at 00:21
  • 1
    @L42 Very true. I did not think the problem is the code itself. I thought it was a structural problem. I thought I was helping by filtering out things that should not be concerned. Another lesson learned. – Tam Le Apr 04 '18 at 10:08