Problem Creating SayHello Add-in with .NET in VS Code

[Terry Aney]

I'm going to starting migrating a significant (and old) Excel-DNA .NET Framework add-in to .NET Core.  Originally, VS proper was the/my IDE of choice, but now I prefer VS Code whenever possible.

First question related to .NET, documentation indicates .NET6.0, I assume it will work in .NET7.0 as well (and 8.0 when I upgrade?)

When simply following the steps from Excel-DNA I received the following message:

"Initialization [Error] No objects loaded from KAT.Extensibility.Excel.AddIn, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null".

Here are the different files in my project:

Excel.AddIn.csproj

```

<Project Sdk="Microsoft.NET.Sdk">

    <PropertyGroup>

        <TargetFramework>net7.0-windows</TargetFramework>

        <Nullable>enable</Nullable>

        <ImplicitUsings>enable</ImplicitUsings>

        <RootNamespace>KAT.Extensibility.Excel.AddIn</RootNamespace>

        <AssemblyName>KAT.Extensibility.Excel.AddIn</AssemblyName>

        <KATVersionPrefix>1.0.*</KATVersionPrefix>

    </PropertyGroup>

    <PropertyGroup>

        <ExcelDna32BitAddInSuffix>.x86</ExcelDna32BitAddInSuffix>

        <ExcelDna64BitAddInSuffix Condition="'$(ExcelDna64BitAddInSuffix)' == ''">.64</ExcelDna64BitAddInSuffix>

        <ExcelDnaPackXllSuffix Condition="'$(ExcelDnaPackXllSuffix)' == ''"></ExcelDnaPackXllSuffix>

    </PropertyGroup>

    <ItemGroup>

      <PackageReference Include="ExcelDna.AddIn" Version="1.7.0" />

    </ItemGroup>

</Project>

```

KAT.Extensibility.dna

```

<DnaLibrary Name="KAT Excel Extensibility" RuntimeVersion="v4.0">

    <ExternalLibrary Path="KAT.Extensibility.Excel.AddIn.dll" LoadFromBytes="true" Pack="true" ComServer="true" ExplicitRegistration="true" ExplicitExports="true"/>

</DnaLibrary>

```

MyFunctions.cs

```

using ExcelDna.Integration;

namespace KAT.Extensibility.Excel.AddIn;

public static class MyFunctions

{

    [ExcelFunction( Description = "My first .NET function" )]

    public static string SayHello( string name )

    {

        return "Hello " + name;

    }

}

```

launch.json

```

{

    "version": "0.2.0",

    "configurations": [

        {

            "name": "Excel.AddIn",

            "type": "coreclr",

            "request": "launch",

            "preLaunchTask": "debug",

            "program": "C:\\Program Files\\Microsoft Office\\root\\Office16\\EXCEL.EXE",

            "args": ["/x", "${workspaceFolder}\\src\\bin\\Debug\\net7.0-windows\\KAT.Extensibility.64.xll"],

            "cwd": "${workspaceFolder}",

            "console": "internalConsole",

            "stopAtEntry": false

        }

    ]

}

```

[Govert van Drimmelen]

Hi Terry,

For the current Excel-DNA version 1.7.0 we only support .NET Framework 4.x and .NET 6.

We have an early pre-release version 1.8.0-alpha1 that also supports .NET 7 and .NET 8.

In your .dna file you should set ExplicitRegistration="false" for Excel-DNA to register the functions from your library.

(You see the error about "No objects loaded" for both reasons - the .NET version you are targeting is not supported by the Excel-DNA package you reference, and you have set ExplicitRegistration="true" but you are not registering anything yourself.)

For new projects, I would suggest that you not create a .dna file in your project at all - then it will be generated automatically into the output directory during the build.

You can set additional properties in the .csproj file itself.

The first one you need to match the .dna file behaviour is the "ExcelAddInFileName" - with a .dna file it uses the .dna file name. Without a .dna file it would use the project name (with -AddIn).

Your project file might then look like this:

<Project Sdk="Microsoft.NET.Sdk">

    <PropertyGroup>

        <TargetFramework>net6.0-windows</TargetFramework>

        <Nullable>enable</Nullable>

        <ImplicitUsings>enable</ImplicitUsings>

        <RootNamespace>KAT.Extensibility.Excel.AddIn</RootNamespace>

        <AssemblyName>KAT.Extensibility.Excel.AddIn</AssemblyName>

        <KATVersionPrefix>1.0.*</KATVersionPrefix>

    </PropertyGroup>

    <PropertyGroup>

        <!-- This root add-in file name would normally come from the project file name, but we can override -->

        <ExcelAddInFileName>KAT.Extensibility</ExcelAddInFileName>

        <ExcelDna32BitAddInSuffix>.x86</ExcelDna32BitAddInSuffix>

        <ExcelDna64BitAddInSuffix>.64</ExcelDna64BitAddInSuffix>

        <ExcelDnaPackXllSuffix>%none%</ExcelDnaPackXllSuffix>

        <ExcelAddInExplicitExports>true</ExcelAddInExplicitExports>

        <ExcelAddInExplicitRegistration>false</ExcelAddInExplicitRegistration>

        <ExcelAddInComServer>true</ExcelAddInComServer>

    </PropertyGroup>

    <ItemGroup>

      <PackageReference Include="ExcelDna.AddIn" Version="1.7.0" />

    </ItemGroup>

</Project>

I'm not sure how to configure the launch.json for VS Code. You'll have to check the path in the "args", but I also got an error about the "preLaunchTask": "debug" which I didn't understand.

