VBA and COM interaction and parameter conversions

To let VBA access COM object,  we need to add ATL class from class wizard, which would add new IDL for the new class into .idl file, update resource.h, .rc file and adding two source files. After editing the IDL file for the class and the source files, then compile the project, which would add a COMclass successfully.

Usually, the method of the COM class should be BSTR for string, note that BSTR is consist of wide characters. BSTR myStr = L "Wide char"; or myStr = ::SysAllocString(L"Wide char");  Actually, BSTR is just a pointer, user should allocate memory before using and release the memory after usage. ::SysFreeString(myStr);

Conversions in CString, std::string should be

String Type	Description
char/wchar/TCHAR	The C strings for ANSI and Unicode
CString	The C++/MFC class wrapper for C strings
BSTR	The Visual Basic string type
_bstr_t	A C++ class wrapper for the Visual Basic string type
CComBSTR	Yet another C++ class wrapper for the Visual Basic string type used predominately in ATL code

CString cs("Hello");

BSTR bstr = cs.AllocSysString();

_bstr_t str1(_T("test string"));

BSTR str2 = str1;    // directly using = operator

BSTR str2 = str1.copy()  // or using copy() function of _bstr_t type.

CString str3 = (LPCTSTR)str1;

BSTR str4 = CComBSTR(_T("test"));  //directly using '=' operator

CComBSTR str5;

str5.AppendBSTR(str1);

TCHAR str6[255];

_stprintf(str6, _T("%s"),(LPCTSTR)str1);  //works for both ansi and unicode.

_bstr_t str7 = str2;

Cstring str8;

str8.Format(_T("%s"), (LPCTSTR)str7);

For other parameter types.

class CComVARIANT : public tagVARIANT {}

For convenience, if you want a VARIANT type variable, CComVARIANT is a better choice which would call ::VariantInit() in constructor and Clear() in destructor automatically. Because CComVARIANT derives from VARIANT, it could be used in anywhere that requires VARIANT as parameter. 

struct tagVARIANT {
    union {
        struct __tagVARIANT {
            VARTYPE vt;
            WORD    wReserved1;
            WORD    wReserved2;
            WORD    wReserved3;
            union {
                ULONGLONG     ullVal;       /* VT_UI8               */
                LONGLONG      llVal;        /* VT_I8                */
                LONG          lVal;         /* VT_I4                */
                BYTE          bVal;         /* VT_UI1               */
                SHORT         iVal;         /* VT_I2                */
                FLOAT         fltVal;       /* VT_R4                */
                DOUBLE        dblVal;       /* VT_R8                */
                VARIANT_BOOL  boolVal;      /* VT_BOOL              */
                _VARIANT_BOOL bool;         /* (obsolete)           */
                SCODE         scode;        /* VT_ERROR             */
                CY            cyVal;        /* VT_CY                */
                DATE          date;         /* VT_DATE              */
                BSTR          bstrVal;      /* VT_BSTR              */
                IUnknown *    punkVal;      /* VT_UNKNOWN           */
                IDispatch *   pdispVal;     /* VT_DISPATCH          */
                SAFEARRAY *   parray;       /* VT_ARRAY             */
                BYTE *        pbVal;        /* VT_BYREF|VT_UI1      */
                SHORT *       piVal;        /* VT_BYREF|VT_I2       */
                LONG *        plVal;        /* VT_BYREF|VT_I4       */
                LONGLONG *    pllVal;       /* VT_BYREF|VT_I8       */
                FLOAT *       pfltVal;      /* VT_BYREF|VT_R4       */
                DOUBLE *      pdblVal;      /* VT_BYREF|VT_R8       */
                VARIANT_BOOL *pboolVal;     /* VT_BYREF|VT_BOOL     */
                _VARIANT_BOOL *pbool;       /* (obsolete)           */
                SCODE *       pscode;       /* VT_BYREF|VT_ERROR    */
                CY *          pcyVal;       /* VT_BYREF|VT_CY       */
                DATE *        pdate;        /* VT_BYREF|VT_DATE     */
                BSTR *        pbstrVal;     /* VT_BYREF|VT_BSTR     */
                IUnknown **   ppunkVal;     /* VT_BYREF|VT_UNKNOWN  */
                IDispatch **  ppdispVal;    /* VT_BYREF|VT_DISPATCH */
                SAFEARRAY **  pparray;      /* VT_BYREF|VT_ARRAY    */
                VARIANT *     pvarVal;      /* VT_BYREF|VT_VARIANT  */
                PVOID         byref;        /* Generic ByRef        */
                CHAR          cVal;         /* VT_I1                */
                USHORT        uiVal;        /* VT_UI2               */
                ULONG         ulVal;        /* VT_UI4               */
                INT           intVal;       /* VT_INT               */
                UINT          uintVal;      /* VT_UINT              */
                DECIMAL *     pdecVal;      /* VT_BYREF|VT_DECIMAL  */
                CHAR *        pcVal;        /* VT_BYREF|VT_I1       */
                USHORT *      puiVal;       /* VT_BYREF|VT_UI2      */
                ULONG *       pulVal;       /* VT_BYREF|VT_UI4      */
                ULONGLONG *   pullVal;      /* VT_BYREF|VT_UI8      */
                INT *         pintVal;      /* VT_BYREF|VT_INT      */
                UINT *        puintVal;     /* VT_BYREF|VT_UINT     */
                struct __tagBRECORD {
                    PVOID         pvRecord;
                    IRecordInfo * pRecInfo;
                } __VARIANT_NAME_4;         /* VT_RECORD            */
            } __VARIANT_NAME_3;
        } __VARIANT_NAME_2;

