A while ago, as a hobby and later a business, I made an Excel plugin for using SQL in Excel: https://www.querystorm.com

I've now added the capability to use C# in Excel, using Roslyn. Sort of like Linqpad in Excel. This lets it work with Excel tables, fiddle with formatting, and automate stuff.

Some of the practical applications:

   - processing data in Excel (LINQ)

   - getting data from various sources into Excel (REST, databases, files, active directory, whatever)

   - building interactive dashboards

   - building prototypes applications (you write logic in C# and use Excel as the UI and data storage)

Here's the introduction: https://querystorm.com/documentation.html

There's also a short video: https://vimeo.com/242216594 for a brief intro.

I charge for the plugin but it's freemium. If you don't want intellisense, error squigglies and so on, you don't need a license.

So... what do you think?

It is insane to me that Microsoft hasn't already provided this. I get that you can't replace VBA entirely, or phase it out, without breaking a Lot of Things, and putting a lot of VBA programmers out of work. But every aspect of this being native to Excel would make macro work and data programming easier for a huge segment of the business world.

In other words, I like what you're doing and will probably buy a license. But... I don't see a license option that looks like it lets me test it out in a commercial environment without paying first. I might just be missing something there though. I'd love to test it out and be in compliance with your licensing.

Thanks for the comment! Yeah, VBA in Excel is pretty outdated in many ways, and Excel isn't going anywhere any time soon. I missed having SQL in Excel originally so that's why I started the plugin. I added C# just now, but I really like how C# and Linq fit into Excel, I just love playing around with it.

About licensing: you can get a free trial license by clicking "Try Querystorm" on the QueryStorm website. The trial lasts 14 days, but if you need a longer trial just send me an email (antonio at querystorm.com) and I'll send you an extended trial license.

If you have any suggestions or questions while you're checking it out, let me know.

You actually can do it, it's just hidden and sort of a pain in the ass. You can insert a SQL-linked datatable that can pull parameters from your spreadsheet, if the cell changes the query updates.

The problem is interoperability with older versions of Excel when that new one is released too...

I love it and I hate it at the same time. You would not believe how many people abuse Excel to solve every issue. Entire companies run solely on Excel turned into SAP :-)

QueryStorm: optimizing the way we abuse Excel!:) Well, jokes aside, C# and .NET are a better tool, so at least its easier to make a good solution in Excel. I think Excel is an excellent platform for developer tools. E.g. if you're making a small application or a prototype, you just write code in C# and you have Excel to take care of data storage, data visualization and user input. With formulas and graphs you also get data-binding for free from Excel. Spend 1% of the time for 30% of the functionality you'd need from a proper app.

Congrats! This is fantastic. Now can you do the same with Python please? :) But seriously, great work, including the docs/videos.

Well thanks a lot:) I'd love to support Python, but for now there's not enough manpower to go into it. Glad you like the docs and videos!

I really loved the introduction videos you posted. Great showing of different features in your product - wishing you the best with this project

Thanks, I really appreciate that! Glad the hours I spent preparing the videos and docs didn't go to waste:)

This is absolutely awesome! On a related note, I'm constantly astounded by people who use excel all day but have no interest in getting better at using Excel. I've tried explaining how to use tables and why writing readable formulas is important but they just stare back with a bemused look.

Maybe they don't want a system they cannot audit or debug.

Or maybe their data acquisition system is nuts, but maybe it expanded a job for someone who would otherwise be out of one.

I have encountered situations like these and my suggestions... well, not wrong, but not a good fit for that department.

Cheers! Yep, most people can't be bothered to make their own lives easier. The algorithm is: if it involves anything unknown - bail.

It looks quite amazing. Is there any way to provide som sort of log in form, if data is provided by a protected source?

Of course, the user could just enter credentials in cells, but if I want something more secure. Is there any way to integrate an OAuth 2 log in form or similar?

Yea, you could do it. In the simplest case, you could build a login form (e.g. winforms) in code in your script and use it to get the username and password from the user.

In a more serious use case, you could build your login form and all the login logic in a separate dll, and just call that dll from your scripts.

If you'd like to try this, let me know via email (antonio at querystorm.com), I can help or at least point you in the right direction.

Thanks! Will do.

How about an F# port of this? Aside from it being a very handy language for ETL type tasks, I suspect your average excel power user who otherwise does not program could more easily wrap his head around the functional paradigm.

It's entirely possible. I'll have to see what kind of support Roslyn has for F#. If the support is there, it would be extremely easy to support F#. I'll look into this, thanks for the tip!

For an example for F# perhaps take a look at:

http://fsprojects.github.io/ExcelProvider/

I have two thoughts.

1. This is a huge improvement over VBA.

2. This is going to make creating unmaintainable mission critical Excel macro based monstrosities even easier.

So a small software improvement may be releasing new terrors into the IT world.

Some of those monstrous spreadsheets are extremely valuable, but wouldn’t exist if it weren’t for Excel.

Reminds me of gunpowder - very powerful, very dangerous and very useful.

Yeah, Excel is crazy flexible so it's no wonder that where ever there isn't a designated application for something, people fall back to Excel. I think it's a good platform to introduce dev tools in, all those small ad-hoc use cases can be served much better with some serious dev tools at hand.

Whoops... Well, at least it's also going to make creating maintainable mission critical Excel based monstrosities easier:)

It's been a long, long time since I've tried to do anything with VBA. How far out of date is the implementation of it that is built into Excel? Like .NET 1.1?

Unless something has changed recently, VBA in Excel and other Office applications isn't .NET at all. It's basically just VB6.

When you go to edit VBA inside Excel, you get a VB6 IDE. I just tried it in Excel 2016, and it looks the same as it always has.

Their are ways to develop Office add-ins using .NET, but they aren't built in the way VBA and its IDE are.

> "Their are ways to develop Office add-ins using .NET, but they aren't built in the way VBA and its IDE are."

It's also worth noting that you can build custom tools in Office using web technologies (HTML/CSS/JS).

Also, there are also a number of options other than Excel formulas and VBA for manipulating Excel data, including DAX and M.

https://en.wikipedia.org/wiki/Data_analysis_expressions

https://msdn.microsoft.com/en-us/library/mt270258.aspx

M's syntax is kind of similar to F#/Ocaml. The only downside is the editor (very basic).

All of the above are built into Excel. If I was starting out building an Excel add on, I'd use the web technologies option.

Yeah the VBA in Excel isn't .NET at all. Which makes it even harder to take something that was VBA in excel and trying to utilize it somewhere else (e.g. a .NET add-in to Excel or using any of the Excel interops via something like C#/VB.NET)

I imagine you'd be in for some fun times with COM if you want to call that VBA code from a .NET language.

Does Excel still use the virtual machine they built in Office 6? I think it was the first whack at aligning the Mac and Windows versions. That was hard. We couldn't afford to give everyone more than 8MB of DRAM.

Pretty cool. On the other hand the most miserable time in my career was developing a plugin for Excel. It just went from one disaster to the next.

Yeah, VSTO is a tricky animal. I've had quite a few mysterious issues over the last few years while building this plugin. In my case though, since I'm hosting a WPF user interface in a COM application in a WinForms host, talking to native libraries it wasn't entirely unexpected:)

We're doing the same. I feel like punching myself in the face some days.

"WPF user interface in a COM application in a WinForms host"

That's close to what we had. It was probably the first and only time that a project got me close to breaking down and crying :-)