CODEWITHSUNDEEP
vb.netvbaexceldictionarycom
KK_
KK_

Reputation: 156

Converting a vb.net dictionary to a vba dictionary

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!

Upvotes: 1

Views: 1140

Answers (2)

cyberponk
cyberponk

Reputation: 1766

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

Upvotes: 0

KK_
KK_

Reputation: 156

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.

Upvotes: 2

Related Questions