VBA - running scripting dictionary (error!)

A

ajliaks

Hi,

Following your advice, I am trying this code, in order to show item
listed in combobox only once each item. (like autofilter), but i
doesn't work, (it gives mi error: Variable not defined and remarkin
"Scripting")


Private Sub UserForm_Initialize()

'Set a reference to MS Scripting runtime

Set Dic = CreateObject(Scripting.Dictionary)

Dim Dic As New Scripting.Dictionary
Dim rSource As Range, cell As Range
Dim sVal As String
Dim MyList As String

'Point to specific column
MyList = "P3:p20"
Set rSource = Range("MyList")

With ChapExpCB
For Each cell In rSource.Cells
sVal = cell.Value
If Not Dic.Exists(sVal) Then
Dic.Add sVal, sVal
.AddItem sVal
End If
Next
End With
Set Dic = Nothing

End Sub

Thanks in advance
 
C

Chip Pearson

You need quotes around the "Scripting.Dictionary" in the
CreateObject function. E.g.,

Set Dic = CreateObject("Scripting.Dictionary")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
T

Tom Ogilvy

You didn't follow my advice. My advice showed

Set Dic = CreateObject("Scripting.Dictionary")

You wrote

Set Dic = CreateObject(Scripting.Dictionary)

also
Dim Dic As New Scripting.Dictionary
shouldn't be used if you use late binding. You would use that for early
binding (creating a reference) and would not use the createobject line.

Dim dic as Object

for late binding
 
C

chris

shouldn't scripting.dictionary be a string: Set Dic = CreateObject("Scripting.Dictionary"


----- ajliaks > wrote: ----

Hi

Following your advice, I am trying this code, in order to show item
listed in combobox only once each item. (like autofilter), but i
doesn't work, (it gives mi error: Variable not defined and remarkin
"Scripting"


Private Sub UserForm_Initialize(

'Set a reference to MS Scripting runtim

Set Dic = CreateObject(Scripting.Dictionary

Dim Dic As New Scripting.Dictionar
Dim rSource As Range, cell As Rang
Dim sVal As Strin
Dim MyList As Strin

'Point to specific colum
MyList = "P3:p20
Set rSource = Range("MyList"

With ChapExpC
For Each cell In rSource.Cell
sVal = cell.Valu
If Not Dic.Exists(sVal) The
Dic.Add sVal, sVa
.AddItem sVa
End I
Nex
End Wit
Set Dic = Nothin

End Su

Thanks in advance
 
D

Dana DeLouis

As a side note...
MyList = "P3:p20"
Set rSource = Range("MyList")

You may get an error here. If so, see if this will work without the quotes.

MyList = "P3:p20"
Set rSource = Range(MyList)
If Not Dic.Exists(sVal) Then

Usually, it is not necessary to test for Existence first. I find it easier
to use an "On error" as in this simple example...

On Error Resume Next
For Each cell In rSource.Cells
sVal = cell.Value
Dic.Add sVal, sVal
Next

Just some ideas. HTH. :>)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top