PRB: Error Message "Argument Not Optional"

• Microsoft Access 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0a, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Visual Basic Standard, Professional, and Enterprise Editions, 16-bit and 32-bit, for Windows, version 4.0

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you use Automation code in one of the products listed at the beginning of this article to manipulate an Automation server application that uses newer version type libraries, such as those found in Microsoft Office 97 products, you may receive the following error message:

   Argument not optional.

CAUSE

Versions of Visual Basic for Applications in Microsoft Visual Basic 4.0 and Microsoft Office 95 Professional only allowed optional arguments that were of type Variant. Versions of Visual Basic for Applications in Microsoft Visual Basic 5.0 and Microsoft Office 97 Professional now allow optional arguments of non-Variant types.

As an example, the Microsoft DAO 3.5 Object Library contains methods which include non-Variant optional arguments. Because this is not recognized in products using older versions of Visual Basic for Applications, these arguments must be explicitly specified. If omitted, you will receive the error, "Argument not optional."

RESOLUTION

There are two possible resolutions to this behavior:

Method 1

When you use earlier version Automation client applications, do not rely on the newer type libraries for the Automation servers. Instead, reference the older version type libraries.

For example, do not reference the Microsoft DAO 3.5 Object Library in Microsoft Visual Basic 4.0. Instead, reference the Microsoft DAO 3.0 Object Library. Similarly, if you are using Microsoft Access 7.0, create a reference to the Microsoft Excel 7.0 Object library and not the Microsoft Excel 8.0 Object library.

Method 2

Explicitly specify all arguments when you execute a method in the new type libraries. For example, when calling the Update method in the Microsoft DAO 3.5 Object Library, be sure to include values for its two arguments. The line of code will look similar to the following:

   MyRec.Update dbUpdateRegular, False

For more information about the Update method, search the Help Index for "Update method."

MORE INFORMATION

Following are examples of Microsoft Office 97 type libraries that may cause an error when referenced in earlier version Automation client applications:

   Library Name                              File Name
   ----------------------------------------------------

   Microsoft Access 8.0 Object Library       Msacc8.olb
   Microsoft DAO 3.5 Object Library          Dao350.dll
   Microsoft Excel 8.0 Object Library        Excel8.olb
   Microsoft Graph 8.0 Object Library        Graph8.olb
   Microsoft Office 8.0 Object Library       Mso97.dll
   Microsoft Outlook 8.0 Object Library      Msoutl8.olb
   Microsoft PowerPoint 8.0 Object Library   Msppt8.olb
   Microsoft Word 8.0 Object Library         Msword8.olb

Steps to Reproduce Behavior

The following example demonstrates adding a new record to the Customers table in the Northwind sample database shipped with Microsoft Access 97, using the Microsoft DAO 3.5 Object Library.

Although this example could be used from most earlier version Automation clients, this example specifically uses Microsoft Visual Basic 4.0 as an Automation client running code in the Microsoft DAO 3.5 Object Library.

1. Start Microsoft Visual Basic 4.0 and click New Project on the File menu to create a new Visual Basic project.

2. On the Tools menu, click References. Create a reference to the Microsoft DAO 3.5 Object Library, Dao350.dll, and then click OK.

3. On the Insert menu, click Module to create a new module.

4. Type the following procedure:

         Function AddRec()
            Dim DB as Database
            Dim r as Recordset
            Dim fname as String
            fname = "C:\Program Files\Microsoft Office\"
            fname = fname & "Office\Samples\Northwind.MDB"
            Set DB = OpenDatabase(fname)
            Set r = DB.OpenRecordset("Customers")
            r.AddNew
              r![CustomerID] = "ZZZZZ"
              r![CompanyName] = "Z Test"
            r.Update
            r.Close
            DB.Close
         End Function
   
   

5. On the Run menu, click "Start with Full Compile." Note that you receive the error message on the line of code that executes the Update method.

REFERENCES

For more information about creating references to other applications, search the Help Index for "setting references, to applications."