Lookup in an array

[Guest]

Is there a quick lookup function in VBA? I have an array defined as
Type newtype
code as string
value as integer
end type
DIM testarray as newtype
Code is "A".."Z"
Is it possible to do something like this in VBA testarray("A").value? or Do
I need to loop through the array until I find the code "A" and then get
testarray(i).value.

Thanks

 

[Edward Ulle]

The Type you describe; key and value, looks very much like a Collection.
The beauty of collection is they are dynamic.

The following is a simple example.

Option Explicit

Sub Test()

Dim myCollection As Collection

Set myCollection = New Collection

myCollection.Add Item:=1#, Key:="one"
myCollection.Add Item:=2#, Key:="two"
myCollection.Add Item:=3#, Key:="three"

MsgBox myCollection("two")

Set myCollection = Nothing ' Remove from memory

End Sub

 

[Guest]

It's possible to add 2 different values for each key or are you constraint to
just 1 value per key?

 

[Edward Ulle]

Not that I can think of, but if its not too late, if your Type as
several values it could be replaced with a class.

I created a sample class called MyClass. Play close attention to the
"Set" command.

+++++ Start of MyClass +++++

Option Explicit

Private firstValue As Long
Private secondValue As Single
Private thirdValue As Range

Public Property Let SetFirstValue(lValue As Long)
firstValue = lValue
End Property

Public Property Let SetSecondValue(sValue As Single)
secondValue = sValue
End Property

Public Property Set SetThirdValue(rValue As Range)
Set thirdValue = rValue
End Property

Public Property Get GetFirstValue() As Long
GetFirstValue = firstValue
End Property

Public Property Get GetSecondValue() As Single
GetSecondValue = secondValue
End Property

Public Property Get GetThirdValue() As Range
Set GetThirdValue = thirdValue
End Property

Public Sub SetAll(lValue As Long, sValue As Single, rValue As Range)
firstValue = lValue
secondValue = sValue
Set thirdValue = rValue
End Sub

+++++ End of MyClass +++++

And a module to create and access the instances of class

+++++ Start of Module +++++

Option Explicit

Sub Test()

Dim myItem As MyClass
Dim myCollection As Collection

Set myCollection = New Collection

Set myItem = New MyClass
myItem.SetFirstValue = 1&
myItem.SetSecondValue = 2!
Set myItem.SetThirdValue = Range("A1")

myCollection.Add Item:=myItem, key:="one"

Set myItem = New MyClass
myItem.SetFirstValue = 3&
myItem.SetSecondValue = 4!
Set myItem.SetThirdValue = Range("B2")

myCollection.Add Item:=myItem, key:="two"

Set myItem = New MyClass
myItem.SetAll 5&, 6!, Range("C3")

myCollection.Add Item:=myItem, key:="three"

Set myItem = myCollection("two")
MsgBox myItem.GetSecondValue

Set myItem = myCollection("three")
MsgBox myItem.GetThirdValue.Address

Set myCollection = Nothing

End Sub
+++++ End of Module +++++

It may seem like a lot of typing to create something as simple as the
Type but its much more flexible in future developement.

I personally do not use arrays or types too much since what is a
Spreadsheet but a two dimensional array, each row or record can have 256
distinct values.

 

[Guest]

You are awesome. That's exactly what I need. Thanks a bunch.

 

[Edward Ulle]

I hope this helped. If not maybe some other idea will work for you.
Let me know.