Scripting.Dictionary question

[Jennifer]

With the following code i keep getting this error. Can you help?
***Compile Error
***User_defined not type defined

Private Sub LoadMares()
Dim mares As New Scripting.Dictionary (THIS IS WHAT IS HIGHLIGHTED)
Dim index As Long
Dim Mare As String
For index = 2 To Source.Rows.Count
Mare = Source.Cells(index, 1)
If Not mares.Exists(Mare) Then
mares.Add Mare, Mare
cmbMare.AddItem Mare
End If
Next


End Sub

 

[NickHK]

Jennifer,
You need to add a reference (Tools>References) to "Microsoft Scripting
Runtime".
Depending what you are doing, you may not need this at all, as VBA has a
Collection object which behaves similar to the Dictionary object.

NickHK
P.S. You should aware that using "New" this way:
Dim mares As New Scripting.Dictionary
will mean that
If mares Is Nothing Then
will never evaluate to True. This is because an instance of the class will
be created everytime the variable "mares" is referred to, if it does not
currently refer to an instance.
Using the 2 part:
Dim mares As Scripting.Dictionary
Set mares = New Scripting.Dictionary
avoids this.

 

[Bob Phillips]

Also it is bad practice to declare an object as New, it may get created when
you least expect it, far better to us

Dim mares As Scripting.Dictionary

Set mares = New Scripting.Dictionary


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Leo Heuser]

Also it is bad practice to declare an object as New, it may get created
when
you least expect it, far better to us

Dim mares As Scripting.Dictionary

Set mares = New Scripting.Dictionary

Hi Bob

I don't understand your comment. If declared as New,
the object is *always* created the first time the
variable is used (as expected!?).
When is this bad?

Leo

 

[Bob Phillips]

When you auto-instance a variable in this way, it is created the first time
that it is referenced, but that is WHENEVER it is referenced. When it is
encountered in code, it is checked to see if it is Nothing, and if so, an
instance of the object is created.

Thus, more code is generated and executed each time the variable is
encountered. Logically, the following train of events takes place
If myObject Is Nothing Then
Set myObject = New object_type
End If

This is no big deal in itself, but this actually precludes you from testing
for Nothing in your code.

Try this, create a class (no code) called clsTest, and run this code

Dim myObject As New clsTest

If myObject Is Nothing Then
MsgBox "empty"
End If

and this code doesn't error as it should

Set myObject = Nothing
myObject.myProp = "xyz"


In short, you lose control, and that is not good.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dave Peterson]

Chip Pearson has some notes, too:
http://www.cpearson.com/excel/variables.htm
Look for: Don't Use The New Keyword In A Dim Statement

 

[Leo Heuser]

Thanks guys!

Leo

Chip Pearson has some notes, too:
http://www.cpearson.com/excel/variables.htm
Look for: Don't Use The New Keyword In A Dim Statement