2

I want to access the dictionary value "Watermelon" by using the key "first", while the dictionary is sitting within the array. I believe this is the correct method for inserting a dictionary into an array. I've tried every combination I can think of for syntax, and can't find the answer anywhere for how to access the dictionary while it's sitting in the array.

Function dictTest() As Object
    Dim m_info As Dictionary
    Set m_info = New Dictionary
    m_info.Add "first", "watermelon"
    Set dictTest = m_info
End Function

Sub checker()
    Dim x(1 to 1) As Object
    Set x(1) = dictTest
End Sub
Improve this question
5
  • What exactly is your problem? What have you tried which has not worked? Commented Aug 15, 2018 at 16:38
  • Edited the above, I want to access the dictionary while it's sitting in the array. Syntax wise, I cannot figure how to access the dictionary while its sitting in the array. This is what I need help with. Commented Aug 15, 2018 at 16:39
  • See tigeravatar's answer! Commented Aug 15, 2018 at 16:40
  • @Joseph (et al) - Here is an excellent reference: Excel VBA Dictionary – A Complete Guide. ...and while you're bookmarking that link, bookmark this one too (from the same site): The Ultimate Guide To Collections in Excel VBA. (I generally avoid coding sites whose main purpose is to sell books, but this one's the exception since the site content is very thorough and well organized with examples of all the usual tasks involving VBA collections and/or dictionaries.) Commented Aug 15, 2018 at 16:43
  • @ashleedawg love that first link, it's what turned me on to dictionaries. I don't believe that stipulates behaviors with arrays, but great ref for sure. Commented Aug 15, 2018 at 16:47

1 Answer 1

Reset to default
5

This should work for you:

Function dictTest() As Object
    Dim m_info As Object
    Set m_info = CreateObject("Scripting.Dictionary")
    m_info.Add "first", "watermelon"
    Set dictTest = m_info
End Function

Sub checker()
    Dim x(1 To 1) As Object
    Set x(1) = dictTest
    MsgBox x(1)("first")
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Perfect. I googled and searched stack, couldn't find this exact answer. Much appreciated.
Can you clarify the benefit of using CreateObject("Scripting.Dictionary") as opposed to the way I declared and set the object, as my syntax works without using it
@JosephErickson It all depends on the machine you’re using. For portability, we use late binding like above because some machines don’t have the same “References” picked. Both ways work, but using early binding like you did in your question means you need to have checked the proper reference first
@JosephErickson Marcucciboy2 is correct, it's just a difference of early binding vs late binding. I personally prefer late binding because it has a higher compatibility with different kinds of systems and version of MS Office. See here for a brief description of the differences: excelmacromastery.com/vba-dictionary/#Early_versus_Late_Binding
@Marcucciboy2 except it's not "the machine" that has references picked, but the project. Project references are always saved along with the VBA project (in the VBProject.References collection, if you've toyed with the VBIDE API a bit). Late-binding the scripting runtime is always completely useless, because 1) it's the exact same version on every single Windows box since 1998 or so (and won't be updated anytime soon), and 2) late-bound or not, it will blow up all the same if ever executed on a machine that doesn't have the library (e.g. MacOS).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.