46-935 BBoard 

Message #1 
Subject:  Creating DLLs for Excel
Date:  11/4/98

A DLL is a library of compiled code that can be stored separately from executing 
processes.  The creator of the DLL specifies entry points (entry functions).  
Processes can dynamically load the DLL (mapping the DLL into the process's 
address space) and call the entry functions.  When the process no longer needs 
the functions in the DLL, it can unload it from its address space.

DLLs are commonly used when you have a block of code that may be useful to more 
than one particular program.  In addition, you might use DLLs when you are 
creating a software product that you want to be able to update in the field.

There are many different flavors of DLLs, and they don't all work the same.  For 
example, MFC DLLs you might create for your C++ Windows program may not be 
loadable by the VBA framework underlying Microsoft Excel.
This is unfortunate.

In this document we will specifically tackle creating a C++ DLL that can be 
loaded in Microsoft Excel.  To do this, you must follow these simple steps using 
Visual C++.

1. In Visual C++, create a new project of type "Win32 Dynamic Link Library" (do 
not use the MFC Appwizard).

2. Under the "Project" menu, choose "Add to Project" and add the source files 
that you wish to have compiled into the DLL.  For example, AbstractOption.cpp, 
AbstractOption.h, etc.  Do not add your "Tester.cpp" file or equivalent.  The 
DLL project should not  include a "main" function.

3. Add a new source file to the project .  In this source file you will write 
the entry point functions.  Model the source file after the one shown below:

#define STDCALL __stdcall
#define EXPORT  __declspec(dllexport)

EXPORT double STDCALL YourEntryPoint(double aParam);

#include "LookBackOption.h" //for example

double STDCALL YourEntryPoint(double aParam)
{
    // do some calculation ....
}

You may instantiate objects, call methods and do whatever calculation you would 
like in your entry point functions.  However, you MUST ensure that you manage 
the memory correctly.  Remember to delete every object that you new.  The 
__stdcall and __declspec(dllexport) keywords tell the compiler/linker that these 
functions will be entry points to the DLL.

4. Add a new text file to the project named "ProjectName.def".  This very short 
text file will list the function names that we will expose as entry points into 
the DLL.    This file should be modeled after the example below:

LIBRARY ProjectName

EXPORTS

YourEntryPoint

After the LIBRARY keyword, list the name of your project (the name of the DLL we 
will build).  After the EXPORTS keyword, list the names of the functions you 
defined in Step 3 (don't list the parameters, etc.).

5. Now you are ready to compile your DLL.  Under the build menu, choose "Rebuild 
All."  This will build a file with the extension .dll.  Move this file to a 
well-known location on your hard drive (not on the network!).

6. Open Excel, and start a new workbook.  Under the Tools menu, go to Macros and 
open the Visual Basic Editor.  Insert a new "Module" into the VBAProject 
associated with your new workbook.  This will open a new window for adding VB 
code for the Module.  To load the DLL you must only write one "Declare 
Statement" for each entry point function you have exposed.  We will use the 
following syntax for the Declare Statement

Declare Function NameofFunction Lib "Patht DLL" (parameter list) As ReturnType

The declare statement for the entry point function described above would be:

Declare Function YourEntryPoint Lib "C:\users\jeffreys\ProjectName" (ByVal 
aParam As Double) As Double 

See the Visual Basic Help (accessible through the VBA Editor) for more 
information about the "Declare Statement".

7. Compile the VBA project.  Save the Workbook, close the Editor and return to 
Excel.  In Excel, choose "Function" under the Insert menu heading.  When the 
"Paste Function" dialog comes up, choose "User Defined" in the left half of the 
window.  You should see your function listed on the right.  Select this 
function, and use it just like a built in Excel function!  If you receive the 
value "#VALUE!" something is not right (Excel won't indicate what went wrong).  
Check over steps 1-6 and ensure that you have done everything correctly.  Good 
luck!


The example that I showed you in class is available in the handout directory and 
as a zip file linked off the bboard.