Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Performance: What is the best "method" to pull data from Several WorkBooks using VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,873
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Performance: What is the best "method" to pull data from Several WorkBooks using VBA

    Quote Originally Posted by Fluff View Post
    Thanks for that Mark, didn't realise it was limited in that way.
    Your welcome (especially for interpreting as I put addition rather than Edition). Just for completeness you need Office 2010 Professional Plus.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  2. #12
    Board Regular
    Join Date
    Apr 2011
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Performance: What is the best "method" to pull data from Several WorkBooks using VBA

    My purpose is understand the limitations and also the pros and cons.
    Just Like Collection is faster than Dictionary, what would be the best options when bearing with a big amount of data (different files).




    Thanks for your comments so far. I really appreciate it.

  3. #13
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,873
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Performance: What is the best "method" to pull data from Several WorkBooks using VBA

    Just Like Collection is faster than Dictionary,
    Interesting... might be worth scrolling to near the end of the link below and testing with the sample workbooks

    https://www.experts-exchange.com/art...ss-in-VBA.html
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  4. #14
    Board Regular
    Join Date
    Apr 2011
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Performance: What is the best "method" to pull data from Several WorkBooks using VBA

    Thanks for the website post.
    But testing with a straightforward code the result is different.
    See below
    Code:
    Option Explicit
    
    Sub Collection_VS_Dictionary()
        Const iterations = 500000
        Dim i As Long
        Dim clc As Collection
        Dim sngDuration As Single
    
    
        Set clc = New Collection
    
    
        sngDuration = Timer
        For i = 1 To iterations
            clc.Add i, CStr(i)
        Next i
        Debug.Print "Collection: " & Timer - sngDuration
        Set clc = Nothing
    
    
        Dim dic As Object  'Scripting.Dictionary
        Set dic = New Scripting.Dictionary
        sngDuration = Timer
        For i = 1 To iterations
            dic.Add CStr(i), i
        Next i
        Debug.Print "Dictionary: " & Timer - sngDuration
        Set dic = Nothing
    
    
    End Sub

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,960
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    42 Thread(s)

    Default Re: Performance: What is the best "method" to pull data from Several WorkBooks using VBA

    what would be the best options when bearing with a big amount of data (different files)
    In my opinion there is no such thing as a "Best" option, just different ways of doing things.
    What is "Best" depends on your exact situation, your knowledge (especially if you want to be able to modify later), what tools you have available, etc etc.

    Another possibility might be to pull everything into a database.
    Last edited by Fluff; Jun 16th, 2019 at 03:34 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #16
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,873
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Performance: What is the best "method" to pull data from Several WorkBooks using VBA

    Quote Originally Posted by Luthius View Post
    Thanks for the website post.
    But testing with a straightforward code the result is different.
    See below
    Code:
        For i = 1 To iterations
            dic.Add CStr(i), i
    From the article....

    Based on my testing, the "Exists" approach is slightly faster than the "Item" approach, and both are significantly faster than the "Add" approach. (Please see Performance: Dictionary vs. Collection for more details.)
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #17
    Board Regular
    Join Date
    Apr 2011
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Performance: What is the best "method" to pull data from Several WorkBooks using VBA

    Quote Originally Posted by Fluff View Post
    In my opinion there is no such thing as a "Best" option, just different ways of doing things.
    What is "Best" depends on your exact situation, your knowledge (especially if you want to be able to modify later), what tools you have available, etc etc.
    When I wrote best I mean for peformance sake. Just like I wrote before

    Quote Originally Posted by Luthius View Post
    My purpose is understand the limitations and also the pros and cons.
    For instance, we know that Collection is faster than Dictionary. But Dictionary has more features that Collection. So we can choose what is the best as per need and per limitation as well.
    CStr(i) was used on both

    clc.Add i, CStr(i)
    dic.Add CStr(i), i
    Last edited by Luthius; Jun 16th, 2019 at 04:03 PM.

  8. #18
    Board Regular NdNoviceHlp's Avatar
    Join Date
    Nov 2002
    Location
    Manitoba Canada
    Posts
    2,262
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Performance: What is the best "method" to pull data from Several WorkBooks using VBA

    This whole thread here ending with using arrays of ranges collected from separate wbs then inserted in 1 wb may be of interest/ HTH, Dave
    https://www.mrexcel.com/forum/excel-...a-macro-7.html

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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