Working with Collection object

[djmc]

Hello,

I have a function which contains two different collection objects. Given the name and key it will return the value if it exists or nothing if it doesnt. However, VBA prompts me with an error if we attempt to retrieve the value with a non existent key. I was wondering how to get around that. Thanks.

 

[SkipVought]

djmc,

My crystal ball is a tad foggy today.

Could you see it within your heart to share your code?

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[djmc]

Here's the actual function, it just returns the collection. I just want to know how to deal with nonexistent keys (instead of being prompted with an error i want to assign an empty string)

i.e someVar = myCollection("12312")
someVar would be ""

Private Function getLookupTable(ByVal strType As String) As Collection
Dim myCollection As New Collection

' Create the collection based on the type we want
Select Case strType
Case "TABLE 1":
myCollection.Add "blah blah blah", "1"
myCollection.Add "blah blah", "2"
myCollection.Add "blah", "10"

Case "TABLE 2":
myCollection.Add "blah blah blah", "11"
myCollection.Add "blah blah", "21"
myCollection.Add "blah", "101"

End Select
Set getLookupTable = myCollection
End Function

 

[SkipVought]

Set someVar = myCollection("12312")
if someVar is nothing then
  'i got nuthin!
else
  'i got something
end if

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[djmc]

I am getting the following error:

Invalid procedure call or arugment. Error #5

 

[SkipVought]

sorry,

Set someVar = getLookupTable("12312")
If someVar Is Nothing Then
  'i got nuthin!
Else
  'i got something
End If

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]