Copying a UDF from an Add-In module to a workbook.

[Prof Wonmug]

I have several UDFs in my personal Add-In module that I would like to
copy to a workbook so I can send it to a colleague to use.

I tried copying the UDF to the ThisWorkBook module and to the Sheet1
module in the target workbook. It continued to execute from my Add-In.
I then renamed the UDF in my Add-In and then it gets a #NAME? error.

Can someone tell me what I am doing wrong?

Thanks

 

[Prof Wonmug]

I have several UDFs in my personal Add-In module that I would like to
copy to a workbook so I can send it to a colleague to use.

I tried copying the UDF to the ThisWorkBook module and to the Sheet1
module in the target workbook. It continued to execute from my Add-In.
I then renamed the UDF in my Add-In and then it gets a #NAME? error.

PS: I meant to add that when I first tried it, it complained that it
was not a macro-enabled workbook, so I resaved it as .xlsm (from
..xlsx).

Why would it be perfectly happy executing the UDF from my Add-In but
would not allow me to save in the workbook itself?

 

[Bob Phillips]

Why? UDFs have to be in a code module, not ThisWorkbook or a sheet module.

 

[Prof Wonmug]

Why? UDFs have to be in a code module, not ThisWorkbook or a sheet module.

I created a code module and copied the UDF there. I added a MsgBox
statement so I could be sure that it was the UDF in the workbook
module and not my Add-In module that was getting executed. When I
updated the cell with the call, I got the MsgBox message. All good.

I also had to change the filetype to .xlsm.

I shipped the workbook to my colleague. I gets the #NAME? error. I
checked and the module is there and the UDF code is in the module.

Now what am I doing wrong?