Excel-DNA Utility Functions

This repository provides a collection of high-performance, thread-safe User Defined Functions (UDFs) for Microsoft Excel, developed using Excel-DNA. These functions are designed to enhance Excel's capabilities, offering advanced features for power users and developers.

Author: Nicolas Pepin Date: 2025-06 Version: 3.1.1 Licensing: MIT

Overview
Available Functions
Integration with eSharper
C# Version Compatibility
Building and Deployment
License
Appendix A: Excel-DNA Technical Overview

Overview

The UDFs in this collection are implemented in C# and can be integrated into Excel through the Excel-DNA framework. They are particularly useful for tasks that require:

Advanced data manipulation.
Enhanced control over Excel's calculation settings.
Improved worksheet function capabilities.

Available Functions

Summary of Functions

VEXCELDNA()
- Returns the current version of the UDF collection
- Usage: =VEXCELDNA()
- Returns: String with the version number
SETTARGETVERSION(version)
- Sets the target version for backward compatibility
- Usage: =SETTARGETVERSION("2.0.0")
- Returns: Confirmation string with the previous and current target version
GETTARGETVERSION()
- Gets the current target version for backward compatibility
- Usage: =GETTARGETVERSION()
- Returns: String with the current target version
RECALCALL()
- Triggers a full recalculation of the workbook
- Usage: =RECALCALL()
- Returns: "TRUE" on success
GETITERATIONSTATUS()
- Returns Excel's iterative calculation settings
- Usage: =GETITERATIONSTATUS()
- Returns: String with status (ON/OFF), max iterations, and max change
SETITERATION(IterationOn, [maxIterations], [maxChange])
- Configures Excel's iterative calculation settings
- Usage: =SETITERATION(TRUE, 100, 0.001)
- Returns: Confirmation string with current settings
ISVISIBLE([cachingTime])
- Checks if a cell is visible (not hidden by rows/columns)
- Usage: =ISVISIBLE(10) (10 second cache duration)
- Returns: "TRUE" if visible, "FALSE" if hidden
DESCRIBE(cell_reference)
- Returns a description of the cell's content type
- Usage: =DESCRIBE(A1)
- Returns: String describing the value type
INJECTVALUE(cell_reference, value)
- Injects a value into a cell (stateful operation)
- Usage: =INJECTVALUE(B2, "Test Value")
- Returns: The injected value
FINDPOS(text, substring, instance)
- Finds positions of substrings (case-insensitive)
- Usage: =FINDPOS("Hello World", "o", 1)
- Returns: Position number or error if not found
PUTOBJECT(name, value, [force], [debug])
- Stores an object in temporary storage
- Usage: =PUTOBJECT("temp1", A1:A10, TRUE)
- Returns: The stored object
GETOBJECT(name, [debug])
- Retrieves an object from temporary storage
- Usage: =GETOBJECT("temp1")
- Returns: The stored object or error
PURGEOBJECTS()
- Clears all objects from temporary storage
- Usage: =PURGEOBJECTS()
- Returns: "TRUE" on success
TRUESPLIT(input_array, delimiter)
- Splits strings into dynamic arrays
- Usage: =TRUESPLIT(A1:A3, ",")
- Returns: 2D array of split components
ISMEMBEROF(array1, array2)
- Checks for common elements between arrays
- Usage: =ISMEMBEROF(A1:A10, B1:B20)
- Returns: TRUE if any match found
GETTHREADS()
- Returns Excel's current thread count for calculations
- Usage: =GETTHREADS()
- Returns: Integer thread count
SETTHREADS(threadCount)
- Configures Excel's calculation thread count
- Usage:
  =SETTHREADS(4) (Use 4 threads)
  =SETTHREADS(0) (Use all processors)
- Returns: Actual thread count set
HASHARRAY(input_array, [hashLength])
- Computes a consistent hash value for an array of values
- Usage: =HASHARRAY(A1:A10, 8)
- Returns: Hash string (default length 8, range 4–32)
ISLOCALIP(ipAddress_string)
- Checks if an IP address is a local IP (private or loopback)
- Usage: =ISLOCALIP(ipAddress_string)
- Returns: TRUE if local IP, FALSE otherwise or #N/A if invalid input

Integration with eSharper

To simplify the management and usage of these UDFs within Excel 365, this project leverages the eSharper Excel add-in container.

C# Version Compatibility

These UDFs use features from C# 10. Attempting to use syntax from later C# versions may cause compilation errors.

Compatibility Notes:

Excel-DNA supports .NET Framework 4.5.2+ and .NET 6+/8.
eSharper relies on the .NET version available within Excel, potentially limiting newer features.

