You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

How to call Microsoft Excel macros that take parameters

This article was previously published under Q153307
SUMMARY
Using 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 INFORMATION

Step-by-Step Example

  1. Start a new Project in Visual Basic. Form1 is created by default.
  2. Place a CommandButton on Form1.
  3. 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					
  4. Start Microsoft Excel. Book1 is created by default.
  5. 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 later: Press ALT+F11 to start the Visual Basic Editor. Click Module on the Insert menu.
  6. In Module1, type the following code:
    Sub Proc(sParam1 As String, iParam2 As Integer)        MsgBox sParam1 & " is " & iParam2 & " Years Old"      End Sub					
  7. Leave the workbook open in Microsoft Excel and switch to your project in Visual Basic.
  8. 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 Visual Basic to Automate Excel, see the following:
219151 How To Automate Microsoft Excel from Visual Basic
XL2003 XL2007
Properties

Article ID: 153307 - Last Review: 05/10/2007 18:10:28 - Revision: 5.1

  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • 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 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 Visual Basic 4.0 Standard Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 16-bit Enterprise Edition
  • Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
  • Microsoft Visual Basic for Applications 5.0
  • kbprogramming kbautomation kbhowto kbinterop KB153307
Feedback
://c1.microsoft.com/c.gif?DI=4050&did=1&t=">id=1&t="> = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> >>