        DECIMAL decVal;
    } __VARIANT_NAME_1;
};

While passing an array of UDT (User Defined Ttype) from COM to VBA, SAFEARRAY is required.

To pass single UDTs or safearrays of UDTs for late binding, the Automation client must have the information necessary to store the type information of the UDT into a VARIANT (and if late-binding, the UDT must be self-described). A VARIANT of type VT_RECORD wraps a RecordInfo object, which contains the necessary information about the UDT and a pointer the UDT itself. The RecordInfo object implements a new interface, IRecordInfo, for access to the information. It is important to know that the actual storage of the UDT is never owned by the VARIANT; the IRecordInfo pointer is the only thing that is owned by the VARIANT.

The data for a UDT includes an IRecordInfo pointer to the description of the UDT, pRecInfo, and a pointer to the data, pvRecord.

To pass a UDT or safearray of UDTs in v-table binding, the Automation client is configured by following these steps:

1. For Visual Basic the client needs the type library generated from the server's IDL file, which describes the data types of the UDT. For C and C++ the client needs the header file generated from the server's IDL file , or to import the type library using the #import directive. For more information, see Describing UDT in the IDL File.

2. For passing a safearray of UDTs in Visual Basic, the client will need to fetch the IRecordInfo interface from the type library for specifying the type information of the UDT.

   For more information on how to fetch the IRecordInfo interface for v-table binding and late binding see Fetching the IRecordInfo Interface.

3. The client and server will then simply declare the UDT as one does any native Automation type and passes it to the other. The client calls the method.

To pass user defined COM objects to VBA, the method should be defined as

HRESULT ParameterRanges([in, out] VARIANT* pOut)  // Here pOut is defined as a VARIANT type which actually is a SAFEARRY.

In the method body, use CComObject<CScenarioParameterRange>::CreateInstance() to create the COM object, do not forget to call AddRef() for the COM object.

Then defines a  CComVariant to wrap the created COM object, as CComVariant  derives from CComOject. Then create a CComSafeArray<VARIANT> object and call SetAt to add the created COM object to the array. By calling CComSafeArray::Detach() to return SAFEARRAY pointer.  pOut should be set as VT_ARRAY | VT_VARIANT.