Try something like
Dim TempFile As String
Dim ModuleName As String
Dim FromWB As Workbook
Dim DestWB As Workbook
Set FromWB = Workbooks("YourAddInFile.xla")
Set DestWB = ThisWorkbook
ModuleName = "modConstants"
TempFile = Environ("Temp") & "\" & ModuleName & ".bas"
On Error Resume Next
Kill TempFile
On Error GoTo 0
FromWB.VBProject.VBComponents(ModuleName).Export _
Filename:=TempFile
DestWB.VBProject.VBComponents.Import _
Filename:=TempFile
On Error Resume Next
Kill TempFile
On Error GoTo 0
For modules, use ".bas" as shown in the TempFile = line of code. For
userforms, use ".frm", and for class modules, sheet modules, and
ThisWorkbook, use ".cls". This assumes that the add-in's VBProject is
not locked and that the "Allow Access To The VB Project" is enabled.
See
www.cpearson.com/Excel/VBE.aspx for much more detail.
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]