Excel-DNA 1.6 – .NET 6 / PackageReference / Anti-virus

Excel-DNA version 1.6 is now available for testing as pre-release version 1.6.0-preview3 from the NuGet package repository. The extension libraries ExcelDna.Registration and ExcelDna.IntelliSense also have matching releases. ExcelDnaDoc should follow in the coming weeks.

The main focus for version 1.6 is to provide a first preview of the support for add-ins that target .NET 6. As a prerequisite to adding the .NET 6 target, we’ve also added support for new SDK-style project files and NuGet references (also great for projects that target .NET Framework). I’ve also taken the opportunity to tweak the Excel-DNA packing mechanism a bit in an attempt to avoid some of the false positive anti-virus detections we’ve seen recently. I’ll briefly discuss these (in reverse order) below, but first a word about our sponsors.

GitHub sponsors

Excel-DNA is now registered on GitHub sponsors – see https://github.com/sponsors/Excel-DNA. Thank you very much to everyone who has already signed up – your contributions are directly funding further development.

If you use Excel-DNA and would like to encourage future support and ongoing development, please sign up as a GitHub sponsor for the project. GitHub sponsors will also have access to a private repository of sample projects where I hope to add additional tools and documentation over time.

I had previously considered the .NET Core / .NET 6 support as a good point to switch to a commercial model for Excel-DNA. However, the benefits of being an open-source project with a permissive license have been significant. So, if possible, I hope to continue with the core library as free and open source software, where further development is funded by the sponsors.

For those in a corporate setting using Excel-DNA extensively or in a mission critical role, I also offer an annual corporate support agreement. Please contact me directly if you are interested in more details.

Anti-virus false positives

Over the last year we’ve seen a number of anti-virus and security programs identify Excel-DNA add-ins as security risks, including false positive detections that delete add-in files by the default Windows Defender service. It seems that these detections were triggered by some malicious Excel add-ins that have been built using Excel-DNA, with the anti-virus heuristics subsequently identifying all Excel-DNA add-ins as problematic. For some security vendors, including Microsoft, we’ve been able to report this false positive detection and they have updated their signatures accordingly. But in many cases the Excel-DNA version 1.5 add-ins are still being flagged.

It seems the main heuristic used to detect Excel .xll add-ins as problematic is the presence of executable assemblies as resources in the .xll library. Excel-DNA uses the packing of assemblies as resources in the .xll to simplify the add-in distribution, in many cases making possible a single file (or two files for 32-bit and 64-bit) add-in distribution.

For this version there are two changes which may help with these false positives.

The first is that packed files are now encoded, so that they are not detected as embedded executable code directly. In my testing that change removed the false positive detections at least in the Microsoft products. However, at least one other user has reported still seeing problems with this version, so it does not seem like bulletproof solution.

Another option has been contributed by user @Phundamentals (thank you!), to add a project property which disables the compression of packed files.

  <ExcelDnaPackCompressResources>false</ExcelDnaPackCompressResources>

Should these mitigations not be enough to reduce most false positives, we could also introduce an option where there are no packed files at all (currently the managed Excel-DNA assemblies are always packed).

There have also been some indications that signing the final add-in library helps reduce false positive detections.

In all cases I do encourage developers to report the false positives to their anti-virus or security vendor. Their software is mistakenly identifying and blocking legitimate add-ins from running.

Developers of malicious add-ins are welcome to contact me for sponsored licenses of an alternative add-in framework.

PackageReference and SDK-style project files

The main ExcelDna.AddIn NuGet package now supports references in SDK-style project files. For new projects, this means that a .dna file is no longer added to the project automatically, and if not present will be created and output at build time from project properties. Existing projects with customized .dna file(s) will work as they did before.

For a new project, a simple project file might look like this:

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

  <PropertyGroup>
    <TargetFramework>net472</TargetFramework>

    <!-- We don't need the extra 'ref' directory and reference assemblies for the Excel add-in -->
    <ProduceReferenceAssembly>false</ProduceReferenceAssembly>

    <!-- We need all dependencies to be copied to the output directory, as-if we are an 'application' and not a 'library'.
         This property also sets the CopyLockFileAssemblies property to true. -->
    <EnableDynamicLoading>true</EnableDynamicLoading>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="ExcelDna.AddIn" Version="1.6.0-preview3" />
  </ItemGroup>

</Project>

Various additional properties can be set in the project file – see the example here https://github.com/Excel-DNA/ExcelDna/blob/master/Source/Tests/ExcelDna.AddIn.Tasks.IntegrationTests.TestTarget/SDKProperties/SDKProperties.csproj

