Update: Added instructions for VS2015 (2016-07-16)

The last post was the introduction to the series of XLW related posts. Today I will describe how to set up a project with XLW. XLW comes with an installer that installs the project templates for your development environment. It is a quick way to start, but you don’t have much control over the process and the project template might not match your expectation on how project should be arranged.

XLW consists of the following components:

  • Header files
  • Static libraries (debug, release)
  • InterfaceGenerator - an executable that takes a C++ header file that contains special directives and creates a .cpp file with code that interfaces your functions with Excel.

Project Setup

I will show how to set up for 32 bit Excel in Visual Studio 2013 Pro (and Visual Studio 2015). Set up for other environments should be similar.

1. Create a new workspace

  • Select menu: File -> New -> Project... :\
    New Project menu

  • Select Other Project Types -> Visual Studio Solutions -> Blank Solution. Set your workspace location, type name mylib and click OK button :\
    New Solution

2. Create a new project in the workspace

  • Right click on the mylib workspace in the Solution Explorer and select Add -> New Project... :\
    New Project

  • Select Visual C++ -> Win32 -> Win32 Console Application, set project name to xlladdin and click OK :\
    xlladdin project

  • On the Overview page of the Win32 Application Wizard dialog box choose the Next button.

  • On the Application Settings, under Application type, select DLL and check Empty project and press Finish button:\
    Application Wizard

3. Download most recent version of XLW and add it to the project

  • Right click on the solution in the Solution Explorer and select Open Folder in File Explorer:\
    Open the solution in Explorer

  • Create thirdparty folder in the mylib solution folder. Then create xlw folder in mylib\thirdparty. Create include and lib folders in mylib\thirdparty\xlw. Finally create xlw folder in mylib\thirdparty\xlw\include\xlw:\
    dir tree

  • Head to XLW download page and download the most recent version of XLW, but do not install it.

  • Use 7z to open the downloaded file as an archive:\
    7z

  • Copy header files from the archive’s $_OUTDIR\xlw folder to mylib\thirdparty\xlw\include\xlw.

  • Copy library files from the archive’s xlw\lib folder to mylib\thirdparty\xlw\lib. Select library files that correspond to your environment. In the case of Visual Studio 2013 you need to choose files that start with xlw-vc120-mt: * xlw-vc120-mt-5_0_2f0.lib * xlw-vc120-mt-gd-5_0_2f0.lib * xlw-vc120-mt-gd-5_0_2f0.pdb

  • At this moment the distribution does not contain libraries for Visual Studio 2015. You can download 5.0.2 version from here (together with Visual Studio 2013 libraries and include files and InterfaceGenerator.exe).

  • Rename these library files to remove version (-5_0_2f0) from their names. This will enable you to update to a new version by just replacing the files without modifying solution build settings. I also create file with name corresponding to library version, so it is easier to track versions. I.e. I put 5_0_2f0.txt in lib folder.

  • Finally copy InterfaceGenerator.exe from the archive’s xlw\build\vc12\Release folder to mylib\thirdparty\xlw.

4. Configure build for All Configurations

Right click on xlladdin in the Solution Explorer, select Properties:\
xlladdin Properties\
Then select All Configurations:\
set configuration -> all

  • On the Configuration Properties/General sheet set Target Extension to .xll:\
    set extension to .xll

  • On the Configuration Properties/Debugging sheet set:\
    set debugging options

    • Command to OFFICE_PATH\excel.exe (where OFFICE_PATH is a path to your installation of Microsoft Office (in my case it is C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE)

    • Command Arguments to $(TargetPath)

  • On the C/C++ sheet (Note: if you don’t have this sheet add an empty mylib.cpp file to your project) set Additional Include Directories to $(SolutionDir)thirdparty\xlw\include;%(AdditionalIncludeDirectories):\
    additional include dirs

  • On the Linker sheet set Additional Library Directories to $(SolutionDir)thirdparty\xlw\lib\;%(AdditionalLibraryDirectories):\
    additional lib dirs

5. Configure build for Debug

Select Debug configuration:\
set configuration -> debug\
Add xlw-vc120-mt-gd.lib (xlw-vc140-mt-gd.lib for VS2015) to the Additional Dependencies on the Linker->Input sheet:\
set debug lib

6. Configure build for Release

Select Release configuration:\
set configuration -> release\
Add xlw-vc120-mt.lib (xlw-vc140-mt.lib for VS2015) to the Additional Dependencies on the Linker->Input sheet:\
set release lib

7. Add initial content

  • Create three empty files in the xlladdin project folder:

    • mylib.h
    • mylib.cpp
    • xlwmylib.cpp
  • Add the following content to mylib.h:

    #ifndef _mylib_h_
    
    #define _mylib_h_
    
    
    
    #include <xlw/xlw.h>
    
    
    //<xlw:libraryname=MyLibrary
    
    double //Square root of an argument
    MySqrt(double x //argument
           );
    
    #endif

  • Add the following content to mylib.cpp:

    #include "mylib.h"
    
    #include <math.h>
    
    
    double MySqrt(double x)
    {
      if( x < 0. )
      {
        throw("#Negative argument");
      }
      return sqrt(x);
    }

8. Set up custom build step to generate xlwmylib.cpp

  • In the Solution Explorer right click on mylib.h and select Properties

  • In the properties dialog box set configuration to All Configurations:\
    set configuration -> all

  • On General sheet set Item Type to Custom Build Tool and hit Apply:\
    set custom build type

  • On Custom Build Tool sheet (which appears after you hit Apply in the previous step) set the following (also make sure that the configuration is still set to All Configurations):\
    custom build tool

    • Command Line to $(SolutionDir)thirdparty\xlw\InterfaceGenerator.exe %(FullPath)

    • Description to Generating xlw%(Filename).cpp

    • Outputs to $(ProjectDir)xlw%(Filename).cpp;%(Outputs)

    • Execute Before to PreBuildEvent (this might be grayed out. Look at next step)

  • If you were not able to set Execute Before to PreBuildEvent on the Custom Build Tool page for the mylib.h file open Properties dialog for the xlladdin project (right click on it and select Properties), and then head to the Custom Build Step sheet. Set Execute Before to PreBuildEvent:\
    custom build step

9. Test addin creation

  • Set to reload files in Visual Studio automatically. Click on main menu ‘TOOLS’ -> ‘Options…’ and check Auto-load changes, if saved on the Environment/Document sheet:\
    autoload

  • Press F5 - the addin will be compiled and loaded in Excel (accept Excel’s security warnings by pressing Enable the add-in for this session only). If you see an error that the file’s content is not recognized - you are trying to open your 32 bit addin in 64 bit Excel.

  • Create a new spreadsheet and go to the Funciton wizard:\
    start function wizart

  • Find MyLibrary:\
    my library in wiz

  • Select MySqrt function and put 3 as an argument.

  • Save spreadsheet and exit Excel.

10. Debugging

  • Press F5 - Excel will start and load the addin.

  • Open the spreadsheet saved in previous section.

  • Put a breakpoint on if(x < 0.) line in mylib.cpp

  • Select a cell with MySqrt function and press Enter to run it.

  • The code will stop on the breakpoint.

11. Happy coding!

If you don’t have patience to go over these steps, just download the mylib solution (or VS2015 version), open it in Visual Studio and press F5 key. If your Excel is not installed in C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE you will need to adjust the path as described in Section 4.