XL5: OLE Automation Example: Running Macro in Visual Basic 3.0

ID: Q124494

5.00 5.00c WINDOWS kbinterop kbprg kbcode

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Visual Basic version 3.0, Standard and Professional Editions

SUMMARY

The following procedure demonstrates how you can use Microsoft Visual Basic version 3.0 and OLE Automation to run a Visual Basic, Applications Edition, procedure created in Microsoft Excel.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Follow the steps below to create a Visual Basic, Applications Edition, procedure and then run it from Visual Basic 3.0.

1. Type the following code into a new Microsoft Excel module sheet in a new

   workbook.

' This procedure creates a new workbook, enters some numbers in a
' worksheet, creates a chart, rotates the chart, and then closes
' Microsoft Excel.

      Sub SpinIt()

      Dim ExcelChart As Object
      Dim i As Integer

         'Create a new workbook, type in three numbers, create a chart,
         'and change the chart type to 3-D Column

         Workbooks.Add
            Range("a1").Value = 3
            Range("a2").Value = 2
            Range("a3").Value = 1
            Range("a1:a3").Select
         Set ExcelChart = Charts.Add()
         ExcelChart.Type = xl3DColumn

         'Rotate chart
         For i = 30 To 180 Step 10
            ExcelChart.Rotation = i
         Next

         'Close all open files and quit Microsoft Excel.
         Application.DisplayAlerts = False
         ActiveWorkbook.Close (False)
         Application.Quit

      End Sub

2. Save the workbook as VB3OLE.XLS in your root directory.

3. Start Visual Basic 3.0, and on a new form, create a button.

4. Double click the button and type the following code into the procedure

   window:

      Sub Command2_Click ()
          Dim ExcelApp As Object
          'Opens Excel OLE Object
          Set ExcelApp = CreateObject("excel.application")
          ExcelApp.Visible = True
          'Opens the previously saved Excel workbook and runs the macro
          ExcelApp.Workbooks.Open ("c:\vb3ole.xls")
          ExcelApp.run ("spinit")
          Set ExcelApp = Nothing
          End
      End Sub

5. Close the procedure window, and choose Start from the Run menu.

To see the rotating chart, click the button in your form.

Additional reference words: 5.00

Keywords          : kbinterop kbprg 
Version           : 5.00 5.00c
Platform          : WINDOWS

Last Reviewed: May 17, 1999