How To Build an Add-in (XLL) for Excel Using Visual C++

Article translations Article translations
Article ID: 178474 - View products that this article applies to.
This article was previously published under Q178474
Expand all | Collapse all

On This Page

SUMMARY

An XLL is an add-in for Microsoft Excel that you can build with any compiler that supports building DLLs (dynamic link libraries). This article is designed to get you started building XLLs with Microsoft Visual C++. To follow the steps outlined in this article, you should have some experience building DLLs and you should have the Microsoft Excel 97 Developer's Kit, which contains the necessary header and library files to build an XLL.

MORE INFORMATION

Steps to Create an XLL

  1. Create a new MFC AppWizard (.dll) project called Anewxll.
  2. Copy the Xlcall.h, Framewrk.h, Framewrk.c, and Xlcall32.lib from the Microsoft Excel 97 Developer's Kit to your project directory.
  3. Rename Framewrk.c to Framewrk.cpp.
  4. Add Framewrk.cpp to your project source files.
  5. Add the following #include line to the top of Framewrk.cpp to avoid compiler errors about pre-compiled headers:
    
          #include "stdafx.h"
    					
  6. Change the following line in Framewrk.cpp:
          #include "..\..\include\xlcall.h"
    					
    to:
          #include "xlcall.h"
    					
  7. Click Settings on the Project menu. Click the Link tab and add Xlcall32.lib to the Object/library modules edit box.
  8. Add the following #include statements to the top of Anewxll.cpp:
          #include "xlcall.h"
          #include "framewrk.h"
    					
    NOTE: The project is now setup appropriately and should compile, however, you have not added XLL support yet. The remaining steps give some sample code you can add to the XLL to get you started.

  9. Append the following code to Anewxll.cpp: Sample Code -----------
          //================================================================
          // Commonly used global variables
          int err;
          char buf[8192];
          char txt[8192];
    
          // Function registering table
          int nFuncs;
    
          // proc, type_text, function_text, arg, macro_type, category,
          // shortcut_text, help_topic, function_help
          static LPSTR func[][9] = {
          {" MyFunc", " JJJ", " MyFunc", " ", " 1", " MyCat", " ", " ", " "},
          {" MyMotd", " I", " MyMotd", " ", " 1", " MyCat", " ", " ", " "},
          {0,0,0,0, 0, 0, 0}
          };
    
          // Menu table
          int nMenuItems;
    
          static LPSTR menu[][5] = {
          {" &MyMenu", " ", " ", " Joe's Xll menu!!!", " "},
          {" M.O.T.D."," MyMotd", " ", " Message of the Day!", " "},
          {0, 0, 0, 0, 0}
          };
    
          // Initialization routine
          BOOL __stdcall xlAutoOpen(void) {
             AFX_MANAGE_STATE(AfxGetStaticModuleState( ));
    
             // DEBUG output to indicate when called
             AfxMessageBox("xlAutoOpen() called!", MB_SETFOREGROUND);
    
             int i, j;
    
             // Get XLL file name
             static XLOPER xDll;
             Excel(xlGetName, &xDll, 0);
    
             // Prefix strengths with their length & count items
             // Note the framework's TempStr() function prefixes the
             // lengths anyway, but this is for other code that might
             // use the arrays
             for(nFuncs=0;     func[nFuncs][0];     nFuncs++) {
                 for(i=0; i<9; i++) {
                     func[nFuncs][i][0]     = (BYTE) strlen(func[nFuncs][i]+1);
                 }
    
             }
    
             for(nMenuItems=0; menu[nMenuItems][0]; nMenuItems++) {
                 for(i=0; i<5; i++) {
                 menu[nMenuItems][i][0] = (BYTE) strlen(menu[nMenuItems][i]+1);
                 }
             }
    
             // Loop through the function list, and register the functions
             for(i=0; i<nFuncs; i++) {
    
                // Register a function
                err = Excel(xlfRegister, 0, 9, (LPXLOPER)&xDll,
                   (LPXLOPER)TempStr(func[i][0]),
                   (LPXLOPER)TempStr(func[i][1]),
                   (LPXLOPER)TempStr(func[i][2]),
                   (LPXLOPER)TempStr(func[i][3]),
                   (LPXLOPER)TempStr(func[i][4]),
                   (LPXLOPER)TempStr(func[i][5]),
                   (LPXLOPER)TempStr(func[i][6]),
                   (LPXLOPER)TempStr(func[i][7]),
                   (LPXLOPER)TempStr(func[i][8])
                   );
    
                if(err != xlretSuccess) {
                 sprintf(buf, "xlfRegister for function %d, err = %d", i, err);
                 AfxMessageBox(buf, MB_SETFOREGROUND);
                }
             }
    
             // Free XLL file name from the xlGetName call made earlier
             Excel(xlFree, 0, 1, (LPXLOPER)&xDll);
    
             // Menu support section
             static XLOPER xMenu;
             static XLOPER xMenuList[10*5];
             ASSERT(nMenuItems< 10);
    
             // Build menu
             xMenu.xltype            = xltypeMulti;
             xMenu.val.array.lparray = &xMenuList[0];
             xMenu.val.array.rows    = nMenuItems;
             xMenu.val.array.columns = 5;
    
             for(i=0; i<nMenuItems; i++) {
                 for(j=0; j<5; j++) {
                     xMenuList[j+i*5].xltype  = xltypeStr;
                     xMenuList[j+i*5].val.str = menu[i][j];
                 }
             }
    
             // Add menu
            Excel(xlfAddMenu,0,3,TempNum(1),(LPXLOPER)&xMenu,TempStr(" Help"));
    
             // Finished
             return 1;
          }
    
          // Cleanup routine
          BOOL __stdcall xlAutoClose(void) {
             ::MessageBox(NULL, "xlAutoClose()", "Debug", MB_SETFOREGROUND );
    
             // Delete menu
             Excel(xlfDeleteMenu, 0, 2, TempNum(1), TempStr(" MyMenu"));
    
             return 1;
          }
    
          // Support for descriptive information about the add-in(s)
          // You can add a new customized title for the user, but
          // unfortunately, only an add-in written in Microsoft Visual Basic
          // can add a description string.
          LPXLOPER _stdcall xlAddInManagerInfo(LPXLOPER xAction) {
             static XLOPER xInfo, xIntAction;
    
             // Find out what action must be taken
             Excel(xlCoerce, &xIntAction, 2, xAction, TempInt(xltypeInt));
    
             // DEBUG output to indicate when called
             sprintf(buf, "xlAddInManagerInfo(%ld)", (long)xIntAction.val.w);
             ::MessageBox(NULL, "xlAddInManagerInfo()", "Debug",
                 MB_SETFOREGROUND );
    
             // Set title if asked
             if(xIntAction.val.w == 1) {
                 xInfo.xltype = xltypeStr;
                 xInfo.val.str = " My Add-in!!!!";
                 xInfo.val.str[0] = (char)strlen(&xInfo.val.str[1]);
             }
             else {
                 xInfo.xltype = xltypeErr;
                 xInfo.val.err = xlerrValue;
             }
    
             return (LPXLOPER)&xInfo;
          }
    
            short __stdcall MyMotd(void) {
             char *name[] = {
                "Rebekah",
                "Brent",
                "John",
                "Joseph",
                "Robert",
                "Sara",
                0
             };
             char *quote[] = {
                "An apple a day, keeps the doctor away!",
                "Carpe Diem: Seize the Day!",
                "What you dare to dream, dare to do!",
                "I think, therefore I am.",
                "A place for everything, and everything in its place.",
                "Home is where the heart is.",
    
                0
             };
    
             int nNames, nQuotes;
    
             for(nNames=0; name[nNames]; nNames++);
             for(nQuotes=0; quote[nQuotes]; nQuotes++);
    
             sprintf(buf, "%s says '%s'", name[rand()%nNames],
                quote[rand()%nQuotes]);
             ::MessageBox(NULL, buf, "XLL MOTD", MB_SETFOREGROUND );
    
             return 0;
          }
    
          // Example function that returns the product of its two parameters
          long __stdcall MyFunc(long parm1, long parm2) {
           sprintf(buf, "You sent %ld and %ld to MyFunc()!", parm1, parm2);
           ::MessageBox(NULL, buf, "MyFunc() in Anewxll!!!", MB_SETFOREGROUND);
    
           return parm1 * parm2;
          }
          //=================================================================
    					
  10. Add the following lines to the end of your Anewxll.def file:
          xlAutoOpen
          xlAutoClose
          xlAddInManagerInfo
          MyMotd
          MyFunc
    					
  11. You can now compile your project to produce a DLL called Anewxll.dll. Once the DLL is compiled, rename it to Anewxll.xll.

