From One BASIC to Another

Below are examples of the changes you have to make to port code from one BASIC to another in order to use OLE automation. (See Chapter 2 of Developing Applications with Microsoft Office for a more complete comparison of the Microsoft BASICs).

There are four main changes to make to code from OLE automation clients when you use it in servers:

• All code used in OLE automation must be preceded by the OLE automation object.

• Enclose in square brackets ([]) keywords used in clients that conflict with those used by the server.

• VBA for Microsoft Excel version 5 has a number of features that Access Basic version 2 and Visual Basic version 3 don't support.

• WordBasic version 6 doesn't use the object-centric syntax used by Access Basic, VBA for Microsoft Excel, and Visual Basic.

Handling Features Used Only by VBA for Microsoft Excel

You can use VBA for Microsoft Excel code wholesale in Access Basic and Visual Basic unless you use a VBA feature that Access Basic or Visual Basic doesn't support, such as For Each... Next, With... End With, named arguments, and variant data types containing arrays. For example, you can generate the following code using Microsoft Excel's macro recorder. (This code creates a new workbook, titles the first sheet "Sales Summary," creates column headings labeled from 1992 through 1997, and then activates the first cell in which users should type information).

Sub NewXLBook()
  Workbooks.Add
  ActiveCell.FormulaR1C1 = "Sales Summary"
  Range("B3").Select
  ActiveCell.FormulaR1C1 = "1992"
  Selection.AutoFill Destination:=Range("B3:G3"),     Type:=xlFillSeries
  Range("B3:G3").Select
  Range("A4").Select
End Sub 

To run this code under Access Basic version 2 or Visual Basic version 3, you have to make four changes:

• Precede each command with the Microsoft Excel object that the OLE automation code creates.

• Rewrite the AutoFill method to use positional arguments rather than named arguments.

• Rewrite the Range argument for the AutoFill method to reference the OLE automation object (in this case, objXL).

• Rewrite the Type argument for the AutoFill method and substitute the numeric value (2) for the xlFillSeries constant. One way to get the value for a Microsoft Excel constant is to use this line of code: MsgBox str$(NameOfConstant).

The following code shows the previous example rewritten for use in an Access Basic or Visual Basic OLE automation procedure. (See the section titled Creating Objects presented later for more information on using Microsoft Excel as an OLE automation server.) Note that since the last line of code sets the Microsoft Excel object's Visible property to True, this function leaves you in Microsoft Excel.

Function NewXLBook ()
  Dim objXL As Object
  Set objXL = CreateObject("Excel.Application")
  objXL.Workbooks.Add
  objXL.ActiveCell.FormulaR1C1 = "Sales Summary"
  objXL.Range("B3").Select
  objXL.ActiveCell.FormulaR1C1 = "1992"
  objXL.Selection.AutoFill objXL.Range("B3:G3"), 2
  objXL.Range("B3:G3").Select
  objXL.Range("A4").Select
  objXL.Visible = True
End Function

Handling WordBasic Code From the Other BASICs

WordBasic doesn't have the object-centric syntax used by Microsoft's other BASICs. However, under OLE automation, you treat WordBasic statements and functions as if they use object-centric syntax. This means that you precede WordBasic statements with a reference to the OLE automation object and a dot (.). For example, you can generate the following code using Word's macro recorder. This code creates a new document based on the Normal template, inserts the words "Sales Summary," styles these words as Heading 1, and then inserts a paragraph where users can start typing.

Sub MAIN
  FileNew .Template = "Normal", .NewTemplate = 0
  Insert "Sales Summary"
  Style "Heading 1"
  InsertPara
End Sub

To run this code under Access Basic, VBA for Microsoft Excel, or Visual Basic, you have to make two changes (See the section titled Word as an OLE Automation Server presented later for more information on using Word as an OLE automation server):

• Precede each command with the Word object that the OLE automation code creates. By doing this, you treat WordBasic statements as methods of the WordBasic object.

• Rewrite the FileNew statement to use positional arguments. This means dropping references to the arguments .Template = and .NewTemplate = and ensuring that each argument is positioned correctly with commas marking unused arguments. (See the section titled "Named vs. Positional Arguments" in Chapter 2 of Developing Applications with Microsoft Office for more information.) The best way of getting the correct position for arguments is to use the Macro Recorder. (The positions listed in the WordBasic Help file aren't always correct.)

The following code shows the previous example rewritten for use in an OLE automation procedure in Access Basic, VBA for Microsoft Excel, or Visual Basic. Note that since objWord is a global variable, this function leaves you in Word.

Function MakeWordObject ()
  Set objWord = CreateObject("Word.Basic")
  objWord.FileNew "Normal", 0
  objWord.Insert "Sales Summary"
  objWord.Style "Heading 1"
  objWord.InsertPara
End Function