MrExcel.com - Your One Stop for Excel Tips & Solutions

Copy VBA Module

December 04, 2001 - by

Michelle writes:

We have developed an Excel application which generates a unique Excel workbook for distribution to each of 54 brokers. I need to include several VBA macros in each of the 54 Excel workbooks that get distributed to the brokers. How can I copy a VBA module from the original Excel workbook to each new Excel workbook?

This is a great question, Michelle. It is easy to use the Sheets().Copy command to copy a worksheet to a new workbook, but it is not as easy to copy a module to the new workbook. The solution involves exporting the module once, and then importing it into each of your 54 broker workbooks as they are completed. Here is a portion of your code with the solution. 

' Delete any old stray copies of the module1
On Error Resume Next
Kill ("C:\MrXL1.bas")
On Error GoTo 0
' Export Module 1
ActiveWorkbook.VBProject.VBComponents("module1").Export ("c:\MrXL1.bas")
For x = 1 to 54
	ThisBroker = Sheets("BrokerList").range("A" & x).value
	' ...customization of plan omited for brevity
	Sheets(Array("Menu", "Plan")).Copy
	NBName = ActiveWorkbook.Name  ' new book name
	' Import Module 1 to this new book
	Application.VBE.ActiveVBProject.VBComponents.Import ("c:\MrXL1.bas")
	ActiveWorkbook.SaveAs Filename:=ThisBroker
	ActiveWorkbook.Close
Next x
Kill ("C:\MrXl1.bas")

The VBE.ActiveProject.VBComponents.Export and VBE.ActiveProject.VBComponents..Import commands take care of getting the macros copied to the new books.

In Excel 2002 and newer, you need to explicitly allow a macro to add code to a workbook. Go to Tools, Macro, Security. Click on the 2nd tab: Trusted Publishers. In the lower left, choose Trust Access to Visual Basic Project. In Excel 2007, Go to Office Icon, Excel Options, Trust Center, Trust Center Settings, Macro Settings, and choose Trust access to the VBA project object model.

Caution

The commands to export and import modules, as well as the commands to type new macros in an existing workbook are collectively known as Visual Basic Extensibility or VBE. If your workbook uses any VBE commands, there a high chance that the workbook will be flagged by virus scanners. This is a false positive. The name of the alleged virus almost always has "heuristic" in the name. This means that the scanner is seeing DLL calls that seem suspicious, but that don't necessarily match any known viruses. You can usually write to the manufacturer of the anti-virus software and apply to have your Excel workbook white-listed after explaining what you are doing it and why it is a good thing to do.