Using the Add-In with Microsoft Excel

  1. Start a new workbook in Microsoft Excel.
  2. Click Add-ins on the Tools menu. Browse to add Anewxll.xll and click OK. Notice that when you click OK in the Add-ins dialog, the xlAutoOpen function runs.
  3. Click M.O.T.D on the MyMenu menu. When your menu item is clicked, the MyMotd function runs to display a MessageBox with a quote such as "Rebekah says 'An Apple a day, keeps the doctor away!'".
  4. In a cell, add the following formula:
    =MYFUNC(2,6)
    					
    MYFUNC returns 12, the product of 2 and 6.

  5. Click Add-ins on the Tools menu. Clear the add-in check box and click OK. Notice that when you click OK in the Add-ins dialog box, the xlAutoClose function runs.

Additional Notes

If you are using Microsoft Visual C++ 6.0, the preceding example fails when you compile it in Debug mode. The failure occurs because as of Visual C++ 6.0, string literals are placed in read only memory in debug builds, and the trick that XLL developers use to length-prefix their strings causes an access violation. As a work-around, you can remove the /ZI compiler switch in your project settings, or just test a release-build. For more information, see the following article in the Microsoft Knowledge Base:
198477 PRB: Use of /ZI May Cause Access Violation

REFERENCES

For more information regarding XLLs, please see:
Microsoft Excel 97 Developer's Kit (ISBN: 1-57231-498-2)

Properties

Article ID: 178474 - Last Review: February 12, 2007 - Revision: 4.3
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual C++ 4.0 Standard Edition
  • Microsoft Visual C++ 5.0 Enterprise Edition
  • Microsoft Visual C++ 6.0 Enterprise Edition
  • Microsoft Visual C++ 5.0 Professional Edition
  • Microsoft Visual C++ 6.0 Professional Edition
  • Microsoft Visual C++, 32-bit Learning Edition 6.0
Keywords: 
kbhowto KB178474

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com