How to build a 'SuperDictionary' custom object / Excel-VBA

Question

As a heavy user of dictionaries in vba, I found useful to create a 'super-dictionary' class, that deals with lots of minor issue I don't wanna deal with the main code. Below is a draft of this 'super-dictionary' custom-object.

Is this a good idea? Could this approach affect the performance of my dictionaries in some unforeseen way? (for example is my Get Item method expensive? - I use it a lot)

tks in advance!

Public pDictionary As Object

Private Sub Class_Initialize()
    Set pDictionary = CreateObject("Scripting.Dictionary")
End Sub

Private Sub Class_Terminate()
    If Not pDictionary Is Nothing Then Set pDictionary = Nothing
End Sub

Public Property Get GetItem(Key As Variant) As Variant:
    If VarType(pDictionary.Items()(1)) = vbObject Then
        Set GetItem = pDictionary(Key)
    Else
        GetItem = pDictionary(Key)
    End If
End Property

Public Property Get GetItems() As Variant:
    Dim tmpArray() As Variant, i As Integer
    If Not pDictionary.Count = 0 Then
        ReDim tmpArray(pDictionary.Count - 1)
        For i = 0 To pDictionary.Count - 1
            If VarType(pDictionary.Items()(i)) = vbObject Then Set tmpArray(i) =pDictionary.Items()(i)
            If Not VarType(pDictionary.Items()(i)) = vbObject Then tmpArray(i) =pDictionary.Items()(i)
        Next i
    Else
       ReDim tmpArray(0)
    End If
      GetItems = tmpArray
End Property

Public Property Get GetKeys() As Variant:
    GetKeys = pDictionary.Keys
End Property

Public Property Get Count() As Integer:
    Count = pDictionary.Count
End Property

Public Property Get Exists(Key As Variant) As Boolean:
    If IsNumeric(Key) Then Exists = pDictionary.Exists(CLng(Key))
    If Not IsNumeric(Key) Then Exists = pDictionary.Exists(Key)
End Property

Public Sub Add(Key As Variant, Item As Variant):
     If IsNumeric(Key) Then pDictionary.Add CLng(Key), Item
     If Not IsNumeric(Key) Then pDictionary.Add Key, Item
End Sub

Public Sub AddorSkip(Key As Variant, Item As Variant):
    If IsNumeric(Key) Then
        If Not pDictionary.Exists(CLng(Key)) Then pDictionary.Add CLng(Key), Item
    Else
        If Not pDictionary.Exists(Key) Then pDictionary.Add Key, Item
    End If
End Sub

Public Sub AddorError(Key As Variant, Item As Variant):
    If IsNumeric(Key) Then
        If Not pDictionary.Exists(CLng(Key)) Then
            pDictionary.Add CLng(Key), Item
        Else
            MsgBox ("Double entry in Dictionary: " & Key & " already exists."): End
        End If
    Else
        If Not pDictionary.Exists(Key) Then
            pDictionary.Add Key, Item
        Else
            MsgBox ("Double entry in Dictionary: " & Key & " already exists"): End
        End If
    End If
End Sub

Public Sub Remove(Key As Variant):
    If IsNumeric(Key) Then
        pDictionary.Remove (CLng(Key))
    Else
        pDictionary.Remove (Key)
    End If
End Sub

Answer 1

Very good-looking class as far as I can tell (albeit your VBA-class-building skills are clearly more advanced than mine). All that I might suggest is, if you are fluent in .NET, then recreate it in Visual Studio as a portable class library so that you might leverage the robust functionality of the .NET framework.

1. Create a new "class library" in Visual Studio.
2. Import System, and System.Runtime.InteropServices into your new class.
3. Wrap the class in a namespace.
4. Create an interface for your properties, methods, etc.
5. Go to the "Compile" settings and click the "Register for COM Interop".
6. Build the project--this creates a .TLB file in the project's BIN folder.
7. Add the .TLB file as a reference in the VBA developer environment.

Here is an example of the VB.net code:

Option Strict On
Imports System
Imports System.Runtime.InteropServices
Namespace SuperDictionary
    ' Interface with members of the Super-Dictionary library exposed in the TLB.
    Public Interface ISuperDictionaryInterface
        ReadOnly Property MyListOfTypeStringProperty(ByVal index As Integer) As String
        Function MyMethod(ByVal someValue as Variant) as Boolean
    End Interface

    <ClassInterface(ClassInterfaceType.None)>
    Public Class SuperDictionary: Implements ISuperDictionaryInterface

        Public Function MyMethod(ByVal someValue as Variant) As Boolean Implements ISuperDictionaryInterface.MyMethod
        '========================
        'Your code here
        '========================
        End Function

        Private _MyListOfTypeStringProperty As List(Of String)
        Public ReadOnly Property MyListOfTypeStringProperty(ByVal index as Integer) As String Implements ISuperDictionaryInterface.MyListOfTypeStringProperty
            Get
                Return _MyListOfTypeString(index)
            End Get
        End Property

    End Class
End Namespace

---

What can you do with .NET that VBA can't?

That's a good question, I'm glad you asked. Obviously you can do much more than what I demonstrated here. Let's say, for the sake of example, that you would like to integrate some of the fancy new Web Services all the cool kids are using these days. Whether you're communicating with web services using a WSDL file, or perhaps your own custom REST methods, the classes of the .NET framework combined with the plethora of tools found in Visual Studio 2012 developer environment make using .NET much more preferable than VBA. Using the technique outlined above, you could create a wrapper class for these web services that utilize custom methods to perform all the necessary actions, then return the VBA-compatible objects and/or data-types back to VBA. Much better, no?

Not to mention, the library you create would also be compatible with other platforms such as ASP.NET, Windows Phones, Silverlight, Xbox, etc.

---

Some helpful links I used (I'll add more as I find them):

• Use .NET Class Library in Excel VBA
• How to enable early binding of VBA object variable...
• Early Binding of a C# COM library in VBA