Programmatically add macro to Excel Workbook using VBA

Insert Code Module to Workbook using Excel VBA

There are numerous instances where code/macro needs to be added to the workbook on the fly. The following code snippet adds the code from the module to the specified workbook

Function Insert_Button(ByRef oWB As Workbook)

Dim oVBP As VBProject ' VB Project Object

Dim oVBC As VBComponent ' VB Component Object

On Error GoTo Err_VBP

Set oVBP = oWB.VBProject

Set oVBC = oVBP.VBComponents.Add(vbext_ct_StdModule)

oVBC.CodeModule.AddFromFile "c:\VBADUD\Templates\MSample.bas"

oWB.Application.Run "'" & oWB.name & "'!SayHello"

oWB.Save

' -------------------

' Destroy Objects

' -------------------

Finally:

If Not oVBP Is Nothing Then Set oVBP = Nothing

' -------------------

' Error Clearer

' -------------------

Err_VBP:

If Err < > 0 Then

Err.Clear

GoTo Finally

End If

End Function

After adding the macro to the Excel workbook programmatically, the code fires the Macro – “SayHello” from the added bas module

The code uses VBProject. Ensure that the Microsoft Visual Basic for Applications Extensibility reference is loaded

Excel VBA CodeModule, Excel VBA AddFromFile, Excel VBA add Code Modules, Excel VBA VBComponents

How to add macro to workbook using VBA, Programmatically add macros to workbook, Add code from BAS module to workbook using VBA, Excel VBA add code to workbook,

Microsoft Visual Basic for Applications Extensibility reference Dialog

See also:

Add Macro to Workbook programatically using Excel VBA

Extract Procedure Names from all Modules - VBA

Insert Procedure to a Module Using VBComponents

Delete Module on the Fly using VBA

Insert User Form on the Fly

Insert Class Module on the Fly