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

On This Page

• SUMMARY
• MORE INFORMATION
  • Steps to Create an XLL
  • Using the Add-In with Microsoft Excel
  • Additional Notes
• REFERENCES

SUMMARY An XLL is an add-in for Microsoft Excel that you can build with any compiler tha...

MORE INFORMATION Steps to Create an XLL Create a new MFC AppWizard (.dll) project called Anewxll....

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   
   Add the following lines to the end of your Anewxll.def file:   
         xlAutoOpen                        xlAutoClose                        xlAddInManagerInfo                        MyMotd                        MyFunc                        
      
   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