How to dynamically add and run a VBA macro from Visual Basic

Steps to build the sample

1. First, create a new text file named KbTest.bas (without the .txt extension). This is the code module that we will insert into Excel at run-time.

2. In the text file, add the following lines of code:

      Attribute VB_Name = "KbTest"
   
      ' Your Microsoft Visual Basic for Applications macro function takes 1 
      ' parameter, the sheet object that you are going to fill.
   
      Public Sub DoKbTest(oSheetToFill As Object)
         Dim i As Integer, j As Integer
         Dim sMsg As String
         For i = 1 To 100
            For j = 1 To 10
   
               sMsg = "Cell(" & Str(i) & "," & Str(j) & ")"
               oSheetToFill.Cells(i, j).Value = sMsg
            Next j
         Next i
      End Sub
   

3. Save the text file to the C:\KbTest.bas directory, then close the file.

4. Start Visual Basic and create a standard project. Form1 is created by default.

5. On the Project menu, clickReferences, and then select the appropriate type library version which allows you to use early binding to Excel.
   
   For example, select one of the following:

   • For Microsoft Office Excel 2007, select the 12.0 library.

   • For Microsoft Office Excel 2003, select the 11.0 library.

   • For Microsoft Excel 2002, select the 10.0 library.

   • For Microsoft Excel 2000, select the 9.0 library.

   • For Microsoft Excel 97, select the 8.0 library.

6. Add a button to Form1, and place the following code in the handler for the button's Click event:

      Private Sub Command1_Click()
         Dim oXL As Excel.Application
         Dim oBook As Excel.Workbook
         Dim oSheet As Excel.Worksheet
         Dim i As Integer, j As Integer
         Dim sMsg As String
   
       ' Create a new instance of Excel and make it visible.
         Set oXL = CreateObject("Excel.Application")
         oXL.Visible = True
   
       ' Add a new workbook and set a reference to Sheet1.
         Set oBook = oXL.Workbooks.Add
         Set oSheet = oBook.Sheets(1)
   
       ' Demo standard Automation from out-of-process,
       ' this routine simply fills in values of cells.
         sMsg = "Fill the sheet from out-of-process"
         MsgBox sMsg, vbInformation Or vbMsgBoxSetForeground
   
         For i = 1 To 100
            For j = 1 To 10
               sMsg = "Cell(" & Str(i) & "," & Str(j) & ")"
               oSheet.Cells(i, j).Value = sMsg
            Next j
         Next i
   
       ' You're done with the first test, now switch sheets
       ' and run the same routine via an inserted Microsoft Visual Basic 
       ' for Applications macro.
         MsgBox "Done.", vbMsgBoxSetForeground
         Set oSheet = oBook.Sheets.Add
         oSheet.Activate
   
         sMsg = "Fill the sheet from in-process"
         MsgBox sMsg, vbInformation Or vbMsgBoxSetForeground
   
       ' The Import method lets you add modules to VBA at
       ' run time. Change the file path to match the location
       ' of the text file you created in step 3.
         oXL.VBE.ActiveVBProject.VBComponents.Import "C:\KbTest.bas"
   
       ' Now run the macro, passing oSheet as the first parameter
         oXL.Run "DoKbTest", oSheet
   
       ' You're done with the second test
         MsgBox "Done.", vbMsgBoxSetForeground
   
       ' Turn instance of Excel over to end user and release
       ' any outstanding object references.
         oXL.UserControl = True
         Set oSheet = Nothing
         Set oBook = Nothing
         Set oXL = Nothing
   
      End Sub
   

7. For Excel 2002 and for later versions of Excel, you must turn on access the VBA project. To do this, use one of the following methods:

   • In Excel 2007, click the Microsoft Office Button, and then click Excel Options. Click Trust Center, and then click Trust Center Settings. Click Macro Settings, click to select the Trust Access to the VBA project object model check box, and then click OK two times.

   • In Excel 2003 and in earlier versions of Excel, point to Macro on the Tools menu, and then click Security. In the Security dialog box, click the Trusted Sources tab, and then click to select the Trust access to Visual Basic Project check box.

8. Run the Visual Basic project.