VBA - COM - DispId(-5) gives VBA Square Brackets but the argument is passed as a string

So the flurry of posts around DispIds is because of a very interesting question on SO How to use square brackets for a string evaluation in VBA? . I had read in some old COM books that it is an ActiveX Control standard to allow a square bracket syntax, that it requires setting the method's DispId to -5 (hex 0xfffffffb) and that by convention the method name is usually called Evaluate.

I think it's best to start off with some code and then talk about it. We can enable the square brackets for a C# authored component, the code below is a .NET class library with ComVisible(true) is AssemblyInfo.cs and with checkbox 'Register for Interop' checked. You also need to run Visual Studio with admin rights.

using System.Collections.Generic;
using System.Runtime.InteropServices;

namespace SquareBracketsCSharp
{
    public interface IList
    {
        [DispId(-5)]
        object Foo(object idx);
    }

    [ClassInterface(ClassInterfaceType.None)]
    [ComDefaultInterface(typeof(IList))]
    public class CList : IList
    {
        private List m_List = new List();

        public CList()
        {
            for (int i = 1; i < 5; i++)
            {
                m_List.Add(i * i);
            }
        }

        object IList.Foo(object idx)
        {
            int idx2;
            if (int.TryParse((string)idx, out idx2))
            {
                return m_List[idx2];
            }
            else
            {
                return 0;
            }
        }
    }

So the code above creates a short list of square numbers. The method Foo has DispId(-5) which makes it the square brackets method for that class. Note well, that the method does not have to be called Evaluate as written some places. You can change the method's name to Evaluate but it won't help.

Now let's write some client VBA code. Below the code attempts to loop through the list but it fails returning zero for each iteration. Why? Because lLoop gets passed as a string! And you can see that happening if you put a break point in the C# code. So you'll see "lLoop" instead of an integer.

Sub TestFibonacci()
    Dim oList As SquareBracketsCSharp.CList
    Set oList = New SquareBracketsCSharp.CList
    
    Dim lLoop As Long
    For lLoop = 0 To 2
        Dim lTerm As Long
        lTerm = oList.[lLoop]
        Debug.Print lTerm
    Next
End Sub

Wherefore Square Brackets

In the code given above the square brackets are useless for simulating C++/C#/Java square bracketed access to array. They will never be indexers for VBA. So, is there any value at all in this syntax then?

Well there might be use case when an argument itself has many quotation marks; in VBA quotes in strings have to be doubled up making it look a bit ugly so in the Immediate Window we type

?"""He said"",""She said"""
"He said","She said"

To illustrate how we can be liberated from the double quote we can insert the following line of code into the above VBA program...

oList.["He said","She said"]

This isn't a trivial nothing, on many occasions when writing code I have had a need to embed expressions with double quotes, often these expressions are in fact fragments of other programming languages such as SQL or JavaScript. Perhaps another post might illustrate the usefulness of being liberated from doubling double quotes.

DispID(-5) does not work for VBA source

The previous posts on DispID assume the ability to export a class from VBA, and add hidden attribute using Attribute <MethodName>.VB_UserMemId = [0|-4] where 0 denotes the default method and -4 denotes an enumeration factory. Sadly, I could not get DispId -5 to work for a VBA class. So you'll need C# (or C++ ATL) to author components with square brackets enabled.