You are currently offline, waiting for your internet to reconnect

How To Create and Call an Excel Macro Programmatically from VB

This article was previously published under Q194611
This article demonstrates how you can create a Microsoft Excel VBA macroprogrammatically from Microsoft Visual Basic, call it, and associate itwith a toolbar button.
Follow the steps below to create the sample application:
  1. Create a Standard EXE project in Visual Basic. Form1 is created by default.
  2. Click References from the Project menu and check "Microsoft Visual Basic for Applications Extensibility."
  3. Add a CommandButton to Form1.
  4. Copy and paste the following code to the form's code window:
          Private Sub Command1_Click()       ' Start Excel       Dim xlapp As Object 'Excel.Application       Set xlapp = CreateObject("Excel.Application")       ' Make it visible...       xlapp.Visible = True       ' Add a new workbook       Dim xlbook As Object 'Excel.Workbook       Set xlbook = xlapp.Workbooks.Add       ' Add a module       Dim xlmodule As Object 'VBComponent       Set xlmodule = xlbook.VBProject.VBComponents.Add(1) 'vbext_ct_StdModule       ' Add a macro to the module...       Dim strCode As String       strCode = _          "sub MyMacro()" & vbCr & _          "   msgbox ""Inside generated macro!!!"" " & vbCr & _          "end sub"       xlmodule.CodeModule.AddFromString strCode       ' Run the new macro!       xlapp.Run "MyMacro"       ' ** Create a new toolbar with a button to fire macro...       ' Add a new toolbar...       Dim cbs As Object 'CommandBars       Dim cb As Object 'CommandBar       Set cbs = xlapp.CommandBars       Set cb = cbs.Add("MyCommandBar", 1, , True) '1=msoBarTop       cb.Visible = True       ' Make it visible & add a button...       Dim cbc As Object 'CommandBarControl       Set cbc = cb.Controls.Add(1) '1=msoControlButton       ' Assign our button to our macro       cbc.OnAction = "MyMacro"       ' Set text...       cbc.Caption = "Call MyMacro()"       ' Set Face image...       ' 51 = white hand       ' 25 = glasses       ' 34 = ink dipper       ' etc...       cbc.FaceId = 51       ' Pause so you can inspect results...       MsgBox "All done, click me to continue...", vbMsgBoxSetForeground       ' Remember to release module       Set xlmodule = Nothing       ' Clean up       xlbook.Saved = True       xlapp.Quit      End Sub						
  5. Run the application. You should see Microsoft Excel launch, followed by a message box saying "Inside generated macro!!!." At this point, you are executing code inside your generated macro. Click OK to dismiss this dialog box and you should then see a dialog box reporting "All done, click me to continue." Leave this up, and switch to Excel. There should be a new toolbar visible, with a button with a white-hand icon. The Visual Basic code above associated this button with your macro, MyMacro(), via the OnAction property. When you click this button, MyMacro() gets called. Click it once to see it work. Click back to the form in Visual Basic and click OK on the "All done, click me to continue" message box.

Additional Notes for Office XP

Office XP applications have a security option to allow programmatic access to the VBA object model. If this setting is "off" (the default), you may receive an error running the sample code. For more information about this setting and how you can correct the error, see the following article in the Microsoft Knowledge Base:
282830 PRB: Programmatic Access to Office XP VBA Project Is Denied

Article ID: 194611 - Last Review: 01/23/2007 18:12:51 - Revision: 4.4

  • Microsoft Excel 2000 Standard Edition
  • Microsoft Visual Basic 5.0 Learning Edition
  • Microsoft Visual Basic 6.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 Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Office XP Developer Edition
  • Microsoft Office 2000 Developer Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • kbautomation kbhowto KB194611