I think you need a tasks.json file with the build task too, and then set the preLaunchTask to that build task.

-Govert

[Terry Aney]

Thanks.  Did mostly what you described and got 'hello' working :)  My Repo is here if you want to see how VS Code is setup to support debugging.  Going to implement as many of the 'weird' things that I did and document them and leave this repo avail in case you want to monitor/reference it as it becomes more complete.  I'll do my best to search google groups, docs and Excel-DNA github source, but my old .Net Framework add-in used/abused about every feature under the sun, and I don't see a ton of 'VS Code' and or '.NET' questions, so I'll probably be dropping them as new conversations as I get to them.

Couple of follow up questions to your help above

No .dna file

1. If I am going to use the Ribbon UI, and previously I had a .dna file that contained DNA settings and then the nested UI, I just change it to something like the following, where the Ribbon.xml file is an embedded resource and now only contains `customUI` information.

```

[ComVisible( true )]

public class Ribbon : ExcelRibbon

{

    public override string GetCustomUI( string RibbonID )

    {

        var resourceName = "KAT.Extensibility.Excel.AddIn.Resources.Ribbon.xml";

        var assembly = Assembly.GetExecutingAssembly();

        using var stream = assembly.GetManifestResourceStream( resourceName )!;

        using var reader = new StreamReader( stream );

        return reader.ReadToEnd();

    }

}

```

2. In my original .dna file, I had packing instructions like the following for embedding assemblies, how is that done without a .dna file?

```

<ExternalLibrary Path="BTR.Extensibility.Excel.dll" LoadFromBytes="true" Pack="true" ComServer="true"

    ExplicitRegistration="true" ExplicitExports="true"/>

<Reference Path="BTR.Evolution.Core.dll" Pack="true" />

<Reference Path="BTR.Evolution.Data.dll" Pack="true" />

``` 

3. You gave sample of the `<ExcelAddInFileName>KAT.Extensibility</ExcelAddInFileName>` property that can be used.  Is there documentation for all available properties or just discoverable in github source?

Ribbon/ComVisible

1. As you can see in sample above or in my repo, I have Ribbon working with `ComVisible` attribute.  In my original add-in, I had the following two settings in AssemblyInfo.cs, is anything like that needed in .NET core version?  I may have been using those settings for something I don't remember yet but will come across.

```

[assembly: ComVisible( false )]

// The following GUID is for the ID of the typelib if this project is exposed to COM

[assembly: Guid( "d2b91799-c2d2-471c-945a-8ffd5262390c" )]

```

2. In your ribbon sample you use `MessageBox.Show`.  I see you have a "using", but how did you get access to that namespace?  Is that from the "Microsoft.Windows.Compatibility" package?  I discovered the <UseWindowsForms>true</UseWindowsForms> csproj property to make this work.  Is one method preferred over another?

[Terry Aney]

So for #2 and #3 about .dna file, I found SDKProperties.csproj.

To solve #2, I'm guessing either a .deps.json file (have to read up on that) or maybe ExcelAddInInclude?

Also, trying to decipher ProduceReferenceAssembly and EnableDynamicLoading descriptions and if they are playing a role.

[Terry Aney]

Simply referencing it seemed to automatically pack it :)  So #2 is solved, unless you want to expand a bit on ExcelAddInInclude, ProduceReferenceAssembly, and EnableDynamicLoading.  But not important.  Thanks.

[Govert van Drimmelen]

Hi Terry,

 

• Your Ribbon plan using an xml file in a resource is good. You would do the same for images for the ribbon.
• When targeting .NET 6+, we use the .deps.json file to find the dependencies to pack. When targeting .NET Framework, you can use the <ExcelAddInInclude> property to add additional references. This post gives details about using wildcards with ExcelAddInInclude: https://groups.google.com/g/exceldna/c/qTKT9gECl7I/m/oCB9kcxbBAAJ
• All the SDK-style project properties are listed here, with defaults and some descriptions: Excel-DNA properties in SDK-style project files | Excel-DNA
• ProduceReferenceAssembly is not really Excel-DNA specific. We are compiling a library, and the reference assemblies are generated to help with other projects that would reference our library (if write these to a “ref” subdirectory of the output). But for an Excel-DNA add-in, the library would not be used from another .NET library, so we don’t need these reference assemblies. So we set it to false in the Excel-DNA build properties.
• EnableDynamicLoading is needed to tell the build that we want the dependencies to be copied to the output directory, just like for an ‘application’. Normally for libraries, the .NET build won’t copy the dependencies, just the compiled library and the .deps.json file. Then when the library is used in an application build, all the dependencies are merged and copied to the output. But in that sense the Excel-DNA build must be like an ‘application’ build, hence this setting. So we set it to true in the Excel-DNA build properties.
• ComVisible works as before. The assembly-level attribute sets the default for classes and interfaces, and you can override for a specific class or interface. It’s appropriate but not important to set the assemble-level attribute to false, then the classes you want to expose through COM (like the Ribbon class) are marked as true.
• The assembly Guid would only come into play it you are making COM server components for use from VBA, and need early binding to work and be stable. For a new project you should generate a new Guid, not use a copied one from another project.
• <UseWindowsForms>true</UseWindowsForms> is the right way to pull in a Windows Forms reference. There is a similar UseWpf property. You can set both if you need to.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/cda5913c-9568-42c0-983c-43eb4a1d6778n%40googlegroups.com.