Building and Deployment

Requirements:

Visual Studio 2022+
.NET Framework 4.7.2 SDK or .NET 6.0 SDK
Excel-DNA NuGet package

Steps:

Clone the repository.
Open in Visual Studio.
Build to generate .xll.
Load .xll in Excel via Add-ins menu.

License

MIT License. See LICENSE file.

Appendix A: Excel-DNA Technical Overview

What is Excel-DNA?

ExcelDNA is a powerful library that allows developers to create high-performance Excel add-ins using .NET languages (like C# or VB.NET). Here's a technical breakdown of how it works:

1. Core Architecture

ExcelDNA bridges Excel's native C API (the Excel XLL SDK) with the .NET runtime. It does this by:

Compiling .NET code into an XLL: An XLL is a DLL specifically designed for Excel. ExcelDNA generates a thin native XLL stub that loads the .NET runtime and hosts your managed code.
Using Managed/Unmanaged Interop: The XLL acts as a bridge between Excel (unmanaged C/C++ world) and .NET (managed world) using P/Invoke and COM Interop.

2. Key Components

ExcelDna.Integration.dll: Provides the core API for registering functions, handling callbacks, and marshaling data between Excel and .NET.
ExcelDna.Loader.dll: Manages the dynamic loading of .NET assemblies into Excel.
ExcelDnaPack: A tool that bundles custom .NET assemblies and dependencies into a single .xll file for deployment.

3. Function Registration

When Excel loads the XLL:

ExcelDNA scans your .NET assembly for methods marked with Excel-specific attributes (e.g., [ExcelFunction]).
It generates Excel-compatible exports (via xlAutoOpen and xlAddInManagerInfo callbacks).
Wraps .NET methods in native XLL-compatible functions, handling type conversion between:
- Excel XLOPER/XLOPER12 types ↔ .NET types (double, string, object[,], etc.).
- Excel arrays ↔ .NET object[,] or double[,].

4. Marshaling & Memory Management

Arguments passed from Excel are converted into .NET types.
Return values from .NET are packed back into Excel-compatible structures.
ExcelDNA manages memory to prevent leaks (e.g., freeing temporary XLOPERs).

5. Asynchronous & Multithreading Support

Excel is single-threaded (STA), but ExcelDNA allows async functions via [ExcelAsync].
Uses .NET Tasks to run computations in the background and return results later.

6. RTD (Real-Time Data) Support

Implements Excel's RTD server interface for push-based real-time updates.
Managed .NET code can push data to Excel cells in real time.

7. COM & Ribbon Integration

If needed, ExcelDNA can expose .NET classes to Excel via COM (for UDFs or macros).
Supports customizing the Ribbon UI via Fluent UI XML.

8. Debugging & Deployment

Works with Visual Studio debugging (attach to Excel process).
Packaged as a single .xll file (no separate installer needed).

9. Performance Considerations

Minimal overhead (~native speed) due to direct XLL integration.
Avoids COM where possible for better performance.

10. Comparison to Other Tech (VSTO, COM Add-ins)

Faster than VSTO (no COM overhead).
Lighter than VSTO (no need for a separate runtime).
More flexible than VBA (full .NET ecosystem access).

Example Flow (Calling a .NET Function from Excel)

User enters =MyNetFunction(A1) in Excel.
Excel calls the XLL’s exported stub.
ExcelDNA marshals arguments to .NET.
Your [ExcelFunction] method runs in .NET.
Return value is marshaled back to Excel.

ExcelDNA essentially makes .NET a first-class citizen in Excel while maintaining high performance and compatibility.

How does it compare with Python-based approaches?

ExcelDNA (for .NET) and Python integration in Excel serve different purposes and have distinct technical approaches. Here’s a detailed comparison:

1. Technical Implementation

Aspect	ExcelDNA (.NET)	Python in Excel
Integration Level	Deep XLL integration (native Excel C API)	Officially supported by Microsoft (via PyXLL, xlwings, or built-in Python in Excel)
Performance	Near-native (minimal overhead)	Slower (Python interpreter + marshaling)
Language	C#, F#, VB.NET	Python
Deployment	Single `.xll` file	Requires Python runtime, dependencies
Concurrency	Supports async via `[ExcelAsync]`	Limited (Python's GIL can bottleneck multithreading)
Real-Time Data	RTD support (push updates)	Possible with PyXLL/xlwings, but slower
Debugging	Easy (attach to Excel process)	Requires IDE setup (e.g., VS Code, PyCharm)

2. Functionality & Use Cases

Feature	ExcelDNA	Python in Excel
User-Defined Functions (UDFs)	Yes (high performance)	Yes (slower, but flexible)
Macros & Automation	Yes (via `[ExcelMacro]`)	Yes (xlwings, COM)
Data Processing	Fast (direct .NET arrays)	Slower (Pandas/NumPy marshaling)
Machine Learning	ML.NET, TensorFlow.NET	Full scikit-learn/TensorFlow/PyTorch
Excel UI Control	Custom Ribbon, WinForms/WPF	Limited (depends on tool)
Cross-Platform	Windows-only	Works on Mac (xlwings)

3. Pros and Cons

ExcelDNA (.NET)

Pros:

Blazing fast (native XLL performance).
Direct access to Excel’s C API (low-level control).
Strong typing (C#/F# reduces runtime errors).
Easy deployment (single .xll file).
Full .NET ecosystem (e.g., parallel computing, databases).

Cons:

Windows-only (no macOS support).
Requires .NET knowledge.
Only works with desktop version of Excel.
Less popular for data science than Python.

Python in Excel

Pros:

Built-in Python in Excel (Microsoft 365): No add-ins needed.
Huge ecosystem (Pandas, NumPy, scikit-learn, etc.).
Better for prototyping (Jupyter-like workflows).
Cross-platform (xlwings works on Mac).

Cons:

Slower (Python interpreter + data marshaling).
Dependency hell (conda/pip environments).
Limited real-time performance (no RTD in pure Python).
Debugging is harder (external IDE needed).

4. When to Use Which?

Use ExcelDNA if:
- You need maximum performance (financial models, real-time data).
- You’re already using .NET/C#.
- You need deep Excel integration (custom UI, RTD, async).
Use Python in Excel if:
- You’re doing data science/ML (Pandas, scikit-learn).
- You prefer quick prototyping (Jupyter-style).
- You need cross-platform support (Mac + Windows).

5. Emerging Trends

Microsoft’s built-in Python in Excel (2023+):
- Runs Python in the cloud (not locally).
- Seamless grid integration (no add-ins).
- Still early (limited libraries, no local execution).
Alternatives:
- PyXLL: Commercial, high-performance Python XLL.
- xlwings: Free, but COM-based (slower).

Final Verdict

ExcelDNA = Speed + Control (best for .NET devs).
Python in Excel = Flexibility + Ecosystem (best for data scientists).

Appendix B: Using Without eSharper

You do not need the eSharper add-in to use these Excel-DNA functions. They can be deployed as standard Excel add-ins using the following steps:

Requirements

Excel-DNA
Visual Studio (recommended) or a text editor
.NET Framework 4.7.2 or later (for compatibility with most versions of Excel)
Excel (2010 or newer recommended)

Steps to Compile and Use the UDFs

1. Create or Use a `.dna` File

Create a file named MyAddIn.dna with the following content:

<DnaLibrary Name="MyExcelFunctions" RuntimeVersion="v4.0">
  <ExternalLibrary Path="MyFunctions.dll" />
</DnaLibrary>

MyFunctions.dll is the compiled output of your .cs code (see next step).
RuntimeVersion must match the .NET version used for compiling the DLL.

2. Compile Your `.cs` Code

Compile your C# file into a class library (.dll). You can do this using:

Visual Studio (File > New > Project > Class Library)
Or with the command line:

csc /target:library /out:MyFunctions.dll Custom-Excel-DNA-UDFs.cs

3. Download Excel-DNA Loader

Download the latest Excel-DNA binaries and place the following in your project folder:

ExcelDna.Integration.dll
ExcelDna.Loader.dll
ExcelDna.xll (rename this to MyAddIn.xll for clarity)

4. Build the `.xll` Add-In

To link everything together, you should have:

MyAddIn.dna
MyFunctions.dll
MyAddIn.xll (copied/renamed from ExcelDna.xll)

Optional: Use the Excel-DNA Pack utility to bundle the .xll, .dll, and .dna into a single file:

ExcelDnaPack.exe MyAddIn.dna

This will create MyAddIn-packed.xll.

5. Load the Add-In in Excel

Open Excel.
Go to File > Options > Add-Ins.
At the bottom, select Manage: Excel Add-ins, and click Go....
Click Browse, find your .xll or *-packed.xll file, and open it.
The UDFs will now be available as native Excel functions.

Notes

Excel-DNA add-ins are fully portable and do not require administrator installation.
You can distribute the .xll or .xll + .dll pair to other users.
No COM registration is needed.
You can sign your .dll for macro security compliance.

Excel-DNA is powerful and flexible, making it ideal for deploying managed-code add-ins without the overhead and complexity of COM registration or VSTO.

ngpepin/Collection-of-CSharp-ExcelDNA-UDFs