Microsoft KB Archive/165538

= OFF: Error Message: Argument Not Optional =

Article ID: 165538

Article Last Modified on 10/11/2006

-

APPLIES TO


 * 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.</li></ol>

<div class="references_section">