• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hard Code Table Data In The Macro ??

What is the time difference between the two ?

And if significant, I guess I'll need to start over again with the macro ?
 

For a million : collection ~10s vs dictionary ~60s !
For 200 000 a dictionary needs 0.3s more.
May vary upon the processor, frequency, …​
 

In fact it depends too on how the code is written.
I just realized a simplified test and this time for a million
a collection stays around 10s but a dictionary needs ~22.5s …​
 

No time to read all but as I wrote under some conditions
(MAC / huge data) I prefer to use a collection …
For less than 400 000 items use a Dictionary.
(Or for more than 500 000 use a Collection …)
​
 
How can I query on " Apple " and return " X " ?

Code: 
Option Explicit

Sub UserCollection()

    ' Declare and Create collection
    Dim collFruit As New Collection
    Dim x As String
  
    ' Add items
    collFruit.Add "Apple", "X"
    collFruit.Add "Pear", " green"
    collFruit.Add "Plum", " purple"

x = InputBox("Enter Term ", "Search ")

    ' Print all items
    Dim i As Long
  
    For i = 1 To collFruit.Count
        If collFruit(i) = x Then
            MsgBox collFruit(i)
        End If
    Next i

End Sub
 
Hard coding these values and loading into a Collection or Dictionary is such poor practice especially if you do have 100,000 records.

Much better to have the records on a very hidden worksheet
Read the worksheet into a Variable array
Then load a Collection or Dictionary
Then process away as you require
 
In regards to your question I think you need to use a Dictionary

Code: 
Sub UserDictionary()

    ' Declare and Create Dictionary
    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")
    Dim x As String
    ' Add items
    Dict.Add "Apple", "X"
    Dict.Add "Pear", " green"
    Dict.Add "Plum", " purple"

    x = InputBox("Enter Term ", "Search ")

    ' Print all items
    Dim i As Long
    MsgBox x & " contains : " & CStr(Dict(x))

End Sub
 
Logit said:
How can I query on " Apple " and return " X " ?
Click to expand...
As you can't query on "Apple" as it is an item an "X" the key !
See post #14 and like you can see in VBA help : Collection.Add Item, Key …
So you can query "X" to get "Apple" : MsgBox collFruit("X")
​
 
Hui & Marc :

You are each recommending something different (Col / Dict).

I've not used either yet. Have no experience on their performance. Is this something that comes down to selecting one type apple from two varieties ... or ... is their truly a preferred difference between the two based on the needs of my project ?

Understand I'm not desirous of initiating a "spitting contest" ... and I'm certainly not questioning your advice. Just trying to best understand which direction to go.

Thank you so much for your assistance and understanding. You guys are great !
 
Hui:

Made an edit to your last macro. Do you see anything wrong with this (trying to create a Collection macro).

Code: 
Option Explicit

Sub UserCollection()

    ' Declare and Create collection
    Dim collFruit As New Collection
    Dim x As String
   
    ' Add items
    collFruit.Add "Apple", "X"
    collFruit.Add "Pear", "green"
    collFruit.Add "Plum", "purple"

x = InputBox("Enter Term ", "Search ")

    ' Print all items
    'Dim i As Long
   
    'For i = 1 To collFruit.Count
    '    If collFruit(i) = x Then
    '        MsgBox collFruit(i)
    '    End If
    'Next i
   
    ' Print all items
  Dim i As Long
    MsgBox x & " contains : " & CStr(collFruit(x))

End Sub
 
Logit said:
Hui & Marc :

You are each recommending something different (Col / Dict).
Click to expand...
No I just warned maybe you made an error in the use of the Collection
method Add as you swap item & key !
As written in VBA inner help and in the post #34
with your codeline collFruit.Add "Apple", "X"
Apple is an item and X its key so you can query only X but not Apple ! …

But the real concern is if you don't use Excel / MAC and you have less
than 500 000 items so use a Dictionary …
With less than 100 000 items no matter using one or the other (if good code)
even if the Dictionary is a bit faster but for both it needs less than a second.
​
 

So forget to hardcode, use a worksheet or an external file.

But for a beginner a Dictionary may be easier …​
 
You can also load arrays into Collections which you might want to do for each row of your data as they contain 10 fields

Excel Macro Mastery has a great video on that in the Vault

I don't have a link but email Paul at Paul@excelmacromastery.com
Paul is very approachable
 

Yes this array is the way I wrote for a class module collection
working on MAC like a Dictionary under Windows …​
 
Thank you all for your assistance. You have been extremely helpful. More tools in my toolbox now !

Have a really great day.

:)
 
You must log in or register to reply here.
Back
Top