INFO: Passing Values by Reference to a VBA Automation Server

ID: Q184805

The information in this article applies to:

The Microsoft Foundation Classes (MFC), used with:
- Microsoft Visual C++, 32-bit Editions, versions 4.0, 4.2, 5.0, 6.0
- Microsoft Visual Basic for Applications version 5.0

SUMMARY

Parameters declared in Visual Basic for Applications (VBA) functions are by default passed by reference. Thus, calling these functions from MFC requires some work to ensure that variables are passed across by reference such that new values are reflected in the MFC calling code.

MORE INFORMATION

To pass a variable by reference, you pass the variable as the type OR'ed with VT_BYREF. This ensures that the data is accessible inside the Visual Basic for Applications function, and that changes are reflected back in the calling code.

The following example uses Microsoft Access 97 as an automation server to illustrate how you can pass variables by reference to a Visual Basic for Applications application. When running Microsoft Access 97 as an automation server, functionality is exposed through the Application interface to run a user-defined function. This is provided through the Run method, which is prototyped below:


   VARIANT _Application::Run(LPCTSTR Procedure, VARIANT* Arg1, VARIANT*
      Arg2, ......., VARIANT* Arg30)

The example demonstrates a way to ensure values passed in as Arg1, Arg2, and so on, can be modified and reflected in the calling application code.

Example

In Microsoft Access, create a new database named "c:\mydatabase.mdb" (without the quotation marks).

Add a new module to the database and then add the following code to the module:


      Function MyFunction(strParam1 As String, strParam2 As String)
         strParam1 = "NewValue1"
         strParam2 = "NewValue2"
      End Function

Exit Microsoft Access.

Follow steps 1 through 12 in the following Microsoft Knowledge Base article to create a sample project that uses the IDispatch interfaces and member functions defined in the Msacc8.olb type library:

Q178749 HOWTO: Create an Automation Project Using MFC and a Type Library

At the top of the AutoProjectDlg.cpp file, add the following line:


      #include "msacc8.h"

Add the following code to CAutoProjectDlg::OnRun() in the AutoProjectDLG.cpp file:

Sample Code


      // Start Microsoft Access.
      _Application* pAccess = new _Application;
      VERIFY(pAccess->CreateDispatch("Access.Application"));

      // Open a database.
      pAccess->OpenCurrentDatabase("c:\\mydatabase.mdb", FALSE);

      // Initialize an array of variants for the Run method.
      VARIANT aExc[30];
      for (int i=0;i<30;i++)
      {
         VariantInit(&aExc[i]);
         aExc[i].vt=VT_ERROR;
         aExc[i].scode=0x80020004;   // indicates parameter isn't used.
      }

      // Set up some arguments.
      CString pAV1 = "FirstParam";   //Initial value for Arg1.
      CString pAV2 = "SecondParam";  //Initial value for Arg2.
      BSTR bParam1 = pAV1.AllocSysString();
      BSTR bParam2 = pAV2.AllocSysString();

      // Initialize parameters 1 and 2.
      aExc[0].pbstrVal=&bParam1;
      aExc[1].pbstrVal=&bParam2;
      aExc[0].vt=VT_BSTR | VT_BYREF;
      aExc[1].vt=VT_BSTR | VT_BYREF;

      // Call Application.Run
      try
      {
         pAccess->Run("MyFunction ",
                  &aExc[0],&aExc[1],&aExc[2],&aExc[3],&aExc[4],
                  &aExc[5],&aExc[6],&aExc[7],&aExc[8],&aExc[9],
                  &aExc[10],&aExc[11],&aExc[12],&aExc[13],&aExc[14],
                  &aExc[15],&aExc[16],&aExc[17],&aExc[18],&aExc[19],
                  &aExc[20],&aExc[21],&aExc[22],&aExc[23],&aExc[24],
                  &aExc[25],&aExc[26],&aExc[27],&aExc[28],&aExc[29]);

         //After the function has been called,
         //the values of bParam1 and bParam2 have changed.
         //Display the new values.
         AfxMessageBox(CString("bParam1 = ") + CString(bParam1));
         AfxMessageBox(CString("bParam2 = ") + CString(bParam2));

      }
      catch(CException* e)
      {
         TCHAR szErrorMessage[1024];
         UINT nHelpContext;
         if (e->GetErrorMessage(szErrorMessage, 1024, &nHelpContext))
         {
            AfxMessageBox(szErrorMessage, MB_OK, nHelpContext);
         }
         e->Delete();
      }

      // Free any allocated strings.
      ::SysFreeString(bParam1);
      ::SysFreeString(bParam2);

      if (pAccess)
      {
         pAccess->Quit(0);
         pAccess->ReleaseDispatch();
         delete pAccess;
         pAccess = NULL;
      }

Compile your Visual C++ project, then run it.

Additional query words:

Keywords : kbinterop kbole kbAutomation kbExcel kbMFC kbVC400 kbVC420 kbVC500 kbVC600
Version : winnt:4.0,4.2,5.0,6.0
Platform : winnt
Issue type : kbinfo