INFO: Passing Values by Reference to a VBA Automation Server (184805)

MORE INFORMATION

Microsoft Access 97 and other Office 97 programs use Visual Basic for Applications 5.0. Office 2000 uses Visual Basic for Applications 6.0. And Microsoft Office XP uses Visual Basic for Applications 6.2.

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

1. In Microsoft Access, create a new database named "c:\mydatabase.mdb" (without the quotation marks).
2. 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
   					

3. Exit Microsoft Access.
4. 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:

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

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

         #include "msacc8.h"
   					

6. 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;
         }
   					

7. Compile your Visual C++ project, then run it. RESULTS: The new values that are returned from the Access user-defined function are displayed.