5

This is hopefully an easy one, but i cant seem to find any reference to it.

How do i print the location of something stored in an array, not the actual item that is in it.

Array(0) = Dog
Array(1) = Cat
Array(2) = Fish

Lets say i searched the array and found cat, how do i print the location cat was stored at, in this case Index Number (1).

Thanks in advance.

Improve this question

2 Answers 2

Reset to default
6

It's better to use ArrayList for such cases than simple array.

Set myArray = CreateObject ("System.Collections.ArrayList")
With myArray
  .Add "Dog"
  .Add "Cat"
  .Add "Fish"
End With

intIndex = myArray.IndexOf ("Cat",0)

Additionally you don't have to care about bounds.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Off topic and missing .indexOf.
Added .IndexOf - thanks. I still think, it's connected with the main question. It's an alternative solution that allows to easily deal with getting index of any element of a single dimensional array.
Well, if he's not sourcing the array, it can be more expensive to commute the array to the dictionary object, then pull the index - which just does a binary traversal anyway. All depends on the source.
5

Location of array is called index

If you run a loop, 

For i = LBound(Array) to UBound(Array)
 if Array(i) = "Cat" then  '--restrict to find index of particular item
   MsgBox i '-- gives the location/index of Cat item
 End if
next i

  • LBound : is the Lower bound, the starting index of the array. The first. It can be zero or anything as VBA provides the flexibility of changing the default array base to be either 0 or 1.

  • UBound : is the Upper bound, the ending index of the array. The last.

For further reading: LBound and Ubound conflicts in case of array which has been assigned by the Range.

Improve this answer

5 Comments

It's not the item that was stored in the index location i am after, its the actual index number itself.
IF YOU WANT the item it will be returned by Array(i) and index is returned as i ;)
Perfect thanks, it was so stupidly obvious i missed the answer in your first post.
Please the accept the answer by clicking on the tick mark :) as well. So the community know your question is solved and will help another will face similar issues like yours.
Sorry I forgot it's 15 minutes span ;) Good Luck, let us know if you reqiure further help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.