Tag Archives: addins

Optional passed by reference parameters with C# for VBA COM APIs

Posted on by
Reply

Introduction

If you’ve already developed COM APIs with .Net, typically in C# with VBA as the consuming language, you’ve probably leveraged two powerful features:

  • by-reference parameter passing that allows the API to change the input object itself, not only its content.
    This is marked with ref in C#:

    void LoadData(ref string data)
  • optional parameters that allow the caller to omit them when calling into the API: 
    void SaveData(string data = "DEFAULT")

Sometimes you want to have both for a parameter: the ability to omit it and to change it.
This is something COM supports but unfortunately this is not supported by C#.
Recently one of my customer needed precisely that for a COM API developed in C# migrated from VB6 where it was plug-and-play.
The original developers of the API had done a quick study and concluded that it was not possible with the new C# API and indeed it was not documented at all.
From experience I know that COM is not used a lot today and that resources are scarce, so, to be sure, I’ve done my due diligence and reexamined the issue, digging a little deeper and testing undocumented stuff.
I’ve finally found a solution that I would describe as a “trick”, because it’s not documented, and that I’ll present in detail in this article.
Continue reading →

Leverage the .Net framework classes from VBA

Posted on by
9

Introduction

Following my previous article on a similar subject, Extend your VBA code with C#, VB.Net or C++/CLI, I’ve received an interesting feedback from a VBA developer who wanted to leverage the advanced support of the .Net framework for regular expressions.

This is clearly another interesting use-case for Excel addins and in this article I’ll quickly demonstrate how to build a wrapper which will close the gap between your VBA code and the .Net framework.

Continue reading →

C# : scope your global state changes with IDisposable and the using statement

Posted on by
3

Introduction

Sometimes we need to modify some global state, but when we do that it’s critical to ensure we leave things as we’ve found them; you know like when you lift the toilet seat: forgetting to put it down afterwards could get you in trouble!
It’s similar in programming and you need to use the right tools and practices.

If you regularly work with C# it’s very likely you’ve already used the IDisposable interface along with the using statement, two tools that help you scope the use of a resource.

In this article I’ll show you how they can be used to scope global state changes in a fluent manner, a pattern I’ve used for years to enhance reusability and readability of this kind of code.
In the first part I’ll illustrate this pattern with a small and hopefully fun example, and then in the second part I’ll describe how it can be applied to other situations like culture settings and Excel development.
Continue reading →

Extend your VBA code with C#, VB.Net or C++/CLI

Posted on by
5

Introduction

If you have an important VBA code base you know how difficult it is to maintain it, not because VBA is inherently a bad or poor platform but because the developers are often either end-users not aware of good development practices or professional developers not skilled enough in VBA development who have learnt it on the job. In both cases, more often than not, you end up with poor quality source code.

There is other forces that make VBA development less appealing, as the pressure of software vendors like Microsoft who’d like to sell you not just Office but Office+Visual Studio and then promotes other technologies like .Net with languages such as C# and VB.Net. Just have a look at the evolution of the VBA editor design and capabilities since 10 years and you’ll understand that it does not benefit from fanatic development and promotion efforts.

It’s why you should avoid the headlong rush and restrict your VBA development efforts to the bare minimum: for new developments you should consider other languages and platforms like C# and VB.Net with the .Net framework as they seamlessly integrate with the Office suite, with little overhead compared to the direct integration of VBA, and give you access to a wealth of modern technologies.
But don’t be fooled by the FUD about the dangers of keeping a legacy VBA code base and do your due diligence: does the guy who suggest you a full migration will do it for free or is he paid for the job? 😉 A full migration may be a necessity: not because the platform is VBA but because the application is buggy, undocumented, out of control and using it creates a true operational risk, and this is true for any application and technology including the newest.

Then, if you have a VBA application that is working perfectly, is documented and controlled, an alternative to both the headlong rush and the full migration is the integration of .Net components with VBA, you then use a future-proof technology to develop new features and/or replace legacy features as you go along, avoiding the big-bang effect.

So now you know what to do and right now I’ll show you how to do this seamless transition from VBA to greener pastures by implementing a simple API with three popular .Net languages: C# (the king), VB.Net (the prince) and C++/CLI (the Jack of all trades, master of none).

Continue reading →

Excel addins troubleshooting guide

Posted on by
46

Introduction

So you have deployed your awesome Excel addin in production and all is working fine for some weeks : you are enjoying your success…
But one day the phone rings and the guy at the other side is not really happy; no, he is completely upset and you could almost smell his breath when he screams.
But why? Because your awesome addin has stopped working on his workstation without notice.

Sometimes the root-cause is obvious: you have delivered a new version, but in many cases you will hardly ever know the root-cause of this annoying situation: Windows update, Office update, quick and dirty moving/installation of the addin from a workstation to another one, bad alignment of Uranus and Jupiter…

A wealth of errors can happen, then it’s hard to have them all in mind, especially when you are in a hurry and with stress you start to get mixed up. So to be as efficient as possible you’d better have a checklist and this is precisely what this article will be, enumerating all the errors I have encountered describing usual causes and solutions.

So let’s troubleshoot!
Continue reading →

Excel UDF library in C# from scratch

Posted on by
28

Introduction

In this article I’ll show you how to create a UDF library for Excel in C# from scratch.
By “from scratch” I mean without using Visual Studio, only low-level tools : a simple text-editor like Notepad and the C# compiler.
This is of course not the way you’ll do it usually but it will hopefully help you to better understand how things work under the hood.

The pretext for this sample is a set of functions that provides financial data like the last bid and ask prices for a stock.
It uses the Yahoo finance REST API which is rich and simple and that you could use as a base for developing more advanced tools.
Continue reading →