INFO: Passing Values by Reference to a VBA Automation Server

• The Microsoft Foundation Classes (MFC), included 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) 

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:

   Q178749 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.

Additional query words: 3.00 3.10 3.20 2.0 2.1 2.2 Excel Word Powerpoint MSProject VB run macro

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

Last Reviewed: July 26, 1999