.NET 6 support

Excel-DNA version 1.6 (finally!) includes support for add-ins that target .NET 6. Most Excel-DNA features seem to work, including the COM-based features like RTD-based functions, ribbon and CTP extensions. However, there has been very limited testing and you should consider the .NET 6 support as an early preview.

Update the TargetFramework tag in an SDK-style project file to the .NET 6 (Windows) target:

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

or build for both .NET Framework and .NET 6, which allows you to test both targets:

    <TargetFrameworks>net472;net6.0-windows</TargetFrameworks>

End users of the add-in will need to have the .NET 6 runtime installed. (To include the runtime files as part of the add-in will not be supported, but an installer program might check and install the runtime.)

A hard limitation of the .NET core series of runtimes (.NET 5 / 6 / 7 etc.) is that only one version of a .NET core runtime can be loaded into a specific process. The core runtime can still be loaded concurrently with a .NET Framework runtime (e.g. .NET Framework 4.8), although this is not a configuration officially supported by Microsoft. Excel-DNA will try to support add-ins targeting a .NET Framework running together with .NET 6 add-ins, but there is not planned to be any support for the future .NET 7 runtime. Fortunately .NET 6 is a ‘Long-term Support’ (LTS) release and will be formally supported until the end of 2024, so we can keep targeting .NET 6 for a few years. There is also some work on the horizon that make ahead-of-time compilation of .NET libraries (like the Excel-DNA add-ins) viable, bypassing the concurrent runtime issues. So I do expect a future path beyond .NET 6, though that is not an immediate concern.

I look forward to bug reports, questions and other feedback about the .NET 6 support (including whether it works at all). Support for modern .NET has been a long time coming and ensures that Excel-DNA can take part in the exciting future evolution of .NET.

After 16 years I am still amazed by and deeply appreciate the support and enthusiasm for Excel-DNA. Last but not least I want to thank Sergey Vlasov for his calm and consistent efforts that are now driving the project forward.

-Govert

Posted in Uncategorized | Tagged , , , | Comments Off on Excel-DNA 1.6 – .NET 6 / PackageReference / Anti-virus

Update v 1.5 / Sponsors / Roadmap

Excel-DNA version 1.5

A release candidate for the next version of Excel-DNA is now available for testing. Please try out this update, and let me know if you run into any problems or whether you are able to confirm that your add-in still works correctly. I expect to make a final release in a few weeks.

This update includes a completely new implementation of the core marshaling, replacing code that I initially wrote in 2005. I’ve also done some internal refactoring to prepare for supporting the .NET 5+ generation of runtimes. Finally, a few improvements were made to improve add-in load times when many functions are registered. Improved resilience in hostile anti-virus environments were also implemented since the last release.

Modernising the marshaling code has resulted in two notable changes of the minimum requirements for using Excel-DNA.

  • Only .NET Framework versions 4.5.2 to 4.8 are supported, and
  • Excel 2007 or newer is required, dropping support for the oldest versions of Excel.

UPDATE NOTE:
If you are updating the NuGet package on a project that currently targets an older version of .NET (e.g. .NET Framework 4.0), you should change the target framework for the project to .NET Framework 4.5.2 or later, before updating the NuGet package. Otherwise you may get one of the following problems:

  • The package install script may not run when the new package is installed. This leaves the project with a file called “_UNINSTALLED_xxxxx-AddIn.dna”. In this case, uninstall the ExcelDna.AddIn package, update the project target framework, and then reinstall. You might need to restart Visual Studio to complete the package updates.
  • Error messages stating “Error CS0246 – The type or namespace name ‘ExcelDna’ could not be found (are you missing a using directive or an assembly reference?)”. This also indicates that the target framework is not supported and should be updated, after which the errors should be resolved.

Apart from the new minimum requirements, the new release is a highly compatible update and you should not expect any issues or significant behaviour changes after updating.

Thank you to @ittegrat and @augustoproiete for their contributions to this version.

The following prerelease packages are available on NuGet:

  • ExcelDna.AddIn.1.5.0-rc1
  • ExcelDna.Integration.1.5.0-rc1
  • ExcelDna.IntelliSense.1.5.0-rc1
  • ExcelDna.Registration.1.5.0-rc1
  • ExcelDna.Registration.VisualBasic.1.5.0-rc1
  • ExcelDna.Registration.FSharp.1.5.0-rc1

GitHub Sponsors

Excel-DNA is now registered on the GitHub sponsors program. This lets you sponsor future Excel-DNA development, and also gives access to direct support for your Excel-DNA projects.
Billing for the monthly sponsorship is through your GitHub account, making it easy to add sponsorship of open-source projects to a corporate GitHub account.

