0

Problem: I want to create a function that gets one string and returns a dictionary. After much reading in SO I reproduced exactly the code given in some questions and I end up with a solution that looks to work. But there are things that I dont understand.

here the code which is a bit long because includes some comments:

Private Sub testdictionary()
Dim dict_in_sub As Scripting.Dictionary
    ' Do I really need this??:
    'Set dict_in_sub = New Scripting.Dictionary
    ' or this:
    'Set dict_in_sub =CreateObject("Scripting.Dictionary")

Call returning_dict_func("word1.word2")
Set dict_in_sub = returning_dict_func("whatever.second")

MsgBox "in code:" & Chr(10) & dict_in_sub("A") & Chr(10) & dict_in_sub("B")
End Sub

This is the function called:

Function returning_dict_func(SC As String) As  Scripting.Dictionary
' should this function return a dictionary or an object or a scrippting dictionary??

Dim dictionary_in_function
    Set dictionary_in_function = CreateObject("Scripting.Dictionary")

'take the first and second part of the string separated by the period
dictionary_in_function("A") = Mid(SC, 1, InStr(SC, ".") - 1)
dictionary_in_function("B") = Mid(SC, InStr(SC, ".") + 1, Len(SC))

MsgBox dictionary_in_function("A") & Chr(10) & dictionary_in_function("B")
Set returning_dict_func = dictionary_in_function
End Function

Even if I got the code running, there are a few things that make returning a dict from a function in VBA strange Introducing the following changes into the code make it not working:

Dim dict_in_sub as Dictionary
' it does not work. What is the difference between dictionary and scrippting.dictionary?

' defining the function as returning a dictionary does not work
Function returning_dict_func(SC As String) As Dictionary

' if both are dictionaries it does not work
 Dim dict_in_sub as Dictionary
 Function returning_dict_func(SC As String) As Dictionary

Why is dictionary different than scripting.dictionary?

When should you initialize with

Set dict_modifiers = CreateObject("Scripting.Dictionary")

thanks

Note: I had a look to: a) VBA in Excel 2010: Scripting.Dictionary return value of a function won't pass as parameter to a wrapping function call b) Returning dictionary from function in vba. Error 450 c) Returning an dictionary object from a function in VBA

Vityata
  • 42,633
  • 8
  • 55
  • 100
JFerro
  • 3,203
  • 7
  • 35
  • 88
  • This question is really too broad for this forum. But two suggestions: Always use `Option Explicit` at the top of your modules (and, in the VBE, go to Tools/Options and **SET** the option to require variable declaration). With regard to declaring the dictionary type, search for the differences between late-binding and early-binding. – Ron Rosenfeld Jul 12 '19 at 11:12
  • I thought so, but just if I get the difference between dictionary and scrippting.dictionary would be enough. – JFerro Jul 12 '19 at 11:36
  • Either can work. `Scripting` ensures that the `Dictionary` object is bound to `Microsoft Scripting Runtime`. So if you are using late-binding, it is required when you `CreateObject`. If you are using early binding, and only one of your references has a `Dictionary` object, then it would be optional. Others may be able to phrase this better, but that's how I understand it. – Ron Rosenfeld Jul 12 '19 at 12:21
  • 1
    This topic covers about 5 out of 7 of the question - https://stackoverflow.com/questions/48429138/are-as-dictionary-and-as-scripting-dictionary-equivalent-for-vba-early-bind – Vityata Jul 12 '19 at 14:10

1 Answers1

2

The difference between CreateObject and New is briefly you need to reference a library with the object (Microsoft Scripting Runtime) for the latter (New), and you don't need to reference anything using CreateObject.

You can also have some intellisense hints when you use the latter definition, as the type is declared and known.

You can find more in another answer: What are the differences between using the New keyword and calling CreateObject in Excel VBA?

As for the Dictionary object, Scripting.Dictionary is the right type to use. Read more about it in https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object.

And by the way, in VBA you have to assign variables values using Set when the value is an object (usually something different than a number or String or Date).

Łukasz Nojek
  • 1,511
  • 11
  • 17