Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scripting.Dictionary question
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 -- Though daily learning, I LOVE EXCEL! Jennifer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scripting.Dictionary question
Jennifer,
You need to add a reference (ToolsReferences) 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. "Jennifer" wrote in message ... 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 -- Though daily learning, I LOVE EXCEL! Jennifer |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scripting.Dictionary question
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) "Jennifer" wrote in message ... 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 -- Though daily learning, I LOVE EXCEL! Jennifer |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scripting.Dictionary question
"Bob Phillips" skrev i en meddelelse
... 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scripting.Dictionary question
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) "Leo Heuser" wrote in message ... "Bob Phillips" skrev i en meddelelse ... 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scripting.Dictionary question
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 wrote: "Bob Phillips" skrev i en meddelelse ... 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 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scripting.Dictionary question
Thanks guys!
Leo "Dave Peterson" skrev i en meddelelse ... 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 wrote: "Bob Phillips" skrev i en meddelelse ... 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 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SCRIPTING | Excel Programming | |||
Scripting Question | Setting up and Configuration of Excel | |||
Newbie VBE scripting question | Excel Programming | |||
VBA - running scripting dictionary (error!) | Excel Programming | |||
VBA question - Set reference to run Scripting.Dictionary | Excel Programming |