If you are using Excel-DNA and would like to see further development and ongoing support, please sign up as a sponsor.
A big thank you to those users who were the first to do so: @PierreYvesR, @terryaney, @mhouldsworth @KoosBusters and one private sponsor.

I’ve set up three levels of monthly sponsorship subscriptions:

  • Individual – $14 pm. For power users and developers making add-ins for personal or limited use.
  • Team – $170 pm. For smaller development teams in a company, those publishing add-ins externally or using more advanced add-in features.
  • Corporate – $850 pm. For larger companies with mission critical applications or multiple development teams.

For the Team and Corporate sponsorship levels I am also available for some monthly direct support sessions to help deal with problems or advise on your add-in projects.

The direct corporate support contracts are available as before, with bespoke agreements and extensive direct support.
Thank you very much to the existing corporate partners who have supported Excel-DNA over the past decade and continue to ensure the project’s viability.

Roadmap

The development work for Excel-DNA will next address two areas:

  • Improved support for SDK-style project files and a PackageReference import of the main NuGet package. The newer .NET project templates and build tools are based on the SDK-style project files, so updating to support these even when targeting .NET Framework will be a big step forward. The main hurdle so far has been the install script and build tasks which depend on the older packages.config style NuGet implementation.
  • Support for the .NET 5+ generation of runtimes. I’ve made some progress towards the new add-in hosting code required for loading the .NET 5+ runtime, but getting to an implementation that can be supported for the future and work together with existing add-ins still needs a lot of work. The .NET 5+ runtime versions have more limited isolation capabilities, and different versions cannot be loaded side-by-side in the process. This might require us to standardise on a specific version like the .NET 6 Long Term Support release as the single supported .NET 5+ runtime target.

Thank you for your ongoing enthusiasm for Excel-DNA and the super-power combination of .NET and Excel. I look forward to your thoughts and feedback.

-Govert

Posted in Uncategorized | Tagged , , | Comments Off on Update v 1.5 / Sponsors / Roadmap

Excel-DNA version 1.1

Excel-DNA version 1.1 is now available on NuGet and as a direct download from GitHub. The easiest way to install is with Visual Studio’s NuGet package manager (package ExcelDna.AddIn) – a ‘ReadMe’ file with further instructions will then be displayed. From the Package Manager Console:

Install-Package ExcelDna.AddIn

Excel-DNA 1.1 is expected to be the final version to support older .NET (< 4.5) and Excel (< 2007) releases.

Excel-DNA version 1.1 implements workarounds for two recent changes in Excel behaviour:

  • RTD servers based on ExcelRtdServer, and streaming functions based on IExcelObservable stopped updating after recent (early 2020) Excel updates.
  • When loaded into an elevated Excel process (running As Administrator) the on-demand COM registration (used for ribbon and CTP loading) started failing (mid 2020).

The update also introduces strong-naming of the Excel-DNA assemblies (thanks to @augustoproiete)

Please post any issues you run into to the Google group (https://groups.google.com/forum/#!forum/exceldna)

To make a donation to the project, or to arrange for a corporate support agreement that lets you steer the future of Excel-DNA, please visit the Excel-DNA Support page.

Thank you for your continued support and enthusiasm towards the Excel-DNA project!

Posted in Uncategorized | Tagged | Comments Off on Excel-DNA version 1.1

Getting started (or: Why Are You Still Using VBA?)

I discovered a really nice write-up on “Excel-DNA Or Why Are You Still Using VBA?” by Efrem J. Sternbach. He  gives a succinct one-page introduction to various Excel-DNA features including the ribbon integration and COM interop. A great start if you’re curious about Excel-DNA and still using VBA :-)

Posted in Uncategorized | Comments Off on Getting started (or: Why Are You Still Using VBA?)

Excel-DNA version 1.0

Excel-DNA version 1.0 is now available on NuGet and as a direct download from GitHub. The easiest way to install is with Visual Studio’s NuGet package manager (package ExcelDna.AddIn) – a ‘ReadMe’ file with further instructions will then be displayed. From the Package Manager Console:

Install-Package ExcelDna.AddIn
The Excel-DNA 1.0.x series (this version with minor fixes) will be the final version to support older .NET (< 4.0) and Excel (< 2007) releases.

