How to post a workbook with code?

[JenniferMurphy]

I have written a Sub that I would like to post with some questions. I also have a workbook that calls that Sub to demonstrate how it works and illustrate the questions.

Since attachments are not allowed, I would upload it to Dropbox and post the link. But what's the best way to do that?

I normally keep all my code in one of several personal add-ins. But those have lots of code that is irrelevant to this question and I'm not sure how to post an add-in anyway.

I could move the code to a code module in the workbook, but then it would not be available to other workbooks and I would have to change the workbook to an xlsm, which raises other problems.

Is there a simple way?

 

[Logit]

.
Preferably ... just post an example workbook with the macro in question .. so it is functional for your questions.
Leave out the irrelevant parts.

 

[JenniferMurphy]

Preferably ... just post an example workbook with the macro in question .. so it is functional for your questions.
Leave out the irrelevant parts.

Yes, of course. But where do I put the macro?

 

[Logit]

.
Inside a module ... as you would do were the workbook to be fully functional.

Or you can place it on Sheet 1.


Make certain you explain what the macro is supposed to do and what the expected outcome is after running the macro.

 

[JenniferMurphy]

I guess my original question was not complete. Let me try again.

I would like to know how to convert a workbook that uses macros and functions that are in an add-in module to one that is self-contained -- that is, one that works without the add-in. This entails, I believe, copying any functions and macros that are needed by the workbook from the add-in to a code module.

Here are the steps that I have come up with by trial and error (mostly error):

In this example, Workbook A is the original workbook which uses the add-in and Workbook B will be the new self-contained that will not use or need the add-in.

1. Make sure that the add-in module is active (see below).
   1. Click on File and then Options (Alt+f, i).
   2. Click on Add-Ins.
   3. In the Manage: box, select Excel Add-Ins.
   4. Click Go....
   5. Make sure that the checkbox next to add-in module is checked.
2. Make a copy of Workbook A and save it as Workbook B.xlsm or copy the relevant sheets from A to a new workbook and save it as Workbook B.xlsm.
3. Close Workbook A.
4. Add a code module to workbook B.
   1. Open the VBA Editor (Alt+F11).
   2. In the Project Explorer (Ctrl+F4), right click on the entry for Workbook B and select Insert | Module.
5. Copy all of the macros and functions that are used in the sheets in Workbook B from the add-in module to the code module in Workbook B. This must be done before disabling the add-in module or the code there will not be available.
6. Save the code module. Excel doesn’t always warn about unsaved code, especially in add-ins.
7. Disable the add-in module. Note that this will disable it for all workbooks, so it's best to have all other workbooks closed during this procedure.
   1. Follow the procedure above, but check the add-in entry.
8. Recalculate the entire sheet(s) (Atl+Ctrl+Shirt+F9). This will cause a #NAME? error for every call to any function in the add-in.
9. Select one of the cells with the error. In the Formula Bar, the call will be prefixed with the full path to the add-in. It will be in single quotes followed by an exclamation point (!).
10. In the Formula Bar, select the entire path string including the quotes and the exclamation point and copy it to the clipboard (Ctrl+C). That’s everything but the equal sign (=) and the function name and parameters.
11. Open up a Find and Replace dialog (Ctrl+H). The path string should already be in the Find what: field.
12. Make sure that the Replace with: field is empty (null), then do a Replace All (Alt+A). All of the #NAME? errors should disappear, replaced by correct values.
13. Reactivate the add-in module.

This procedure seems to work. Can anyone see any problems with it? Is there anything simpler?

Thanks

 

[JenniferMurphy]

PS: Maybe this thread should be moved to the general section. It's really more about Excel itself than this board.

 

[shg]

You could instead just drag all the code modules from the add0in to the workbook of interest in the VBE.

 

[JenniferMurphy]

You could instead just drag all the code modules from the add0in to the workbook of interest in the VBE.

That's an interesting alternative that I hadn't considered. Thanks for the tip.