HOWTO: Call Microsoft Excel Macros That Take Parameters
ID: Q153307
|
The information in this article applies to:
-
Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, versions 5.0, 6.0
-
Microsoft Visual Basic Standard, Professional, and Enterprise Editions, 16-bit and 32-bit, for Windows, version 4.0
-
Microsoft Visual Basic for Applications version 5.0
-
Microsoft Office 2000 Developer
-
Microsoft Excel 2000
-
Microsoft Excel 97 for Windows
SUMMARY
Using OLE Automation, you can manipulate Microsoft Excel. It is possible to
call macro procedures that are stored in Microsoft Excel Workbooks by using
the Run method of the Microsoft Excel application object. Microsoft Excel
macro procedures that take parameters cause a slight change in the syntax.
Included below is a code sample showing how to call a Microsoft Excel macro
procedure from Visual Basic.
MORE INFORMATIONStep-by-Step Example
- Start a new Project in Visual Basic. Form1 is created by default.
- Place a CommandButton on form1.
- In the General Declarations section of Form1, enter this code:
Option Explicit
Private Sub Command1_Click()
Dim oExcelApp As Object
' Create a reference to the currently running excel application
Set oExcelApp = GetObject(, "Excel.application")
' Make the Excel Application Visible.
oExcelApp.Visible = True
' Run the excel procedure
oExcelApp.run "proc", "David", 30
End Sub
- Start Microsoft Excel. Book1 is created by default.
- Add a new module to the workbook.
For Excel 5.0 and 7.0: From the Insert menu, choose Macro, and select the
Module Option. This will give you a new module sheet, Module1.
For Excel 97 and 2000: Press ALT+F11 to start the Visual Basic Editor. Click Module on the Insert menu.
- In Module1, type the following code:
Sub Proc(sParam1 As String, iParam2 As Integer)
MsgBox sParam1 & " is " & iParam2 & " Years Old"
End Sub
- Leave the workbook open in Microsoft Excel and switch to your project in Visual Basic.
- From Visual Basic, press F5 to run the project. Click the command
button, and you should see a dialog box appear with the text "David is
30 years old" in it.
REFERENCES
For more information about using GetObject, please see the following
article in the Microsoft Knowledge Base:
Q114347
OLE Automation Objects with GetObject and CreateObject
Additional query words:
Keywords : kbinterop kbAutomation kbVBp kbVBp400 kbVBp500 kbVBp600 kbGrpDSO kbOffice2000 kbExcel97 kbexcel2000
Version : WINDOWS:2000,4.0,5.0,6.0,97; :
Platform : WINDOWS
Issue type : kbhowto
|