You are currently offline, waiting for your internet to reconnect

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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q219905
SUMMARY
When automating an Office product from Visual Basic, it may be useful to move part of the code into a Microsoft Visual Basic for Applications (VBA) module that can run inside the process space of the server. This can boost overall execution speed for your application and help alleviate problems if the server only carries out an action when a call is made in-process.

This article demonstrates how to dynamically add a VBA module to a running Office application from Visual Basic, and then call the macro to fill a worksheet in-process.
MORE INFORMATION
The following sample demonstrates inserting a code module into Microsoft Excel, but you can use the same technique for Word and PowerPoint because both incorporate the same VBA engine.

The sample uses a static text file for the code module that is inserted into Excel. You may want to consider moving the code into a resource file that you can compile into your application, and then extract into a temporary file when needed at run time. This would make the project more manageable for re-distribution.

Starting with Microsoft Office XP, a user must grant access to the VBA object model before any Automation code written to manipulate VBA will work. This is a new security feature with Office XP. For more information, please see the following Knowledge base article:
282830 Programmatic Access to Office XP VBA Project is Denied

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.
REFERENCES
For more information on Automation of Office from Visual Basic, please see the Office Development Support site at the following address:
XL2007 OFF2007
Properties

Article ID: 219905 - Last Review: 09/23/2011 10:51:00 - Revision: 8.0

Microsoft Office Excel 2007, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Visual Basic 5.0 Learning Edition, Microsoft Visual Basic 5.0 Professional Edition, Microsoft Visual Basic 6.0 Professional Edition, Microsoft Visual Basic 5.0 Enterprise Edition, Microsoft Office Professional 2007, Microsoft Office Standard 2007, Microsoft Office Professional Edition 2003, Microsoft Office XP Developer Edition, Microsoft Office 2000 Developer Edition, Microsoft Office Excel 2003, Microsoft Excel 2010

  • kbexpertisebeginner kbautomation kbhowto KB219905
Feedback
ePV = 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" l-xs-24 ng-scope"> 香港特別行政區 - 繁體中文
El Salvador - Español
Panamá - Español
Uruguay - Español
대한민국 - 한국어
España - Español
Paraguay - Español
Venezuela - Español
0&did=1&t=">mp;did=1&t="> var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" .getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">