Optional method parameter usage from VBA question

[Michael Mattias]

If that user asks (and someone will ask) , "Why do I have to pass 'empty' here, when it's not used?," the above answer sounds a lot better than "because either me or my compiler seems to be having some problems with optional parameters passed to COM class methods"

And what I suggest is true, if only in a Clintonesque way: the 'future development' being better handling of optional parameters.

[Trevor Lane]

Michael is thinking he is microsoft again

[Michael Mattias]

And if the user asks why she has to pass 'empty' or 'null' , you just tell her, "That parameter is reserved for future development."

[David Warner]

Hi Trevor,

The following VBA works.

        l_result = oQFnc.ftestoptional(Empty)

as does

        l_result = oQFnc.ftestoptional(Null)

David

[Trevor Lane]

If memory serves me correct you can check for empty but you cannot pass EMPTY as a parameter. you have to pass NULL

[David Warner]

Good call Michael, the keyword is 'Empty'.

From the Access help file...

The Empty keyword is used as a Variant subtype. It indicates an uninitialized variable value.

Thanks,

David

[Michael Mattias]

I think VBA allows you pass literals such as "NONE" or "MISSING" (without the quotes) in various parameter positions and it handles that kind of thing for you. (I know VB does).

Worth looking into anyway...

[David Warner]

Thanks for the thoughts Michael.

According to PB support it does appear to be a compiler issue and they are currently looking into it.

In the short term, I think I'll just pass something recognisable (to be ignored by the routine) in the 'non-optional optional parameters' to work around the VBA compilation error.

Regards,

David

[Michael Mattias]

May or may not be relevant as a workaround...

A couple of months ago I got some help here with ADO which has a number of METHOD calls with "optional" params....

Seems there were two possible solutions....
A) Go to a 'named parameter' string (option=value, option=value....)
B) pass a VARIANT assigned with ' vVar = ERROR %DISP_E_PARAMNOTFOUND' (something like that, I'm too lazy to check the actual equate name right now).

I'm not sure this applies when YOU are creating the server, but maybe it will give you an idea or two.

MCM

[David Warner]

Hi Dominic,

Thanks for your reply, I did wonder if there might be an issue when I looked at the method information returned by the PowerBasic COM browser as there was no mention of OPTIONAL in the parameter list...

Method TESTOPTIONAL <136> (Byval S_PARAM As Variant) As Long

I'll email support for their advice.

David

[Dominic Mitchell]

// Generated .IDL file (by the OLE/COM Object Viewer)
// 
// typelib filename: <could not determine filename>

[
  uuid(133A366D-A5A7-46F6-96D8-2CC9340CC24D),
  version(1.0),
  helpstring("Server")
]
library Server
{
    // TLib :     // TLib : OLE Automation : {00020430-0000-0000-C000-000000000046}
    importlib("stdole32.tlb");

    // Forward declare all types defined in this typelib
    interface IFUNC;

    [
      odl,
      uuid(255A2473-9BF2-4252-AF80-21B89C467134),
      helpstring("IFUNC is a dual interface with IDispatch."),
      dual,
      nonextensible,
      oleautomation
    ]
    interface IFUNC : IDispatch {
        [id(0x00000088)]
        HRESULT TESTOPTIONAL(
                        [in] VARIANT S_PARAM, 
                        [out, retval] long* );
    };

    [
      uuid(522FBD0C-C345-49A5-9B55-709048226DE8),
      noncreatable
    ]
    coclass CFUNC {
        [default] interface IFUNC;
    };
};

The code shown above is the *.idl file that was decompiled from the type library file(*.tlb) that was
generated by PowerBASIC for your code. Unfortunately, it is wrong.
This was produced by PB9.0(I keep forgetting to download the update), but I guess the update is
generating the same bad code.

It should be

        [id(0x00000088)]
        HRESULT TESTOPTIONAL(
                        [in, optional] VARIANT S_PARAM, 
                        [out, retval] long* );

The generated code is missing the optional attribute.

[David Warner]

Hi Folks,

I have a PB/Win 9.01 COM server application that I use successfully from within the Microsoft Access 2003 VBA environment. Everything works fine when all method parameters are passed to the COM server from VBA, however I would like to specify an optional method parameter.

I get a VBA compile error saying 'Argument not optional' even though the PB method parameter clearly has OPTIONAL specified.

Am I missing something simple? I would appreciate any ideas or comments.

Thanks,

David



(example un-compiled PB/Win 9.01 COM server code )

#COMPILE DLL "SERV.DLL
#DIM ALL
#COM NAME "Server",1.0
#COM DOC "Server"
#COM GUID GUID$("{133A366D-A5A7-46F6-96D8-2CC9340CC24D}")
#COM TLIB ON        

$cFuncGuid = GUID$("{522FBD0C-C345-49A5-9B55-709048226DE8}")
$iFuncGuid = GUID$("{255A2473-9BF2-4252-AF80-21B89C467134}")

CLASS cFunc $cFuncGuid AS COM

    INTERFACE iFunc $iFuncGuid : INHERIT DUAL

        METHOD testoptional<136> (OPTIONAL BYVAL s_param AS VARIANT) AS LONG
            IF NOT ISMISSING (s_param) THEN
                MSGBOX "Parameter passed : " & VARIANT$(s_param)
                METHOD = %TRUE
            ELSE
                MSGBOX "No parameter passed."
                METHOD = %FALSE
            END IF
        END METHOD

    END INTERFACE

END CLASS

Access 2003 VBA

Public Function testoptional()
        Dim l_result As Long
        
        Set oFnc = New CFUNC

	' 1) method called successfully with a parameter
        l_result = oFnc.testoptional("XYZ")

	' 2) causes a 'Microsoft Visual Basic compile error : Argument not optional'
        l_result = oFnc.testoptional()
        
        Set oFnc = Nothing
End Function