'----------------------------------------
'Copie un module d'un classeur à un autre
'Nécessite d'activer la référence "Microsoft Visual Basic for Applications Extensibility 5.3"
'----------------------------------------
Sub CopyModuleToWorkbook(WBSource As Workbook, WBDestination As Workbook, ModuleName As String)
Dim S As String
Dim ApplicationScreenUpdatingAtCallTime As Boolean
Dim ActiveSheetAtCallTime As Worksheet
'Valeurs initiales
ApplicationScreenUpdatingAtCallTime = Application.ScreenUpdating
Set ActiveSheetAtCallTime = ActiveSheet
With WBSource.VBProject.VBComponents(ModuleName).CodeModule
S = .Lines(1, .CountOfLines)
End With
With WBDestination.VBProject
On Error Resume Next
.VBComponents.Remove .VBComponents.Item(ModuleName)
On Error GoTo 0
.VBComponents.Add(1).Name = ModuleName
.VBComponents(ModuleName).CodeModule.AddFromString S
.VBE.ActiveWindow.Close
'Pour sortir du contexte Projet VBA et revenir sur la feuille active
Application.ScreenUpdating = False
WBSource.Activate
ActiveSheetAtCallTime.Activate
Application.ScreenUpdating = ApplicationScreenUpdatingAtCallTime
End With
End Sub
'-----------------------------
'Supprime module d'un classeur
'Nécessite d'activer la référence "Microsoft Visual Basic for Applications Extensibility 5.3"
'-----------------------------
Sub RemoveModuleFromWorkbook(WB As Workbook, ModuleName As String)
With WB.VBProject
.VBComponents.Remove .VBComponents.Item(ModuleName)
End With
End Sub