Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection::Item runtime error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection::Item runtime error
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection::Item runtime error
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. -- HTH... Jim Thomlinson "PaulH" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection::Item runtime error
On Aug 27, 1:45 pm, "Jim Cone" wrote:
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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection::Item runtime error
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) -- HTH... Jim Thomlinson "PaulH" wrote: On Aug 27, 1:45 pm, "Jim Cone" wrote: 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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection::Item runtime error
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" wrote in message On Aug 27, 1:45 pm, "Jim Cone" wrote: 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" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection::Item runtime error
On Aug 27, 2:48 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: 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) -- HTH... Jim Thomlinson "PaulH" wrote: On Aug 27, 1:45 pm, "Jim Cone" wrote: 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" 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 Those are both really useful pages. Thanks, guys. -PaulH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How does Collection.Item search | Excel Programming | |||
How to assign a collection item? | Excel Programming | |||
Series Collection Results in Runtime Error 1004 | Excel Programming | |||
Retrieving a Collection Item | Excel Programming | |||
Runtime error with the sheet collection, which was not there in 20 | Excel Programming |