Creating a column chart from dictionary values in VBA

I need to create a column chart from values that I have stored in a dictionary (Set dict = CreateObject("Scripting.Dictionary")) in VBA. The x-axis are t开发者_运维问答he keys and the y axis are the values. Is there a way to do this?

---

Try this:

Public Sub WriteDictionary()

    Dim dict As Variant
    Dim currRow As Integer


    Set dict = CreateObject("Scripting.Dictionary")
    currRow = 1

    dict.Add "key 1", "data 1"
    dict.Add "key 2", "data 2"

    For Each Key In dict.Keys
        Range("A" & currRow).Formula = Key
        Range("B" & currRow).Formula = dict(Key)
        currRow = currRow + 1
    Next Key

    Set dict = Nothing

End Sub

---

I'm answering this question because I had the same question, and I didn't find the answer here.

I'm using arrays to store the data from the scripting dictionary. Then, the arrays provide the source data for the graph.

Sub DictToChart()

    'Arrays for graph
    Dim xinput As Variant
    Dim yinput As Variant

    'Example dictionary
    Dim dict As Scripting.Dictionary
    Set dict = New Scripting.Dictionary

    'Populate dictionary
    dict.Add 0, 0
    dict.Add 10, 1
    dict.Add 20, 2
    dict.Add 30, 3
    dict.Add 40, 4

    'Populate arrays for graph
    ReDim xinput(0 To dict.Count - 1)
    ReDim yinput(0 To dict.Count - 1)

    For i = 0 To dict.Count - 1
        xinput(i) = dict.Keys(i)
        yinput(i) = dict.Items(i)
    Next i

    'Create graph
    ActiveSheet.Shapes.AddChart(xlColumnClustered).Select
    ActiveChart.SeriesCollection.NewSeries

    With ActiveChart
        .SeriesCollection(1).xvalues = xinput
        .SeriesCollection(1).Values = yinput
    End With


End Sub