Converting a vb.net dictionary to a vba dictionary

Question

I was pleased to find that I could call structs that I had set up in vb.net straight into excel vba - using COM visible and registering using regasm.exe.

I am struggling to do the same with a dictionary created in vb.net.

I found this link which suggested that the dictionary in vb.net was not the same as the runtime.scripting dictionary found in vba.

I was unable to have much luck with the links suggested in the comments though.

Here is the vb.net code:

Public Function ReturnDict() As Dictionary(Of String, Integer)
    Dim dict As New Dictionary(Of String, Integer)
    dict.Add("a", 10)
    dict.Add("b", 11)
    Return dict
End Function

Here is the vba code:

Function MyReturnDict()
   Dim classLib As New MyVBClass.Class1
   Set MyDict = classLib.ReturnDict()
     \\do stuff with dictionary
   MyReturnDict = Result
End Function

Any help/advice would be much appreciated!

Parke

Possible duplicate of parser to convert .net dictionary or list to VBA's equivalent dictionary or collection. – Corticosterone

While the linked question refers to c sharp, I agree in essence the two questions are extremely similar and I apologise for this. However, is it possible to shed some light on how that answer could be applied to vb.net? – Parke

Neither COM nor VBA support generic types. Simple to fix, all you have to do is change the return type to System.Collections.IDictionary. If type safety is important then you have to create your own. – Coulson

Many thanks @HansPassant - that returned a dictionary-like object to VBA. In order to convert it in the scripting.runtime VBA dictionary - is it a case of iterating over all the keys and values? If so, is there an efficient way of doing that? – Parke

An efficient way is to not copy. Add a reference to c:\windows\syswow64\scrrun.dll, create a Scripting.Dictionary object in your code. – Coulson

@KK_: Please would you edit your question, take out the answer, and add it as an answer which you could then accept... of course, you should give credit to Hans Passant too in your answer. – Cyanotype

Answer 1

Hans Passant's solutions in the comments above perfectly solved the problem:

In VB.net, either use:

Public Function ReturnDict() As System.Collections.IDictionary

or reference scrrun.dll and:

Public Function ReturnDict() As Scripting.Dictionary
    Dim dict As New Scripting.Dictionary

The latter solution provides a VBA dictionary that can be used as one would like.

Parke

Answer 2

To convert a Dictionary(Of String, String) from VB.NET to a Scripting.Dictionary object in VBA:

In both VB.NET and VBA projects, add a reference to

c:\windows\syswow64\scrrun.dll or c:\windows\system32\scrrun.dll

In your VB.NET project, add conversion function:

''' <summary>
''' Converts a VB.NET string Dictionary to a VBA Scripting.Dictionary object for COM interaction
''' </summary>
''' <returns>A Scripting.Dictionary object</returns>
''' <remarks>Required reference: "c:\windows\syswow64\scrrun.dll"</remarks>

Public Function ConvertDictionary_to_VBA(dic As Dictionary(Of String, String)) As Scripting.Dictionary
    Dim dic_vba As New Scripting.Dictionary
    For Each item In dic.ToArray()
        dic_vba.Add(item.Key, item.Value)
    Next
    Return dic_vba
End Function

In your VBA project, use Scripting.Dictionary as object that receives the converted dictionary:

Dim dic As Scripting.Dictionary

Ergotism