PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Excel VBA: How To: Copy a Dictionary ???-VBForums
Results 1 to 5 of 5

Thread: Excel VBA: How To: Copy a Dictionary ???

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Question Excel VBA: How To: Copy a Dictionary ???

    Esteemed Forum Participants and Lurkers:
    ===============================
    Excel 2003 VBA
    Is there a quick and dirty way to copy all of the contents of one dictionary into a different dictionary? Here is my attempt, but the last line of this snippet of code pops up an error: MS Visual Basic: Compile error: Argument not optional
    Code:
    Sub MyJunk()
        'Generate a Smart Team Dictionary - only one is required
        Dim aSmart_List As New Dictionary '- ONLY REQUIRED for DOUBLE Tables - Smart Team per Store
        'Generate a TEST dictionary
        Dim junk As New Dictionary
        
        'Make sure the dictionaries are clean
        aSmart_List.RemoveAll
        junk.RemoveAll
        
    This routine just loads about 150 Key/Data pairs into the Dictionary
        'Create the new Smart Team List Dictionaries BY REFERENCE
        Call Make_Master_Team_Dictionary(aSmart_List)    
        Debug.Print aSmart_List("029"), aSmart_List("051"), aSmart_List("477")
        
        'My feeble attempt to copy all of the data into a new dictionary
        junk = aSmart_List
        
    End Sub
    If this doesn't work, I'll just use arrays to get the job done.

    Thank you for any and all comments, suggestions, and assistance.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  2. #2
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,610

    Re: Excel VBA: How To: Copy a Dictionary ???

    Have you tried using the Set command

    vb Code:
    1. Set junk = aSmart_List

    Now i haven't tried it myself, and i would be surprised if it worked but here is a link to a page i use sometimes which may help.

    You could for instance put the data in an array first and then copy that into a dictionary !!

    http://www.cpearson.com/excel/Collec...ctionaries.htm
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Excel VBA: How To: Copy a Dictionary ???

    Doesn't the "Set" directive just point the "junk" directory to the other one? I need 2 separate dictionaries, but 98% of the keys are the same.

    Thanks ... I'll try it and post the results.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Excel VBA: How To: Copy a Dictionary ???

    Yes, SET just creates 2 names for the SAME dictionary ... not 2 separate dictionaries. If I change one of the items in "junk", it changes the same item in "aSmart_List". I know I can equate ranges on a WorkSheet (no SET directive) to copy values from one area into another, and I was hoping for an equally simple way of copying Dictionary or Array contents.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  5. #5
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,610

    Re: Excel VBA: How To: Copy a Dictionary ???

    Well I have to say I didn't think it would work really !!

    Have you looked at the link ?, i am not sure exactly what you are trying to do in the end but there are some quite nifty functions there which may help including

    ArrayToCollection
    ArrayToDictionary
    CollectionToArray
    CollectionToDictionary
    CollectionToRange
    CreateDictionaryKeyFromCollectionItem
    DictionaryToArray
    DictionaryToCollection
    DictionaryToRange
    KeyExistsInCollection
    RangeToCollection
    RangeToDictionary
    SortCollection
    SortDictionary
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width