Create and Extract The Data from Dictionary

     

     

     

    Dictionary

     

  • Activate the Microsoft Scripting Liabrary

    •  

     

    Define Dictionary Variable:

  • Dim dict As New Scripting.Dictionary

    •  

     

    Syntax for Add Method:

     

     

  • Dict.Add Key:="Apple", Item:=45

 

  • Dict.Add Key:="Banana", Item:=145
  • Dict.Add Key:="PineApple", Item:=245

    •  

    Syntax for Add Method:

  • Dict.Add "grapes", 265
  • Dict("Banana") = 284

    •  

    Check whether exists or not

    If Dict.Exists("Banana") Then
    MsgBox Dict("Banana")
    End If

     

     

    Add dictionary Item by using range

     

  • Dict.Add Key:=(Range("A5")), Item:=(Range("B5"))

    •  

    Find the count of Dictionary elements

  • Dict.Remove "Banana"

    •  

    For Each Loop for KeyValue

    Dim KeyValue As Variant
    For Each KeyValue In Dict.Keys
    MsgBox Dict(KeyValue)
    Next

     

    For Next Loop

    Dim i As Long
    For i = 0 To Dict.Count - 1
    msgbox Dict.Items(i)
    Next

     

    Nullifying the Dictionary

  • Set Dict = Nothing

    •  

    Click on below mentioned image to watch the video

     

     

    Download the Workbook

    Sub RetrieveValues_From_Dictionary()
    On Error Resume Next

    '======Define Worksheet=========
    Dim SH As Worksheet
    Set SH = ThisWorkbook.Sheets("Dictionary Structure")

    '======Define Dictionary=========
    Dim SalesDict As Scripting.Dictionary
    Set SalesDict = New Scripting.Dictionary

    '=========Add Values to Dictionary===
    LastRow = SH.Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To LastRow
    K = SH.Range("A" & r).Value
    Jan = SH.Range("B" & r).Value
    Feb = SH.Range("C" & r).Value
    Mar = SH.Range("D" & r).Value
    April = SH.Range("E" & r).Value
    May = SH.Range("F" & r).Value
    SalesDict.Add Key:=K, Item:=Array(Jan, Feb, Mar, April, May)
    Next

    '============Extract the Data From Dictionary=====
    KeyValue = SH.Range("J4").Value
    'MsgBox SalesDict.Item(KeyValue)(1)

    For i = 0 To UBound(SalesDict.Item(KeyValue))
    SH.Cells(4, i + 11).Value = SalesDict.Item(KeyValue)(i)
    Next
    EraseDictionary
    End Sub

    Function EraseDictionary()
    Set SalesDict = Nothing
    End Function

     

     

Hi Welcome to VBA Tutorial.