HOW TO: How can I call methods in .NET assemblies?

Reference: Q0040

Article Last Modified on 29-Dec-2006

The information in this article applies to:

XLL+ for Visual Studio .NET - 4.2, 4.3.1, 5.0

HOW TO: How can I call methods in .NET assemblies?

Question

I want to use methods from the .NET runtime libraries (or other code in .NET assemblies authored in C#, VB.NET or managed C++). What do I have to do to get it to work safely using Visual Studio .NET (2003)?

Answer

There are 3 issues to address:

Change the compiler settings for the project.
Use Managed C++ to call the .NET code.
Use a bootstrap XLL to load your XLL, and override the virtual method CXllApp::GetRegDllName().

The steps described below use an example XLL named ClrUser.xll. This was created using the XLL+ AppWizard (and selecting the STL run-time library). Wherever you see ClrUser you should replace it with the name of your XLL.

The sample project can be downloaded using the link below.

Compiler settings

Use the Project/Properties menu to open the Project Properties dialog. Click the Configuration Properties folder. Click the General property page. Modify the Use Managed Extensions property to Yes.
Click the Debugging folder. Modify the Command Arguments property to "$(TargetDir)/XnLoader.xll" (including the quotes).
Click the Linker folder. Click the Input page. Add the following libraries to the Additional Dependencies property:
```
xlllibsr.lib msvcrt.lib libcmt.lib
```
In the Debug build, use these libraries instead:
```
xlllibsd.lib msvcrtd.lib libcmtd.lib
```
Still on the Input page, add the following symbol to the Force Symbol References property:
```
__DllMainCRTStartup@12
```
Remaining within the Linker folder, click the Advanced page. Modify the Resource Only DLL property to Yes (/NOENTRY).
Click the Build Events folder. Click the Post-Build Event page. Modify the Command Line property to look as follows:
```
copy "$(ProjectDir)..\XnLoader\$(OutDir)\XnLoader.xll" "$(TargetDir)"
copy "$(ProjectDir)XnLoader.cfg" "$(TargetDir)"
```
This assumes that the XnLoader project is in a directory that is a sibling to the project directory. In the case of the samples, this is true - the sample project is in Samples/ClrUser, and the XnLoader project is in Samples/xnLoader. If your XnLoader project is located elsewhere, you should amend the lines above.

Use Managed C++ from your add-in

This is not the place to go into further details of using Managed C++. These notes describe the minimum changes required to let your project call the CLR.

In ClrUser.h add the following line, after #include <xllplus.h>:
```
#using <mscorlib.dll>
```
In ClrUser.def, add the following lines:
```
	crtInit   PRIVATE
	crtTerm   PRIVATE
```
Copy the file init.cpp from the ClrUser sample folder into your project folder, and add it to the project.

Add the following line to ClrUser.h:

	virtual void GetRegDllName(CXlOper& xloDllName);

Add the following declaration and method to ClrUser.cpp:

extern const char* GetFullPathForLoadedDLL();   // init.cpp

void CClrUserApp::GetRegDllName(CXlOper& xloDllName)
{
    xloDllName = GetFullPathForLoadedDLL();
}

Copy the file xnloader.cfg from the ClrUser sample folder into your project folder. Edit the file so that it contains the unqualified name of your real XLL in the first line, e.g.:
```
ClrUser.xll
```

Add an add-in function that makes use of the CLR. Use Managed C++ to call CLR methods. e.g.:

extern "C" __declspec( dllexport )
LPXLOPER ClrNow()
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    try
    {
        System::String* str = System::String::Format("{0}", __box(System::DateTime::Now));
        xloResult = NetToXL(str);
    }
    catch(System::Exception __gc* ex)
    {
        xloResult.Format("#ERROR: %s", NetToXL(ex->Message).c_str());
    }
    return xloResult.Ret();
}

Build and run

Build the project.

If you get an error The system cannot find the file specified. during the Post-Build Event, check the locations of XnLoader.xll and XnLoader.cfg (see step 6 above.)
To run the project, always open XnLoader.xll (located in the same directory as the real XLL), instead of the real XLL.

Your project is now ready to use .NET assemblies. If you using your own assemblies, then be sure to make them accessible to EXCEL.EXE at run-time. You can do this in several ways, including:

Adding your assemblies to the GAC.
Locating your assemblies in the folder which contains EXCEL.EXE.
Adding a codepath to excel.exe.config.

Sample project

The sample project disucssed in the steps above can be downloaded here.

The solution file ClrUser\ClrUser.sln contains the sample project and also the bootstrapper project, XnLoader.vcproj.

If you unzip the file into your XllPlus samples directory (typically C:\Program Files\Planatech\XllPlus\Samples) it will build immediately, without any need to change the Include and Lib paths.

Why a bootstrap XLL?

A serious problem can arise when initializing the C run-time libraries in a library which uses the .NET Common Language Runtime. Because of indeterminacy in the order of initialization, the process can lock indefinitely. For more details about the problem, see Mixed DLL Loading Problem and You receive linker warnings when you build Managed Extensions for C++ DLL projects on the Microsoft web-site.

The solution to this problem is to use a bootstrap XLL to do the following:

Respond to Excel's required callback functions (xlAutoOpen() etc).
Explicitly load the real XLL into memory using LoadLibrary().
Explicitly initialize the C run-time library on behalf of the real XLL.

A sample bootstrap XLL is provided, which can be used to load your real XLL.

Loading your XLL

To make the bootstrapper load your specific XLL, you should do the following:

Edit the file XnLoader.cfg to contain the name of your XLL.
Ensure that XnLoader.xll and XnLoader.cfg are located in the same directory as your real XLL.
Open (or select in Tools/Add-ins) XnLoader.xll instead of your real XLL.

How the Bootstrapper works

The two most important constraints that apply to the design of the bootstrapper are:

The C runtime library for the real XLL must be initialized before any CLR methods are called.
LoadLibrary() may not be called during DllMain().

Therefore, the bootstrapper does not load the real XLL during DllMain(), but delays until the first Excel API callback is made. (This will usually be xlAutoOpen(), but may be xlAddInManagerInfo().)

For each callback function, the bootstrapper does the following:

Checks whether the real XLL has been loaded.
If it has not been loaded, loads the XLL, and calls the CRT initialization function (crtInit).
Redirects the call to the identically named function (e.g. xlAutoOpen(), xlAutoFree() etc) in the real XLL.

HOW TO: How can I call methods in .NET assemblies?

HOW TO: How can I call methods in .NET assemblies?

Question

Answer

Compiler settings

Use Managed C++ from your add-in

Build and run

Sample project

Why a bootstrap XLL?

Loading your XLL

How the Bootstrapper works

See also