Replacing VBA with C#?

I spend a fair amount of time writing code in C#, which I enjoy. I occasionally wish I could use C# inside other applications.

One application where I think C# would fit right in is Excel, as a replacement for VBA. VBA is a bit (a lot) behind the times and Microsoft isn’t planning on upgrading it. Instead, they’re offering a sandboxed JavaScript environment called Office Scripts.

I see the appeal of JavaScript for the web version of Excel, but a good replacement for VBA, it is not.

Since Microsoft won’t be doing it, I figured I might have a crack at it. After all, with Microsoft committed to JavaScript, it’s unlikely they will muscle me out of the lucrative C# in Excel market.

So here’s what I came up with:

QueryStorm IDE running a LINQ query over an Excel table
QueryStorm IDE running a LINQ query over an Excel table

“Is that a C# IDE running LINQ queries over Excel tables?” Yep, that’s exactly what it is.

I call it QueryStorm. I started working on it way back in 2014. It originally had just SQL support, and I’ve continued working on it ever since, gradually adding support for C# scripting, custom C#/Excel functions, NuGet support, a debugger, and even an app store!

Let me give you a tour.

The QueryStorm IDE

QueryStorm uses AvalonEdit as its code editor. AvalonEdit is a fancy textbox that can show text with rich formatting, but it doesn’t know anything about the C# language and its syntax. It needs to be told where symbols and errors are located in the text (so it can highlight them), which autocomplete options to offer, etc.

To understand the user’s C# code, QueryStorm uses Microsoft’s C# compiler, aka Roslyn.

Roslyn is a .NET library rather than a separate program. Aside from being able to compile C# code into a dll, Roslyn can tell you all about any C# code you give it.

For example, if you give it some C# code, it can tell you where any errors might be, where each symbol is defined and referenced, what each symbol means, and what auto-complete options to offer at a particular location in code.

Most of the functionality that Roslyn offers is surfaced in the QueryStorm IDE, which enables features such as:

  • Syntax and error highlighting
  • Code completion
  • Code fixes and formatting
  • …and many others
Writing C# code in QueryStorm
Writing C# code in QueryStorm

These features make for a productive and enjoyable code editing experience. It might not be exactly on par with Visual Studio or Rider but it certainly beats the pants off of the VBA IDE.

C# scripting in Excel

The simplest way to explore C# in Excel is to start a C# script which you can do by clicking the “C#” button in the ribbon.

Starting a new C# script in Excel
Creating a new C# script

The scripting flavor of C# allows executing snippets of code and evaluating expressions without all the ceremony of defining namespaces, classes, and a static void Main() method.

Example C# expression and its result
Example C# expression and its result

You can run the script by pressing F5 on the keyboard or clicking the “Run” button in the ribbon.

If the script returns a value explicitly (uses the return keyword), or implicitly (ends with an expression that isn’t terminated by a semi-colon), the result will be displayed in the results grid upon execution.

You can use this scripting functionality as a C# scratchpad (like LinqPad), but the added value is the interaction with the workbook and Excel. This way, we get the benefits of C# and .NET on top of Excel’s native functionality.

Scripts can get a reference to the Excel Application object or the Workbook object using the Resolve<> method.

For example, we can write a script to create a new Excel table with a list of files contained in a particular folder:

using System.IO;
using Microsoft.Office.Interop.Excel;

