Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in an array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in an array
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 *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in an array
It's possible to add 2 different values for each key or are you constraint to
just 1 value per key? "Edward Ulle" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in an array
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. *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in an array
You are awesome. That's exactly what I need. Thanks a bunch.
"Edward Ulle" wrote: 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. *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup in an array
I hope this helped. If not maybe some other idea will work for you.
Let me know. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use array for lookup value, to return array of lookups | Excel Discussion (Misc queries) | |||
Lookup array | Excel Worksheet Functions | |||
Lookup or Array or Something???? | Excel Discussion (Misc queries) | |||
Lookup "greater than or equal to" in lookup array | New Users to Excel | |||
How do I use <= in a lookup array? | Excel Discussion (Misc queries) |