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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,688
Office Version
365, 2010
Platform
Windows, Mobile
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Luthius

Active Member
Joined
Apr 5, 2011
Messages
274
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.
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
274
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,968
Office Version
365
Platform
Windows
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:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,688
Office Version
365, 2010
Platform
Windows, Mobile
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.)
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
274
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

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:

Watch MrExcel Video

Forum statistics

Threads
1,099,600
Messages
5,469,628
Members
406,661
Latest member
west5405

This Week's Hot Topics

Top