HOWTO: Create and Call an Excel Macro Programmatically From VB
ID: Q194611
|
The information in this article applies to:
-
Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, versions 5.0, 6.0
-
Microsoft Office 2000 Developer
-
Microsoft Excel 2000
-
Microsoft Excel 97 for Windows
SUMMARY
This article demonstrates how you can create a Microsoft Excel VBA macro
programmatically from Microsoft Visual Basic, call it, and associate it
with a toolbar button.
MORE INFORMATION
Follow the steps below to create the sample application:
- Create a Standard EXE project in Visual Basic. Form1 is created by
default.
- Click References from the Project menu and check "Microsoft Visual
Basic for Applications Extensibility."
- Add a CommandButton to Form1.
- 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(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
- 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.
© Microsoft Corporation 1999, All Rights Reserved. Contributions by Joe Crump, Microsoft Corporation
Additional query words:
Keywords : kbActiveX kbAppToolBar kbAutomation kbCOMt kbExcel KbVBA kbVBp kbVBp500 kbVBp600 kbGrpDSO kbOffice2000 kbexcel2000
Version : WINDOWS:2000,5.0,6.0,97; :
Platform : WINDOWS
Issue type : kbhowto
|