Skip to main content

VBA versus .NET

Adapter or Wrapper

This is code possible through .NET, in that it bridges across to COM for automation in VBA.  This concept can be extended to work for many other COM-based applications.  This code allows the add-in to expose internal .NET-coded procedures to Excel COM, extending the use of the .NET code.

Salient Characteristic(s)

  • Handle interface between different, incompatible systems
  • The interface is required because COM is interface-based
  • Other code elements are required for this to work properly
    • Registering the DLL
    • Creating the AdapterForVBA class on startup
Code

using System;
using System.Runtime.InteropServices;

namespace DesignPatterns
{
    /// <summary>
    /// Interace to expose VSTO/COM obects for COM and Excel
    /// Used by class below
    /// </summary>
    [ComVisible(true)]
    [Guid("B523844E-1A41-4118-A0F0-FDFA7BCD77C9")]
    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface IAdapterForVba
    {
        //signatures for methods acessible to VBA go here
        //exists here and in class
    }

    /// <summary>
    /// Class to expose items to VBA and other COM application
    /// </summary>
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.None)]
    public class AdapterForVba : IAdapterForVba
    {
        public AdapterForVba()
        {
            //methods to expose go here
            //exists here and in interface
        }
    }
}

Comments

Popular posts from this blog

VBA versus .NET

I was recently messaged by someone on LinkedIn, and since my response seemed full enough, I thought I'd share. Question I see that you also program in VBA but you have made the jump to .NET. Unfortunately, I have found C#/Excel coding to be quite slow and just wanted to hear about your experiences. Responses Slow? It depends on what you mean. Honest, I have had to make the pitch when building apps that it should be in .NET rather than VBA for speed. One particular app had a form that needed to fill about 20 dropdowns on load, so using async operations was essential. That same app, while executing one SQL statement in the foreground, also executed 2 background statements that filled panels. It wouldn't have performed well if done in VBA. If you mean that it takes longer, then yes, but that is a necessity for good code anyway. If you only need a local operation, non-threaded, that doesn't need to be used across the enterprise, VBA can make sense, but with .NET comes n

Do Algorithms Make You a Better Developer?

Responding to a question on HashNode, Developers who practise algorithms are better at software development than people who just do development. Is it true? , I wrote the following: My feeling is that algorithms help make one a better programmer, but that is likely true of many coding concepts. I did not have algorithms as an undergraduate, so my knowledge is acquired through reading and practice, but after reading and applying Algorithm's in a Nutshell, I felt the quality of my work improved. That said, my development work increased more after understanding Design Patterns, or after consuming books on database design.  Since many types of knowledge improve developing and architecting abilities, one has to consider how it helps and to what degree. Algorithms are coding-in-the-small, often narrowly focused solutions, but which can have a great impact at scale. For many applications, a focus on algorithms would be overkill as data sets and requirements do not require it. In this

Multiton

A fairly simple example of the multiton pattern, with a private constructor, and a tracker for created objects, the name being an integer identifier. Salient Chacteristic(s) A private keyed list for tracking objects A private constructor Named objects Code using System.Collections.Generic; using System.Linq; namespace DesignPatterns {     public class Multiton     {         //read-only dictionary to track multitons         private static IDictionary<int, Multiton> _Tracker = new Dictionary<int, Multiton> { };         private Multiton()         {         }         public static Multiton GetInstance(int key)         {             //value to return             Multiton item = null;                          //lock collection to prevent changes during operation             lock (_Tracker)             {                  //if value not found, create and add                 if(!_Tracker.TryGetValue(key, out item))                 {