Collection::Item runtime error

[PaulH]

I have an excel vba macro that uses a collection to store some string
data. The collection 'key' is a string that is shown in a listbox. (as
below)

When accessing the 'Item' property, I get the runtime error: "Object
variable or With block variable not set"

' lbFiles is a listbox
Dim FileNames_ As Collection
Private Sub btn_Click()
Dim ListItem As Integer
ListItem = GetSelected()

If ListItem > -1 Then
Dim Path As String
Path = FileNames_.item( lbFiles.List(ListItem)) 'error here
'...
End If

'...
End Sub

I noticed that in all the documentation, the 'Item' property is upper
case, but if I try to type 'FileNames_.Item' in to vba, it
automatically lowers the case. (as above) Does that make a difference?

Thanks,
Paul

 

[Jim Cone]

You need a Set statement to create the Collection...
Set FileNames_ = New Collection.

Also, the first item in a Collection has an index of 1,
while the first item in a Listbox has an index of 0.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"PaulH" <[email protected]>
wrote in message
I have an excel vba macro that uses a collection to store some string
data. The collection 'key' is a string that is shown in a listbox. (as
below)
When accessing the 'Item' property, I get the runtime error: "Object
variable or With block variable not set"

' lbFiles is a listbox
Dim FileNames_ As Collection
Private Sub btn_Click()
Dim ListItem As Integer
ListItem = GetSelected()

If ListItem > -1 Then
Dim Path As String
Path = FileNames_.item( lbFiles.List(ListItem)) 'error here
'...
End If

'...
End Sub
I noticed that in all the documentation, the 'Item' property is upper
case, but if I try to type 'FileNames_.Item' in to vba, it
automatically lowers the case. (as above) Does that make a difference?
Thanks,
Paul

 

[Jim Thomlinson]

A couple of things to try...

Add something like this prior to the line that causes the problem
msgbox ListItem
msgbox lbFiles.List(ListItem)
msgbox FileNames_.item(1)

Your problem with "item" is probably that you have declared item as a
Variable somewhere (either intentionally or unintentionally). Do you use
option explicit in your code? I noticed that you are not using variable
prefixes like strFileNames. That is an easy way of making sure that your
variables do not replace the reserved words.

 

[PaulH]

You need a Set statement to create the Collection...
Set FileNames_ = New Collection.

Also, the first item in a Collection has an index of 1,
while the first item in a Listbox has an index of 0.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"PaulH" <[email protected]>
wrote in message
I have an excel vba macro that uses a collection to store some string
data. The collection 'key' is a string that is shown in a listbox. (as
below)
When accessing the 'Item' property, I get the runtime error: "Object
variable or With block variable not set"

' lbFiles is a listbox
Dim FileNames_ As Collection
Private Sub btn_Click()
Dim ListItem As Integer
ListItem = GetSelected()

If ListItem > -1 Then
Dim Path As String
Path = FileNames_.item( lbFiles.List(ListItem)) 'error here
'...
End If

'...
End Sub
I noticed that in all the documentation, the 'Item' property is upper
case, but if I try to type 'FileNames_.Item' in to vba, it
automatically lowers the case. (as above) Does that make a difference?
Thanks,
Paul

Ah, the 'new' keyword does it!

What does the 'set' keyword do? Does it replace the 'Dim'?
Is there a 'delete' (or something equivalent) I need to do since I'm
using 'new'?

Thanks,
PaulH

 

[Jim Thomlinson]

Check out this link...

http://www.cpearson.com/excel/variables.htm

Set is used with objects when you want to create, modfy or destroy the
object. (You can change the properties of an existing object without set)

 

[Jim Cone]

A collection is an intrinsic VBA object.
The Set statement assigns a new instance of the object to your variable.
Dim tells Excel what variable you will be using and to allocate some memory.
Both Dim and Set are required.
(note: they can be combined into one statement, but that is bad programming practice)

At the point in your code that the collection will not be used or referred to
again, you can free up memory by using...
Set FileNames_ = Nothing.

Some useful advice here...
http://www.cpearson.com/excel/newposte.htm
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"PaulH"
<[email protected]>
wrote in message

You need a Set statement to create the Collection...
Set FileNames_ = New Collection.

Also, the first item in a Collection has an index of 1,
while the first item in a Listbox has an index of 0.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"PaulH" <[email protected]>
wrote in message

Ah, the 'new' keyword does it!
What does the 'set' keyword do? Does it replace the 'Dim'?
Is there a 'delete' (or something equivalent) I need to do since I'm
using 'new'?
Thanks,
PaulH

 

[PaulH]

Check out this link...

http://www.cpearson.com/excel/variables.htm

Set is used with objects when you want to create, modfy or destroy the
object. (You can change the properties of an existing object without set)

Those are both really useful pages. Thanks, guys.

-PaulH