Function and collections

[JoshuaD]

Hello everyone. I am trying to create a function that runs smoothly. Currently i am using a bunch If/Then statements to return values (about fifty) . The tricky part is i need to return strings from numbers. For instance if the value is 1 return apple. The issue i am having is the keys are not in sequence. I need to store values like 1,5,48,25. With strings specific to the number. I wanted to store the strings in a collection and call them with a function. But as i said before the values in the cells are not in sequence. Is a collection the right way to go? I will post some example code when i get back to my computer.

 

[Leith Ross]

Re: Function and collections help

Hello JoshuaD,

Either a Collection or Dictionary object will provide you with random access to the stored values.

 

[Rick Rothstein]

Re: Function and collections help

You could also use an array which would have a lot of unused elements in it. Let's say your data is like this where 48 is the largest key value...

1 = Apple
5 = Peach
25 = Pear
48 = Banana

then you could do this...

Dim N As Long, V As Variant, Arr(1 To 48) As String
For Each V in Array(1, 5, 25, 48)
  Arr(V) = Array("Apple", "Peach", "Pear", "Banana")(N)
  N = N + 1
Next

Now, in the rest of your code, Arr(1) would return "Apple", Arr(5) would return "Peach", Arr(25) would return "Pear" and Arr(48) would return "Banana".

 

[JoshuaD]

Re: Function and collections help

You could also use an array which would have a lot of unused elements in it. Let's say your data is like this where 48 is the largest key value...

1 = Apple
5 = Peach
25 = Pear
48 = Banana

then you could do this...

Dim N As Long, V As Variant, Arr(1 To 48) As String
For Each V in Array(1, 5, 25, 48)
  Arr(V) = Array("Apple", "Peach", "Pear", "Banana")(N)
  N = N + 1
Next

Now, in the rest of your code, Arr(1) would return "Apple", Arr(5) would return "Peach", Arr(25) would return "Pear" and Arr(48) would return "Banana".

Rick Thank You

That is exactly what I was looking for. I new it was possible, but could not figure out how to word it.


Leith-

I do not have experience with Dictionary Objects but I ill look into it!


Thank you both so much!