Version 1.0 is essentially the same as the long-overdue version 0.35. The update includes a number of bug fixes as well as improvements to the build-time Visual Studio integration:

  • Improve build tasks – more reliable clean-up and debugger detection
  • Improve RTD and async QueueAsMacro reliability
  • Improve install process of ExcelDna.AddIn NuGet package (now requires NuGet 2.5) (thanks to @caioproiete)
  • Change how ExcelDnaUtil.Application works in Protected View – try harder but don’t cache
  • Call UnhandledExceptionHandler for macros (ExcelCommands) too
  • Add XML schema for .dna file (thanks to @caioproiete)
  • Add option to pack .pdb files (thanks to @lanfeust69)
  • Fix exception handling from native async functions (thanks to @ittegrat)
Please post any issues you run into to the Google group (https://groups.google.com/forum/#!forum/exceldna)

To make a donation to the project, or to arrange for a corporate support agreement that lets you influence the future of Excel-DNA, please visit the Excel-DNA Support page.

Thank you for your continued support and enthusiasm towards the Excel-DNA project!

 

Posted in Release | Comments Off on Excel-DNA version 1.0

Develop Excel Conference – London, 18 October 2018

Charles Williams of FastExcel fame is hosting a one-day conference in
London on 18 October, covering a wide range of Excel extensibility topics.

I will be presenting Excel-DNA, joining developers responsible for PyXLL,
Planatech XLL+, the new Javascript interfaces from Microsoft, and more.
Anyone interested in advanced Excel extensibility will get great insights
into the present and future of developing Excel-based solutions.
Please join us!

This is a community organized pay-if-you-can event, with contributions helping to defer speakers’ travel expenses.

Find all the details and registration info at:
https://developexcel.wordpress.com

 

Posted in Uncategorized | Comments Off on Develop Excel Conference – London, 18 October 2018

Excel-DNA version 0.34

Excel-DNA version 0.34 has been release and is now available

Please post any issues you run into to the Google group (https://groups.google.com/forum/#!forum/exceldna)
The easiest way to test the new version is by installing (or upgrading to) the ‘ExcelDna.AddIn‘ NuGet package.

Excel-DNA version 0.34 introduces a much improved build procedure for add-ins created using the NuGet package (thanks to a fantastic effort by @caioproiete !)

This replaces the error-prone post-build steps we had with a custom build helper and allows easier build output customization (see https://github.com/Excel-DNA/ExcelDna/wiki/Build-Output-Customization).
Various bug fixes and smaller improvements are also included in this version:
  • Add ExplicitExports=”false” to NuGet .dna file template
  • Fix getting Application from ProtectedViewWindow
  • Add attempts to get Application object from all windows of class EXCEL7.
  • Fix ExcelAsyncUtil.Observe re-open restart – broken by other fixes in the previous version. Add option to not restart.
  • Change ExcelRtdServer.ConnectData to be more careful about raising an update notice. Calls to Topic.UpdateNotify during the ConnectData overload are now always ignored. If the topic value is updated (through Topic.UpdateValue) during ConnectData, and the same value is returned from ConnectData, then no spurious UpdateNotify is raised. If the value returned from ConnectData differs from Topic.Value, UpdateNotify will still be raised.
  • Allow AccessViolation exceptions to be caught under .NET 4.0 – change marshaling wrapper from DynamicMethod to MethodBuilder.
  • Fix QueueAsMacro failure after paste live preview.
  • Fix AssemblyResolve re-entrancy race condition.

To make a donation to the project, or to arrange for a corporate support agreement that lets you ensure Excel-DNA will live on, please visit the Excel-DNA Support page.

Thank you for your continued support and enthusiasm towards the Excel-DNA project!

Posted in Uncategorized | Comments Off on Excel-DNA version 0.34

Excel UDF IntelliSense for Excel-DNA and VBA

I’m happy to announce the first official release of the IntelliSense extension!

Excel-DNA IntelliSense provides on-sheet help for UDF functions as they are entered into a cell formula, similar to the help available for built-in Excel functions.

intellisense1intellisense2

For Excel-DNA add-ins (v0.32 and later) that already provide descriptions in the [ExcelFunction] and [ExcelArgument] attributes, no extra work is needed.
Just download and open (or install) the latest ExcelDna.IntelliSense.xll add-in from the GitHub (https://github.com/Excel-DNA/IntelliSense/releases), and the IntelliSense will light up. (There is also a NuGet package for embedding the service into your add-in, making distribution a bit easier.)
 
For VBA functions, you can add an extra sheet with the IntelliSense descriptions, or add an external .xml file with the information, or embed as a the CustomXML part in the Workbook or .xlam add-in.
Then open (or install) the ExcelDna.IntelliSense.xll add-in to provide the display service. Charles Williams, of FastExcel fame, has a detailed write-up on adding IntelliSense for your VBA function – see https://fastexcel.wordpress.com/2016/10/07/writing-efficient-vba-udfs-part-15-adding-intellisense-to-your-udfs/.
 
For PyXLL users, the latest PyXLL 3.1 release offer built-in support for IntelliSense with the ExcelDna.IntelliSense.xll add-in installed. See https://enthought.pyxll.com/whatsnew.html#intellisense.
 
Other native .xll add-ins can also provide IntelliSense through an external .xml file.
 
Details and downloads are on GitHub:
* Detailed Usage Instructions: https://github.com/Excel-DNA/IntelliSense/wiki/Usage-Instructions including details for incorporating the library into your own add-in for easier distribution.
 
Public support and bug reports:
The Excel-DNA Google group (https://groups.google.com/forum/#!forum/exceldna) is the best place for general questions, comments etc.
Detailed bug reports and feature requests can be added to the GitHub issues list: https://github.com/Excel-DNA/IntelliSense/issues
 
Corporate support and private donations:
If you find Excel-DNA and extensions like the IntelliSense service useful, please support the project by arranging a corporate support agreement, or making a donation via PayPal. See https://excel-dna.net/support/ for details and contact information.
Posted in Uncategorized | Comments Off on Excel UDF IntelliSense for Excel-DNA and VBA

Add-in spotlight: ACQ for interpolation

This is the first in an occasional series of posts about interesting Excel-DNA based add-ins.

Alex Chirokov’s ACQ add-in provides a library of interpolation routines for Excel. The add-in includes 1D and 2D interpolators, scatter plot smoothing and a Mersenne Twister random number generator.

To have a closer look:

Features I like about the add-in (apart from it using Excel-DNA) include:

  • A liberal open-source license
  • A clear and authoritative implementation of a particular domain
  • Very nice example of using object handles – an interpolator is build from the data, and then used to interpolate many values. ACQ has a clean implementation and great example of this technique.
  • All the functions have a common prefix (“=acq…”), making them easy to find in the function list, and use with the Excel-DNA IntelliSense extension.

Thank you for publishing a great add-in, Alex.

PS: ACQ has a bonus feature that implements a Sudoku solver (and generator)! See the write-up here: Sudoku Solver in Excel using C# and Excel-DNA

Posted in Uncategorized | Comments Off on Add-in spotlight: ACQ for interpolation

Excel-DNA 0.33 Release Candidate and License Change

Version 0.33 Release Candidate

Excel-DNA 0.33 contains a number of bug fixes and improvements, including a diagnostic logging approach based on the .NET Trace classes. More details can be found in the current ChangeLog.

This version will also be the foundation for a first release of the custom Registration extension and the ongoing work towards on-sheet IntelliSense for user-defined functions.

A release candidate for the new version is available

Please help me test that the new version works correctly in the many different ways, Excel and Windows versions, and languages where Excel-DNA add-ins run.

If you run into any unexpected behaviour bugs or regressions, please post to the Google group or contact me directly.

Also, if you are able to confirm that the new version works in a particular setting, please post that too. Details about what functionality you’ve tested and what operating environment (including .NET and Excel version) you are running with, would help me a lot.

License Change

For the Excel-DNA project, I’ve changed to the standard MIT license. This has become the most common open-source license aligned with my intention of making Excel-DNA free for all use, including commercial use.

If you have any concerns with this change, please let me know.

NuGet Packages

With this version, I am re-aligning the Excel-DNA package names on NuGet with the assembly names and standard naming conventions. The main packages for this release will be:

  • ExcelDna.AddIn – Includes the .xll and creates a complete add-in when installed into a Class Library project. This is update of the “Excel-DNA” package.
  • ExcelDna.Integration – Containts only the integration reference library, suitable for referencing in third-party libraries that are intended to be used in Excel-DNA add-ins. An update of the “Excel-DNA.Lib” package.

The old packages will be updated to refer to the new ones as dependencies, which should allow package updates to work correctly.

GitHub

The Excel-DNA project is (slowly) moving to GitHub.

  • The core library project can be found at https://github.com/Excel-DNA/ExcelDna, where the latest source versions are hosted.
  • The best documentation and links to related projects and other source is still found on the old CodePlex site.
  • For general questions and discussion about Excel-DNA, please continue use the Excel-DNA Google group.
  • Specific issues, bug reports and feature requests can be added to the GitHub Issues list.
  • For a permanent book-mark to the project, please use the Excel-DNA home page at http://excel-dna.net.

Thank you for your continued support of Excel-DNA!

Posted in Uncategorized | Comments Off on Excel-DNA 0.33 Release Candidate and License Change