Regarding some issues in Excel-DNA

Apr 21, 2011 at 8:40 PM

Hi,

I am using Excel-DNA in order to implement excel- addins and I found it so interesting.

First of all, I really appreciate for the hard work to create Excel-DNA for the easier implementation.

However, I have some issues within the usage of Excel- DNA

Here are the issues.

1. I cannot delete the .DLL file.

    For example, in .dna file,

     <DnaLibrary>
                    <ExternalLibrary Path="TrialAddin.dll" />
     </DnaLibrary>
     

     In this case, I cannot delete TrialAddin.dll after I loaded it using ExcelDna and Excel keeps referencing the same file even after I change its name or move it to different folder.

2. I want to use DateTime in my method for my UDF. Is it okay to use it in the method? (Parameters in my UDF are strings)

3. I am referencing Bwl.Core.dll in TrialAddin.dll. As Bwl.Core.dll does not contain any static method that will be used in excel, is it just okay to include it as reference TrialAddin.dll

and just write

<DnaLibrary>
                <ExternalLibrary Path="Trial2.dll" />
</DnaLibrary>

in ExcelDna.dna?

 

Thank you so much,

 

 

Apr 21, 2011 at 8:55 PM

In addition, I am posting my UDF here.

 

 

using System;

using ExcelDna.Integration;

using Bwl.Core;

public class MyFunctions

{

    private static readonly IHasTimeSeries has_time_series = new HasTimeSeries();

    [ExcelFunction(Description = "Get LoadSeries", Category = "Useful functions")]

    public static object LoadSeries(string ticker, string seriesname,string startDate, string endDate

    {

        return has_time_series.GetArray(ticker, seriesname, DateTime.ParseExact(startDate, "yyyy-mm-dd", null), DateTime.ParseExact(endDate, "yyyy-mm-dd", null));

    }

}

Coordinator
Apr 21, 2011 at 9:27 PM

Hi,

1. The latest source version in the Source Code tab (from 13 March) has a feature to load the library without locking the .dll file. To enable this you'd say:

 <ExternalLibrary Path="TrialAddin.dll" LoadFromBytes="true" />

This loads the external library into a different .NET runtime 'Load context' so it can possibly have side effects. But your .dll will no longer be locked, so you can recompile and reload it in Excel without having to close Excel - worth trying out, I think.

2. Indeed you can use DateTime as a parameter type and as a return type for your functions. For input parameters like in your LoadSeries function it works fine. The only issue is that dates returned to Excel do not automatically have date formatting applied to the calling cells, so it might be displayed as a floating point value, and you need to set the numeric date format for those cells. It is a limitation of Excel that user-defined function cannot behave like the built-in TODAY() and NOW() functions.

So I would recommend you try:

public static object LoadSeries(string ticker, string seriesname, DateTime startDate, DateTime endDate {...}

3. Additional assemblies that are referenced by your 'ExternalLibrary' need not be mentioned in the .dna file. However, if you do add them as 


<DnaLibrary>
   <Reference Path="BWL.Core.dll" Pack="true"/>
   <ExternalLibrary Path="..." Pack="true" />
<DnaLibrary>

you can use the ExcelDnaPack utility to pack those dependencies into the .xll too. It might also affect the assembly resolution, allowing you to just copy the BWL.Core.dll next to your .xll and TrialAddIn.dll, but I forget whether that is needed.

Regards,

Govert