vba: return dictionary from function

user_360826 0
Viewed 30369

this outlines what i am trying to do.

this is not working for me, and it is unclear why.

thank you in advance for any and all help.

        Sub mySub()
        dim myDict as Dictionary
            myDict=new Dictionary

                myDict=myFunc()

        End Sub

        Function myFunc()
            dim myDict2
                set myDict2 = new Dictionary

                    'some code that does things and adds to myDict2'

            myFunc=myDict2
        End Function
excel-2003 vba excel
2 Answers

I see that this is an old question, but the post AND solution helped me figure this out, and I took it to the next level. It was a small leap. Thank you!

How about converting your function that populates the dictionary with process names and IDs so it returns a dictionary object? Then it is a simple task of populating a sheet with the dictionary contents, which I learned to do from a blog. I wish I had the author's name but the link is included.

Sheet1 was assumed of course. Customize however you wish. Again this was a small leap from what both of you posted. Absolutely brilliant work guys, thank you!

Sub Test_AllRunningApps()
    Dim apps As Dictionary
    Set apps = AllRunningApps()

    'Populate a sheet with a dictionary - http://exceldevelopmentplatform.blogspot.com/2018/05/vba-writing-dictionaries-to-worksheet.html
    Sheet1.Cells(1, 1).Resize(apps.Count, 1).Value2 = Application.Transpose(apps.Keys)
    Sheet1.Cells(1, 2).Resize(apps.Count, 1).Value2 = Application.Transpose(apps.Items)

    Set apps = Nothing
End Sub

'Similar to: http://msdn.microsoft.com/en-us/library/aa393618%28VS.85%29.aspx
Public Function AllRunningApps() As Dictionary
    Dim strComputer As String
    Dim objServices As Object, objProcessSet As Object, Process As Object
    Dim oDic As Object, oDic2 As Object, a() As Variant

    Set oDic = CreateObject("Scripting.Dictionary")

    strComputer = "."

    Set objServices = GetObject("winmgmts:\\" _
        & strComputer & "\root\CIMV2")
    Set objProcessSet = objServices.ExecQuery _
        ("Select Name, ProcessID FROM Win32_Process", , 48)

    For Each Process In objProcessSet
       If Not oDic.exists(Process.Name) Then
        oDic.Add Key:=Process.Properties_("Name").Value, Item:=Process.Properties_("ProcessID").Value
       End If
    Next

    Set AllRunningApps = oDic

    Set objProcessSet = Nothing
    Set oDic = Nothing
End Function
user_37847380
Related