Collection::Item runtime error


P

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
 
Ad

Advertisements

J

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
 
G

Guest

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.
 
P

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
 
J

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
 
Ad

Advertisements


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