// get some data
var appDataFolder = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
var files = Directory
	.GetFiles(appDataFolder, "*", SearchOption.AllDirectories)
	.Select(f => 
		new {
			File= Path.GetFileName(f),
			Size= new FileInfo(f).Length

// write to Excel as a new table
Resolve().Run(excel => 
	(excel.Selection as Range).WriteTable(files, "myNewTable");
Writing a List<T> instance as new Excel table” height=”779″ loading=”lazy” src=”” width=”1415″></img><figcaption>Writing a List<T> instance as new Excel table</figcaption></figure>
<p>The Excel API available to the user is the same as in VBA, so anything you can do with VBA, you can do with C#.</p>
<p>In addition, a set of handy extension methods is also available, like the <code>WriteTable()</code> method shown in the example above.</p>
<h2>Linq to Excel tables</h2>
<p>Rather than focusing on Excel concepts like cells and sheets, though, a more interesting thing to focus on is the data. In QueryStorm’s C# scripts, <strong>each Excel table shows up as a strongly typed collection</strong> we can query and update.</p>
<p>For example, suppose we have a table containing a list of cities. For each city, we have its name, population, and the country it belongs to. To find the 3 most populous cities in each country, we could run the following LINQ query:</p>
<pre data-enlighter-group=cities.GroupBy(x => .SelectMany(g => g.OrderByDescending(x => x.population).Take(3)) .OrderBy(x => => x.population) .Select(x => new {,, x.population })
Example LINQ query over Excel table
Example LINQ query over Excel table

ORM for Excel Tables

Notice that column names show up in auto-complete, meaning that somewhere there is a strongly typed class that represents the rows of the table.

Rows have strongly typed properties that correspond to table columns
Rows have strongly typed properties that correspond to table columns

Where does this class come from? The answer is – it’s generated automatically by QueryStorm (using Reflection.Emit).

The generated class lives inside a dll which the script automatically references. Each time the user changes a table, QueryStorm will examine the change and, if needed, update the generated types.

Dynamically generated dll with types for accessing data in Excel tables
Dynamically generated dll with types for accessing data in Excel tables

This functions as an ORM layer on top of Excel tables. For a proper ORM, though, we’d need to be able to specify column data types and table relationships.

By default, QueryStorm will guess the data type for each table column based on the data it contains, and it will not assume any relationships between tables.

However, defining a custom data context allows us to specify column types and table relationships. Adding relationships between tables will cause extra navigation properties to be added to the generated types.

For example, let’s assume we have a “cities” table and a “countries” table. We could modify the column types and add table relationships using the following data context class:

public class WorkbookDataContext1 : WorkbookDataContext 
	public WorkbookDataContext1(IWorkbookAccessor workbookAccessor, ISyncRunner syncRunner)
		: base(workbookAccessor, syncRunner, false)

	// Set up column data types
	protected override void Configure(ITablesConfiguration config)
		config.ConfigureTable("countries", tableView => {
				.ConfigureColumn("Population (2020)");

		config.ConfigureTable("cities", tableView => {

	// Set up relationships
	protected override IEnumerable CreateRelationships()
		// city.Country relationship (each city to 1 country)
		yield return new RelationInfo("cities", "countryName", To.One, "countries", "Name", "Country");
		// country.Cities relationship (each country to N cities)
		yield return new RelationInfo("countries", "Name", To.Many, "cities", "countryName", "Cities");

Once the data context is updated, we can see the new navigation properties in our C# scripts.

For example, let’s try to find how much of a country’s population each city accounts for. Since rows in the “cities” table now have a navigation property that allows us to fetch the corresponding country, this is an easy task:

The custom data context changes the strongly typed classes that represent table rows
The custom data context changes the strongly typed classes that represent table rows

As we specified in the data context definition, the population properties are now of type “int?” and cities now have a navigation property “Country” that makes it easy to navigate to the corresponding row in the countries table.

Updating Excel tables

We aren’t limited to just querying the data in Excel tables. We can also perform updates on Excel tables.

For example, let’s add a new column in the cities table for the share of the country’s population that the city accounts for. First, we must add the column manually in Excel, and then update it through the C# script.

cities.ForEach(c =>
	int? cityPopulation = c.population;
	int? countryPopulation = c.Country?.Population__2020_;

	var shareOfCountrysPopulation = 
		1.0 * cityPopulation/ countryPopulation;
	c.share_of_country_s_popupation = 
        	$"{100.0 * shareOfCountrysPopulation:0.00}%";
Updating the contents of an Excel table's column using C# LINQ
Updating the contents of an Excel table’s column using C# LINQ

Formatting table rows

Lastly, we can update row formatting using the Format() extension method available on IEnumerable objects.

For example, let’s highlight all cities that account for more than 20% of the population of their country.

cities.Where(c => c.share_of_country_s_popupation > 0.2)
	.Format(r => r.Interior.ColorIndex = 35);
Set Excel table row formatting using C#
Updating Excel table row formatting using C#


The data from Excel tables is internally cached, so reading performance is about the same as working with lists of objects in memory, i.e., very fast.

Reading data from large tables, even ones with hundreds of thousands of rows takes just a few milliseconds (except for the first read that populates the cache and can take a few seconds for huge tables).

Formatting, though slower than reading, is also highly optimized and has a processing rate of about 1k rows per second.

Extending Excel functionality with C#

Scripts are mainly used for running one-off queries against data in tables. But we can also use C# to add permanent functionality to Excel itself! We can even publish our extensions to allow other people to use them.

We can extend Excel with the following kinds of elements:

  • Custom Excel functions
  • New context menus
  • New ribbon tabs and controls
  • Keyboard shortcuts

Custom Excel functions with C#

In VBA, we can easily define custom functions for use in Excel formulas. In QueryStorm, it’s just as easy (but more powerful).

To define one or more custom Excel functions, we need to create a project in QueryStorm, write the C# function, decorate it with the [ExcelFunction] attribute, and build the project. At that point, the function will be available for use in Excel.

Creating and using a C# function in Excel formulas
Creating and using a C# function in Excel formulas

For example, let’s create a function called “IsMatch” which will tell us if an input string matches a regular expression pattern:

using System;
using System.ComponentModel;
using System.Text.RegularExpressions;
using QueryStorm.Apps;
namespace DemoProject;

public class ExcelFunctions1
	[ExcelFunction(Description="check if input matches rx pattern")]
	public static bool IsMatch(string inputText, string regexPattern)
		=> Regex.IsMatch(inputText, regexPattern);

Here’s what the new function looks like in Excel:

Validating IP addresses using the new IsMatch method created using Regex
Validating IP addresses using the new IsMatch method created using Regex

The .NET base class library contains all sorts of functionality which might be useful in Excel (like regular expressions), and this is an easy way to make it available in Excel.

And, as we’ll see later on, we can also use NuGet packages in our extensions.

C# functions exposed as Excel functions can:

See the documentation for more details on defining Excel functions with C#.

Adding custom commands to Excel

Aside from custom functions, we can also extend Excel with custom ribbon commands, context menus, and keyboard shortcuts.

For example, suppose we want to add a custom context menu command for converting the texts in selected cells to upper case.

Here’s what the code for that command would look like:

using Microsoft.Office.Interop.Excel;
using System;
using QueryStorm.Apps;
using QueryStorm.Apps.Contract;
using QueryStorm.Tools.Excel;
using QueryStorm.Tools;

namespace Project;

public class ContextCommand1 : ContextMenuCommand
    private readonly IWorkbookAccessor workbookAccessor;

    public ContextCommand1(IWorkbookAccessor workbookAccessor)
        : base(
            caption: "To UPPPER case",
            faceId: 100,
            allowedLocations: new[] { KnownContextMenuLocations.Cell, KnownContextMenuLocations.Table })
        this.workbookAccessor = workbookAccessor;

    public override void Execute()
        workbookAccessor.Run(wb => 
        	var range = wb.Application.Selection as Range;
        	object [,] data = range.GetData();
        	for (int i = data.GetLowerBound(0); i <= data.GetUpperBound(0); i++)
        		for (int j = data.GetLowerBound(1); j <= data.GetUpperBound(1); j++)
        			var val = data[i,j];
        			if(val is string s)
        				data[i, j] = s.ToUpper();
        	range.Value = data;

And here’s what the new context menu command looks like in Excel:

Custom command for converting text to uppercase
Custom command for converting text to uppercase

If you look at the context menu command’s constructor, you might notice that it uses dependency injection to access the current workbook. In fact, QueryStorm apps use dependency injection extensively. More on that in this section of the documentation.

Publishing Extension packages

Once you’ve built the customizations you want, you might want to share them with colleagues or clients. You can do so by publishing your extension package to a repository.

Once you publish an extension to a repository, users who have the QueryStorm Runtime and the URL of your repository can download and use it. More on this in the “Runtime” section of this post.

Automating workbooks

Another thing you can do in Excel with VBA is react to events in the workbook. We can easily do this with C# and QueryStorm too.

To add behavior (in the form of C# code) to a workbook, create a project inside the workbook and add a component class:

Create a project and a component inside the workbook
Create a project and a component inside the workbook

For example, the following component pops up the name of the oldest person in an Excel table whenever the user clicks a specific button in the workbook.

using System;
using System.Collections.Generic;
using System.Linq;
using QueryStorm.Apps;
using QueryStorm.Apps.Contract;
using QueryStorm.Data;
using static QueryStorm.Tools.DebugHelpers;

namespace Project;

public class Component1 : ComponentBase
	private double _number;
	// data-bind to "People" table
	public PeopleTable People { get; set; }
	// handle click of button "Sheet1!CommandButton1"
	public void Test()
		var oldestPersonsName = People
			.OrderByDescending(x => x.Age)
			$"Oldest person is {oldestPersonsName}");

The component uses data binding to access data in an Excel table and defines a method for handling the click event of a button.

Upon building the project, the compiled workbook app is embedded inside the workbook as one or more dll files. The QueryStorm Runtime then detects the workbook app and starts it.

Simple Workbook app via C# (demo)
Simple Workbook App demo

It’s worth noting that workbook apps can also include their own custom Excel functions. The functions defined inside of a workbook are only available in that particular workbook. The same goes for any context menus, ribbon customizations, and keyboard shortcuts that the workbook app defines.

The project system

QueryStorm has its own project system similar to the one in Visual Studio.

Projects that contain Excel extensions are defined at the machine level, while a project that automates a workbook is stored inside the workbook itself.

QueryStorm project system
QueryStorm project system

The context menu contains commands for generating various kinds of files with some basic code scaffolding included.

VB.NET support

One thing worth mentioning is that C# is not the only language that’s supported – VB.NET is as well. When creating a project you can choose which language to use.

Choosing between C# and VB.NET languages when creating a project
Choosing between C# and VB.NET languages when creating a project

This choice is stored inside the project.config file.

The language of the project is stored inside the project.config file
The language of the project is stored inside the project.config file

Support for VB.NET might appeal to users with a background in VBA. Since Roslyn includes support for VB.NET, surfacing it in QueryStorm made sense.

VB.NET code in QueryStorm
VB.NET code in QueryStorm

Note that we can use VB.NET to build extension apps and workbook apps, but there is no scripting flavor of VB.NET in Roslyn, so there are no VB.NET scripts, only C# scripts.

NuGet packages

One of the main advantages of C# and .NET is the ability to use existing libraries and packages from the .NET ecosystem, so supporting NuGet was a must for QueryStorm.

Here’s what adding a package to a project looks like:

Adding and using a NuGet package to a project
Adding and using a NuGet package to a project

The QueryStorm Runtime

Ok, so how does someone use a workbook or Excel extension that you’ve built using QueryStorm?

The main thing they need is the QueryStorm runtime. The runtime allows running workbooks with compiled C# code as well as downloading and running Excel extensions created by QueryStorm.

The runtime is a free 4MB add-in that end users install into Excel. Users can download it from the QueryStorm website.

Downloading the runtime installer
Downloading the runtime installer

The installer performs a per-user installation that does not require admin privileges.

Workbook apps and extension apps are very similar but differ in how they are distributed.

Running and distributing workbook apps

For workbook apps, distribution is simple. The compiled code is embedded inside the workbook itself. The workbook can be distributed via email, network share, cloud storage, etc…

Note that sharing the workbook as an email attachment might not always work, since the workbook contains an embedded dll which some email filters will detect and block for security reasons. Sharing through OneDrive, Dropbox, or Google Drive will work just fine, though.

When an end user opens a workbook that has compiled code inside it, the QueryStorm runtime will prompt the user to allow the code in the workbook to run.

Security prompt before allowing a workbook app to run
Security prompt before allowing a workbook app to run

If the user allows the workbook to run, they will not be asked again for that particular workbook, unless the compiled code is changed in any way.

Check out the docs for more information on workbook app security.

Running and distributing extension apps

Excel extensions, on the other hand, are distributed through the QueryStorm app store. The author publishes their extension package to a (NuGet) server.

End users can pull the extension from there, but they must first register the URL of the repository using the “Extensions” dialog in the “QueryStorm Runtime” tab in the ribbon:

Registering an extensions repository
Registering an extensions repository

Once they do, they will be able to browse and install all extensions on that repository.

The QueryStorm runtime comes with one predefined repository where we (mostly I) publish general-purpose extensions.

For example, I’ve built and published an extension that adds several functions for working with phone numbers.

Here’s how to install and use it:

Installing and using the Windy.Phone package that adds functions for working with phone numbers
Installing and using the Windy.Phone package with functions for working with phone numbers

Currently, authors can’t publish their extensions to this built-in repository (contact me if you would like to). However, you can easily create your own repository and use it to share your extensions with colleagues and clients.

If you would like to share extensions with other users on the same network, a simple shared folder will work nicely. Simply registering the path of the network share is sufficient both for package authors and end users.

If you would like to share your extensions with other people outside of your network, then using a cloud repository like Azure Artifacts would be best. Azure Artifacts has a free tier that offers 2GB of storage which is enough for hundreds of extensions. Setting up an Azure Artifacts repository takes just a few minutes, see this video for instructions.

For more information on setting up repositories, check out this section of the documentation.

Debugging C# code in Excel

If you’re writing more than a trivial amount of C#, you’ll need to be able to debug. There are several ways to debug C# code in QueryStorm, the easiest one being the built-in .NET debugger.

To start debugging a project, press F5.

Debugging C# code in Excel demo
Debugging C# code demo

All of the debugger commands are available in the ribbon, but you can also use keyboard shortcuts which you can configure in the settings dialog:

Viewing and adjusting keyboard shortcuts
Viewing and adjusting keyboard shortcuts

One limitation of the debugger is that you can only use it to debug regular C# code. Debugging C# scripts is not (yet) supported.

To debug C# scripts, you can either fall back on log statements (using the Log() method) or attach Visual Studio to the Excel process and use the Debug() method to act as a breakpoint.

Final thoughts

Excel has a lot of functionality. With the ability to process data and add logic using C#, however, it can become an extremely powerful tool in the hands of a skilled user.

This post only scratched the surface of the possible ways to use C# inside Excel.

If you’re a C# developer, VBA developer, data analyst, or data scientist, I hope that QueryStorm finds its place in your toolbox as your favorite tool.

If you have any thoughts or comments, please leave them below. I’d very much welcome your feedback.

Read More