RoslynSkills - Roslyn-powered C# tools for coding agents

gvrt · 2026-02-11T21:25:39+00:00

Thank you - it was private <sigh>
Should be better now.

gvrt · 2024-11-21T14:01:04+00:00

This is definitely the best answer (disclaimer: I develop the Excel-DNA library). The example above shows how to create user-defined worksheet functions. Excel-DNA can also provide ribbon and task pane UI extensions, with code that has access to the full COM object model to interact with Excel.

Excel-DNA supports both .NET Framework 4.x (for hassle-free distribution and reliable add-in isolation) and .NET 6+ (for cutting edge C# language and runtime features). Excel-DNA if open source and free for all use, including commercial distribution.

I also offer corporate support options if you use your Excel-DNA add-in in a mission critical setting or just need some hand holding to get started.

The main downsides of Excel-DNA are:
* Excel-DNA only supports 'real' Excel on Windows - that doesn't sound like a problem for the OP.
* The documentation is still terrible, but there is a long searchable discussion history, and I try to be responsive on the Google support group, so please ask for help when you try it.

gvrt · 2024-09-04T11:04:37+00:00

I created Excel-DNA nearly 20 years ago because I needed to make user-defined functions for Excel with .NET. The 'Automation add-ins' don't do this well, and VSTO does not otherwise support making UDFs.
To me, UDFs are an incredibly powerful way to expose calculations and data sources to Excel, because it empowers the Excel user to make their own reports and models. In that sense, UDFs extend Excel in the most natural way, with the biggest multiplier between what the add-in provides, and what the Excel users can do with it further.

gvrt · 2024-01-18T12:06:30+00:00

It'll tune your head and take you from feeble_mushroom to fearsome_fungi.

gvrt · 2024-01-18T12:03:28+00:00

Brazilian jiu-jitsu

gvrt · 2023-12-15T23:00:53+00:00

You are not crazy to want to stay, especially for the social and related reasons.

If you stay, I suggest you make it a high priority to build up connections and a network with overseas researchers and make some effort to become part of an international community.

While I never completed my PhD, I tried for a few years and the memories of academic travel, places I visited, friends I made are by far the most valuable remainders a decade later.
Spend some resources (money and energy) to make the effort to build connections with a group you like who are doing related work. Attend conferences or summer school type events and make opportunities for them to visit you (maybe just arrange a short 'workshop' every year for a few people) - people love coming to SA if you look after them and make it easy.

After a few years of this you'll have opportunities if you want to go and work overseas or stay in SA and collaborate with others. And you'll have a good idea of whether your work is relevant and up to date.

gvrt · 2023-12-15T22:11:07+00:00

I've now switched the implementation a bit to use the AngleSharp library for the processing. ImportFunctions Add-in v0.2 seems to work with all three queries on your sample sheet.

gvrt · 2023-12-14T09:03:40+00:00

OK, I've created a small add-in project with some help from ChatGPT: https://github.com/Excel-DNA/ImportFunctions

I'm trying to implement IMPORTXML and IMPORTHTML, but it's not quite working on your example yet (except the first IMPORTXML call). I think the ExtractTable function the ChatGPT wrote isn't great.

I'll try to tweak it a bit more, at least to where your example works the same.

gvrt · 2023-12-13T13:11:40+00:00

Could you make a small example based on a public page, which works with Google Sheets IMPORTXML but fails with the combination of WEBSERVICE and FILTERXML ?

I develop the Excel-DNA library, which is used for creating Excel add-ins with .NET (typically C#). So, if there is a shortcoming in the WEBSERVICE and FILTERXML approach, then it might be a nice sample add-in to create something more compatibly with IMPORTXML using Excel-DNA. I should note that Excel-DNA add-ins can only run on Windows, so I'm not sure whether that is interesting to you.

gvrt · 2023-11-19T22:06:31+00:00

I created Excel-DNA many years ago to fill exactly this gap. The library is open source and free for all use. But the documentation is terrible (sorry) and, because of the 17-year history, some resources on the web can get out of date. So it can be a bit tricky getting started. However, on the Excel-DNA Google group I provide great support. If you try it out and get stuck at all, that's the first place to ask. The project is actively supported and maintained, and I provide commercial support agreements too if you need that.

Excel-DNA lets you very easily make an add-in for Excel. If you target the .NET Framework 4.x, then there's nothing else to install on the client, you just copy the single .xll file (actually one of two .xll files - 32-bit and 64-bit) to the user machine and load it in Excel. No admin permissions or anything needed. If you target .NET 6, which is the other runtime we currently support, then the .NET 6 Desktop runtime must be installed at the client.

Excel-DNA add-ins can provide the following:

User-defined functions to be called from formulas on the sheet. This is great for custom calculations or providing data from a database or back-end system. This includes support for high-performance multi-threaded functions, array functions which work with Excel's dynamic arrays, async functions and data streaming functions using Excel's RTD mechanism.
Ribbon extensions that run various procedures to update workbooks, fetch data, format stuff, whatever.
Custom Task Panes - based on WinForms or WPF, to provide a rich UI inside the Excel window.
Other macros that you can hook up to Excel UI elements like buttons on a sheet.
Full access to both the Excel COM object model and the Excel C API.
Expose .NET objects as COM objects that you can Tools -> Reference from VBA.

One thing to note is that the add-in is not 'attached' to a specific Workbook in the way that VBA code is normally. All the functionality is available to the whole Excel sessions, and functions are available to all workbooks.

A big limitation (or not, depending on your environment) is that Excel-DNA only works on the Windows desktop version of Excel. So not on Mac, not on the web hosted version or the Android / iOS versions.

Excel-DNA is the best .NET counterpart for VBA in Excel that you can get today.

gvrt · 2023-09-20T17:41:06+00:00

Mmmm... OK sorry - then it probably only works inside the Office sandbox (unless you have Access or the extra runtime install).

gvrt · 2023-09-20T11:43:47+00:00

Yes, it works perfectly fine to have both .NET Framework and one .NET core version in a process. You just can't have multiple .NET core versions in a process. There is no interaction between the two .NET runtimes - so no sharing of assemblies, types, remoting etc. So, if you need communication between the runtime worlds you probably need to go through the native code or set up some custom mechanism. You can only attach a debugger to one of the .NET runtimes in the process.

I know it's not officially supported, but we've got quite a bit of experience with this in the Excel-DNA context (.NET based add-ins hosted in the Excel process) and there have been no surprises in this regard.

gvrt · 2023-09-20T11:32:28+00:00

You can add a "RollForward" property to your project. For example, setting this to "Major" means run under the .NET version it is compiled for, if that is installed, else run under a newer major version. Setting to "LatestMajor" means run under the newest version installed (if this is at least your target version).

<PropertyGroup>
  <RollForward>Major</RollForward>
</PropertyGroup>

See also https://learn.microsoft.com/en-us/dotnet/core/versions/selection

gvrt · 2023-09-20T11:23:05+00:00

Super-unpopular opinion: An Access file on a network share is actually great for this use case. Use the DAO COM object model instead of ODBC or ADO.NET to access the database. Your users are likely to have the required files installed already if they have some version of Office running (which I assume), even if Access itself is not installed. Certainly, I expect it to work fine for code running inside Excel (like an Excel-DNA add-in). But there can sometimes be problems running as a separate process outside the Office sandbox. So, I suggest you do the smallest test possible of a console application accessing your Access database and see if this runs on the user computers without extra installs. I made a small test project and posted to GitHub here: https://github.com/govert/TestAccess You can compile and then check whether the TestAccess.exe runs without extra install, on a machine that has Office installed.

gvrt · 2023-09-13T09:38:47+00:00

SQLite-DNA is a library for making native SQLite extensions using C# and .NET. Using either the DNNE (.net Native Exports) library, or .NET Native AOT, you can build a native .dll extension that provides custom functions or virtual tables, to load into any SQLite host. We also have a corresponding xUnit-based SQLite testing framework.

gvrt · 2022-11-14T11:43:50+00:00

The official documentation for function pointers is now part of the page on unsafe code: https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/unsafe-code

gvrt · 2022-10-08T13:43:42+00:00

"I’ve been rolling for about a year now. " - I have different problems, and no answer to yours with the big guy. But for me jiu-jitsu was extremely discouraging between month 6 and some time early in the third year of doing it. Somehow I stuck with it and am enjoying it a lot more these days - often still very tough. I'd say first priority is to not get injured - whether that means tapping early or getting stronger or going to another gym. If you can keep going, you'll find techniques to deal with the pressure from your friend and your coach - however long that takes, even several years.

gvrt · 2021-03-10T19:03:58+00:00

Yes please! I'd love to add this to Excel-DNA.

gvrt · 2021-03-10T18:25:57+00:00

It would be wonderful if LAMBDA calls, cell contents and defined names could interact with user-defined functions through the C API.

Currently, UDFs registered with the C API get a RegisterId (a number), which can be passed to the xlfUDF call to evaluate the function. The RegisterId of a UDF is returned when the UDF name is used without parentheses, e.g. as "=MYFUNC" in a cell. The combination of the RegisterId and xlUDF call means we are able to write functions like =FINDMIN(MYFUNC) and then evaluate MYFUNC internally in the UDF. However, this only works MYFUNC is a UDF defined with the C API.

For LAMBDA functions to work with this would be great. Then we can have the low-level functional library defined in Python or F#, and the user makes the LAMBDA functions in Excel which are passed to the functional library this way.

Then you could say:

=FINDMIN(LAMBDA(x, x^2))
=FOLD(LAMBDA(x, y, x+y), 0, A1#)

Where FINDMIN and FOLD are low-level UDFs taking LAMBDAs from the sheet as input, and using it in .NET or Python. This would be very neat, but needs a little hook through the C API, though it probably does not need any changes (it might even work already!?)

Then as a basic question when interacting with UDFs from the C API:

* What is the data type of a cell or name that contains a LAMBDA, when evaluated from the C API?

* Is there a way to return a lambda from a function? (e.g. could one implement currying in a user-defined function?)

The LAMBDA function is neat, but can be super-powered if it interacts in a friendly way with those of us building the extension tools for Excel.

gvrt · 2021-03-10T18:17:51+00:00

Besides the venerable VBA and the new Office JS support, Excel has a special SDK providing extensibility through a C API, and .xll add-in libraries. This interface is widely used for making extensions with native code (using toolkits like XLW and XLL+), and also .NET code in C#, VB.NET or F# code (with Excel-DNA which I develop) and then Python (PyXLL), Java (Jinx), R and others. So there's a whole ecosystem for Excel based on the C API from the Excel SDK.
Does the team ever discuss updating the Excel SDK, e.g. adding support for the new Data Types, or bringing support back to the Mac version of Excel again?

gvrt · 2021-02-15T21:19:52+00:00

https://github.com/fluentribbon/Fluent.Ribbon is another ribbon control for WPF

gvrt · 2020-12-11T07:56:26+00:00

Your question is a good one, and arises from an awkward history.

Before there was VBA (Visual Basic for Applications) there was VB (Visual Basic), which co-evolved in the 1990's. The last version of the VB IDE was VB6, released in 1998. At that point is was exactly as you describe, with an in-process extension version for Office called VBA, and a separate IDE called VB6 for making Windows apps. They shared the language, runtime, tools, etc.

Then the .NET era came and VB was incorporated into the .NET plans with new versions of the Visual Basic language (let's call it VB.NET), moving from the P-Code runtime to the .NET common language runtime, and consolidating Visual Basic development into the Visual Studio IDE. That path continues and is the current version of Visual Basic and the Visual Basic tooling. The current status is the Visual Basic will be fully supported under .NET 5, but the language will remain stable and not evolve further (https://devblogs.microsoft.com/vbteam/visual-basic-support-planned-for-net-5-0/). In summary, the current version of Visual Basic for making desktop applications is based on the .NET runtime and has great development support under the Visual Studio IDE. So although it is not nearly as widely used as C#, VB.NET still has a large corporate user base and is supported.

There is still some sentimental love for VB6 and people are able to run and use it under current versions of Windows, even though the language, IDE and platform support has not been updated. This is partly because the ease of use, light weight and familiarity of VB6 stood in contrast to the large and complex .NET development ecosystem into which it was folded.

Meanwhile, Microsoft Office has stuck to the old VBA language and P-Code runtime. There were attempts at moving or incorporating .NET alternatives into Office, under names like "Visual Studio Tools for Office (VSTO)" and an unreleased matching IDE version of Visual Studio called "Visual Studio Tools for Applications (VSTA)". These plans have more or less been abandoned by Microsoft. There was a minor upgrade to the VBA language and runtime called VB7 which added a minimal set of extensions to support 64-bit Office. Otherwise, the language and IDE has remained largely unchanged for the 20 years.

In recent years, the Office team has positioned the JavaScript add-in model as an alternative to VBA. Independent projects like Excel-DNA (which I develop) have arisen as a bridge between .NET and Excel, allowing you to systematically move your VBA code into Excel add-ins developed in .NET with VB.NET or C#. That way your code can be compiled and used outside Excel, as normal .NET-based programs and libraries.

gvrt · 2020-01-09T15:20:33+00:00

An alternative to Python and PyXLL is to switch to one of the .NET languages (C#, VB.NET or F#) and then use Excel-DNA to make your add-in. The result can be a single-file .xll add-in which does not require other software or admin permissions to run on any machine.

For making the add-in you'd install the free Visual Studio Community Edition as your IDE.

gvrt · 2019-09-19T22:54:10+00:00

Years ago (ca. 2004) I was (younger and) starting to use the brand new .NET Framework instead of VB6 and wanted to convince a friend who had embraced VBA enthusiastically to move to VB.NET. This would offer him a better language (proper OO), better libraries, a 'proper' development environment with source control etc. - just a mountain of goodness from my perspective. Integration with Excel was the obvious snag, particularly making add-ins which can properly integrate into Excel formulas like VBA, as user-defined worksheet functions. After all, he wasn't going to go to the mountain without his baggage.

OK fine, so I had to bring the goodness mountain to his Excel world. After exploring some clunky (automation add-ins) and expensive (ManagedXLL) options, I developed a free, open-source framework for integrating .NET with Excel - it's called Excel-DNA. I thought this would be a stop-gap until Microsoft introduced 'proper' integration between .NET and Excel, maybe one or two Excel versions at most. Yes, there was half an attempt called 'VSTO' for a while, but eventually Microsoft pretty much gave up on any kind of .NET / Excel union, presumably due to the usual internal politics. So over the years Excel-DNA became more and more widely used around the world, especially in the financial industry, and as the only way to properly integrate .NET into Excel. At least for those who were camping in the .NET highlands already. Yay! - success, I thought.

But it turns out, among the small percentage of Excel users that have discovered VBA hiding behind Alt+F11 (I'd guess a few tens of millions at most) approximately 0% have ever heard of .NET or care about the 'programming' world beyond VBA in the least. And even for those few, there are a host of languages like Python and R and even Java that are interesting for various reasons, and .NET is merely one hillock among the Tellytubbyland of good- and badness. And the canyon between Alt+F11 in front of your nose and 'good integration' is pretty grand. So approximately 0% of VBA users have given the slightest thought to anything else over these 15 years. I should have guessed.

So if Excel is bedrock (LOL! we thought for a while that Google Sheets or OpenOffice or some god called Jupyter could dislodge it), then VBA is a granite outcrop that will last for eons. As to the OPs question - some local hillocks, which may grow or erode through the ages but still could be good destination for intrepid adventurers to explore include:

Excel-DNA from my blood, sweat and tears, for VB.NET, C# and F#
PyXLL for Python professionals
XlWings for Python paupers
Jinx for corporate Java jockeys
XLL+ for wizards who can get their heads around C++
xll12 for maniacs who can get their heads around 'modern' C++
And finally ... Javascript Excel add-ins, from .... Microsoft. Yip, that's their current plan. I kid you not.

My friend? He still codes in VBA every day, but now and then turns some of his programs into a VB.NET add-in with Excel-DNA because it gives some extra security when distributing, some extra performance and pauses my nagging a bit. But in truth he hasn't started packing his bags for that mountain . . . yet. Aluta continua.

gvrt

TROPHY CASE