HOWTO: Call Microsoft Excel Macros that Take Parameters

• Standard, Professional, and Enterprise Editions of Microsoft Visual Basic for Windows, 16-bit and 32-bit, version 4.0

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 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. From the Insert menu, choose Macro, and select the Module Option. This will give you a new module sheet, Module1.

6. In Module1, type the following code:

         Sub Proc(sParam1 As String, iParam2 As Integer)
           MsgBox sParam1 & " is " & iParam2 & " Years Old"
         End Sub
   
   

7. 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:

   ARTICLE-ID: Q114347
   TITLE     : OLE Automation Objects with GetObject and CreateObject

Keywords          : VB4ALL VB4WIN vbwin GnrlVb kbprg
Technology        : kbvba
Version           : WINDOWS:4.0
Platform          : NT WINDOWS
Issue type        : kbhowto