Making a workbook portable

[JenniferMurphy]

If I want to send a friend a workbook that uses UDFs in my personal add-in module, but I don't want to sent the entire add-in module, do I just copy the UDFs it needs to a code module in that workbook?

Is that code module the one named "ThisWorkbook" or do I need to create one?

Thanks

 

[Joe4]

The "ThisWorkbook" and sheet modules are for Event Procedure VBA code, which is code that is automatically triggered.
Don't put it there. Insert a new module, and place your code there, and it will be accessible from all sheets without issue.

 

[JenniferMurphy]

The "ThisWorkbook" and sheet modules are for Event Procedure VBA code, which is code that is automatically triggered.
Don't put it there. Insert a new module, and place your code there, and it will be accessible from all sheets without issue.

Perfect, thanks.

Is there a way that I can disable my add-in module so that I can test that the stand-alone workbook is working (ie, that I found all of the UDFs it needs)?

 

[Joe4]

I am no expert on add-ins, but you may need to remove it, and then add it back in.
Or maybe see if there is another computer that you can test it on (one that does not have the add-in).

 

[JenniferMurphy]

I am no expert on add-ins, but you may need to remove it, and then add it back in.
Or maybe see if there is another computer that you can test it on (one that does not have the add-in).

OK, thanks, Maybe someone else will chime in.

 

[Jaafar Tribak]

OK, thanks, Maybe someone else will chime in.

Maybe you can go to the addin module, temporarly comment out the code in it and then test the stand-alone workbook

 

[JenniferMurphy]

OK. I found some more information, but still can't get it to work.

This link has instructions for disabling and enabling add-ins.

https://support.office.com/en-ie/ar...fa9-9b88-403625a0b460?ui=en-US&rs=en-IE&ad=IE

That part works. If I disable my add-in, none of the functions inside work. If I re-enable it, they work again.

I created a code module (Module1) and copied several UDFs from my add-in to that module. I had to do that while the add-in was enabled or it would disappear from the IDE.

When I tried to save the workbook, got an error message saying that I needed to save it as a macro-enabled workbook. When I gave the wrong reply, it deleted all the code form Module1. So I had to repeat the process. (Grrrr...)

I then disabled my add-in, but all of the UDF calls got a #NAME error. When I looked at one of the cells, the simple call (=xyz(A1, B2)) was had the full path to the add-id prefixed (something like 'c:\aaa\bbb\...\zzz'!zyx(A1,B2)). So I had to edit each cell and remove the path.

I would like to know how to avoid that step.

But after all that, I cannot get the UDFs in the code module to execute.

What am I doing wrong?

 

[JenniferMurphy]

Maybe you can go to the addin module, temporarly comment out the code in it and then test the stand-alone workbook

I found a way to disable it without changing any code. With my fat fingers, I try not to touch anything that is working.

But I can't get the code module code to work.

 

[JenniferMurphy]

OK. I have it all working, but it's a real pain to get it set up. I hope there's a better way.

To create a portable workbook that uses code from my personal add-in,

1. Make a copy of the workbook.
2. Save it as a macro-enabled workbook (.xlsm).
3. Add a code module (Module1).
4. Copy the code from the add-in to Module1.

But the workbook continues to execute the code form the add-in. How do I get it to use the code in the code module?

I tried disabling the add-in from the Add-Ins dialog. That causes all of the function calls to get a #NAME error and it also changes the syntax of the calls from:

=getformula(C4,0)

to

='C:\Documents and Settings\Administrator\Application Data\Microsoft\AddIns\My Add-Ins.xlam'!getformula(C4,0)

If I manually edit out the path to get it back to the original form, then it executes the code form the code module. But there has to be a simpler way.

Can someone please point me in the right direction?