Knowledge Base

OFF: Error Message: Argument Not Optional

Article ID: 165538

Article Last Modified on 10/11/2006

Microsoft Access 95 Standard Edition
Microsoft Excel 5.0 Standard Edition
Microsoft Excel 5.0a
Microsoft Excel 5.0c
Microsoft Excel 95 Standard Edition
Microsoft Excel 95a
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

This article was previously published under Q165538

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.

This article assumes that you are familiar with Visual Basic for Applications.

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

NOTE: The values dbUpdateRegular and False are the default values for the Update method. By explicitly including them in the line of code, you will not receive the "Argument not optional" error message.

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.

Start Microsoft Visual Basic 4.0 and click New Project on the File menu to create a new Visual Basic project.
On the Tools menu, click References. Create a reference to the Microsoft DAO 3.5 Object Library, Dao350.dll, and then click OK.
On the Insert menu, click Module to create a new module.

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

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

Additional query words: libraries Object TypeLib Dynamic Link Library DLL OLB TLB OLE Automation VB VBA 8.0 8.0a 97 Project VBA332.DLL VBA232.DLL

Keywords: kberrmsg kbprb KB165538