Sending MACROS to Users

OK, I've been thinking about it and is seems that you can send someone an XLS file which has the VBA built in to modify a persons PERSONAL.XLS.

How would I go about doing that?

Bill,

This may work, I had a similar issue wanting to use VBA to copy a macro from one worksheet to another:

Here is the code.

'Copy the required macros over to new workbook
ThisWorkbook.VBProject.VBComponents _("Module_Name").Activate
ThisWorkbook.VBProject.VBComponents _("Update_Summary").Export ("Module_Name")

' This will save the module name to your last 'active path form there you can send to whomever
'to import the module use

ActiveWorkbook.VBProject.VBComponents.Import _("Module_Name")
Kill "Module_Name" ' Deletes macro file.

I think the BUFFET at lunch dulled my brain!

1) set focus to macro to replicate
2) "Update summary" is?
3) Import line grabs from a file? No, an open workbook?

Confused. But darn was the Generals Tso's good!

Seems like step 1) can be deleted.

NOT CRITIZING! Only trying to understand.

UPDATE SUMMARY is the workbook containing the "to be exported" macro.

VBA did not like the UnderscorE after VBcomponents.

Help...

I found that to export a module from a VBA project you first have to activate it (Line 1)

the second line will export ( save) the module to your active directory as a file with the module name with no extension.

You can then send that file to whomever.

ThisWorkbook.VBProject.VBComponents _("Module_Name").Activate
ThisWorkbook.VBProject.VBComponents _("Module_Name").Export ("Module_Name")

the last line ( in original message) will import that file as a module into an open VBAproject

hope this works for you

The underscore is just a line continuation in VBA

if the code is all on one line you can omit the _

Thank you Bruce!

The "_" did throw me off a bit but the code works

Thanks again
Bill