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

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).

Presentation of the .Net API

The .Net API we’re going to build is a basic market-data provider that uses the Yahoo finance API as its back-end.
The API provides 4 methods:

  • 3 unitary methods: GetBid, GetAsk, GetCapitalization, for retrieving the bid price, ask price and the capitalization of a stock, respectively,
  • a bulk method: GetValues, for retrieving any set of fields (check this list of available fields to have an idea of which data you can get).

The code is minimal, with no error handling, to avoid any distraction.

We’ll write usual C#,VB.Net and C++/CLI code with some COM stuff: the Guid attribute to uniquely identify our COM-visible types and the ClassInterface attribute to control the way the COM interface is generated: “None” means we use the first interface explicitly implemented by the class.

If you don’t want to write any code or command-lines you can download this ZIP archive where I’ve packaged: the source-codes, a CMD script that builds and registers the API, and a demo Excel spreadsheet with VBA code (in the “mYahooVBA module).
You should just have to run the CMD script with administrator privileges from a Visual Studio Command Prompt, open the sample spreadsheet and click the “Load button.

The C# implementation

When it comes to selecting a language for .Net development C# is the default choice if you have no constraints because it was designed and is promoted as the flagship of the .Net framework. As such it benefits from a great development effort and a huge community.

Here is the C# implementation of our API:

using System.Net; // WebClient
using System.Runtime.InteropServices; // Guid, ClassInterface, ClassInterfaceType
using System.Globalization; // CultureInfo

[Guid("97E1D9DB-8478-4E56-9D6D-26D8EF13B100")]
public interface IYahooAPICS
{
    double GetBid(string symbol);
    double GetAsk(string symbol);
    string GetCapitalization(string symbol);
    string[] GetValues(string symbol, string fields);
}

[Guid("BBF87E31-77E2-46B6-8093-1689A144BFC6")]
[ClassInterface(ClassInterfaceType.None)]
public class YahooAPICS : IYahooAPICS
{
    private static readonly WebClient webClient = new WebClient();

    private const string UrlTemplate = "http://finance.yahoo.com/d/quotes.csv?s={0}&f={1}";

    private static double ParseDouble(string value)
    {
         return double.Parse(value.Trim(), CultureInfo.InvariantCulture);
    }

    private static string[] GetDataFromYahoo(string symbol, string fields)
    {
        string request = string.Format(UrlTemplate, symbol, fields);

        string rawData = webClient.DownloadString(request).Trim();

        return rawData.Split(',');
    }

    public double GetBid(string symbol)
    {
        return ParseDouble(GetDataFromYahoo(symbol, "b3")[0]);
    }

    public double GetAsk(string symbol)
    {
        return ParseDouble(GetDataFromYahoo(symbol, "b2")[0]);
    }

    public string GetCapitalization(string symbol)
    {
        return GetDataFromYahoo(symbol, "j1")[0];
    }

    public string[] GetValues(string symbol, string fields)
    {
        return GetDataFromYahoo(symbol, fields);
    }
}

We compile it using the CSC C# compiler:

csc /target:library FinanceCS.cs
Microsoft (R) Visual C# Compiler version 4.0.30319.17929
for Microsoft (R) .NET Framework 4.5
Copyright (C) Microsoft Corporation. All rights reserved.

/target:library” asks CSC to generate a DLL rather than an EXE.

So we now have a “FinanceCS.dll.Net DLL assembly.

The VB.Net implementation

In the .Net world VB.Net is your second choice if for any reason you have discarded C#. VB.Net is easier to grasp for VBA developers and can be an intermediate step on the road to full .Net applications: you won’t be distracted by a new language syntax and can concentrate on the .Net framework itself to get your productivity back quicker.

And this is what our API looks like in VB