none
Dictionary code query in excel VBA RRS feed

  • Question

  • Hi

    I have come across the following code in a live Excel VBA application.

      If Not (gapDictionary Is Nothing) Then
            Exit Function
        End If
       
        Set gapDictionary = New Dictionary

    My understanding of this is that it checks if there are entries in the gapDictionary and if there it exits otherwise it creates a dictionary with the New command. I am not sure if this is a correct interpretation.

    I copied this code into a little stand alone file but it would not compile - it did not recognize the "New Dictionary" statement .

    Reading the online documentation suggests that the correct way to create a dictionary is by using a command like:

    Set d = CreateObject(Scripting.Dictionary)

    Can anyone explain what the code above means and why my little test does not seem to work?

    thank you.

     Peter

    Friday, October 14, 2016 8:51 AM

Answers

  • The Dictionary object is not part of the default VBA code library. It is part of the Microsoft Scripting Runtime library.

    There are two ways to use this object in your code: early binding and late binding.

    With early binding, you set a reference to the relevant library in Tools > References...

    After doing this, you can type

    Dim d As Scr

    in your code, and the Visual Basic Editor will automatically offer Scripting as possibility:

    And it will also show you the methods and properties of the Dictionary object.

    The line

    Set gapDictionary = New Scripting.Dictionary

    or

    Set gapDictionary = New Dictionary

    will work.

    If you don't set a reference to the relevant library, you can't use the above lines, since the Dictionary object is unknown. Instead, you must use the CreateObject method and specify the object type as a text string:

    Set gapDictionary = CreateObject("Scripting.Dictionary")

    The Visual Basic Editor will only show the methods and properties of a generic object, not the specific ones of the Dictionary object.

    This is called late binding.

    For more info, see:

    EARLY binding and LATE binding

    Early vs. Late Binding


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Friday, October 14, 2016 12:29 PM
    Friday, October 14, 2016 10:52 AM

All replies

  • The Dictionary object is not part of the default VBA code library. It is part of the Microsoft Scripting Runtime library.

    There are two ways to use this object in your code: early binding and late binding.

    With early binding, you set a reference to the relevant library in Tools > References...

    After doing this, you can type

    Dim d As Scr

    in your code, and the Visual Basic Editor will automatically offer Scripting as possibility:

    And it will also show you the methods and properties of the Dictionary object.

    The line

    Set gapDictionary = New Scripting.Dictionary

    or

    Set gapDictionary = New Dictionary

    will work.

    If you don't set a reference to the relevant library, you can't use the above lines, since the Dictionary object is unknown. Instead, you must use the CreateObject method and specify the object type as a text string:

    Set gapDictionary = CreateObject("Scripting.Dictionary")

    The Visual Basic Editor will only show the methods and properties of a generic object, not the specific ones of the Dictionary object.

    This is called late binding.

    For more info, see:

    EARLY binding and LATE binding

    Early vs. Late Binding


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Friday, October 14, 2016 12:29 PM
    Friday, October 14, 2016 10:52 AM
  • Hans

    Thanks you for such a comprehensive answer - as you can tell this iarea is new to me but the references you have provided will allow to increase my understanding of this area.

    thank you for your continued support.

    kind regards,

    Peter

    Friday, October 14, 2016 12:29 PM