Getting the index of item in an array VBA

Question

I am trying to get the index of the item in an array VBA, but I have issue getting it.

Const NoOfVol As Integer = 5
Dim vol(NoOfVol) As Integer
For Index = 1 To NoOfVol
   vol(Index) = Cells(15 + Index, 8).Value
Next

The array looks like this: (-2500,-1250,0,1250,2500). I am calling the function this way.

Function Find(ByVal Value As Variant, arr As Variant) As Integer
    Find = Application.Match(Value, arr, False)
End Function
posOfVol = Find(-1250, vol)

But the posOfVol = 3, when it should be 2. Not sure where my error is. Need some guidance on this.

I don't think your array looks like you think it does. Unless you have an Option Base 1 statement, your array dimensions are 0 to 5, not 1 to 5. — Rory
– Rory, Commented Jan 21, 2015 at 9:19
even in the case, it should be 1 not 3. Not sure why it is 3. — lakshmen
– lakshmen, Commented Jan 21, 2015 at 9:23
How do you figure that? Your array is actually (0,-2500,-1250,0,1250,2500) since you didn't populate the first element and you are looking for an exact match. — Rory
– Rory, Commented Jan 21, 2015 at 9:25
Aside from Option Base 1, you can be explicitly in the dimension of your array: Dim vol(1 to NoOfVol) As Integer — L42
– L42, Commented Jan 21, 2015 at 9:42

Rory · Accepted Answer · 2015-01-21 11:40:16Z

1

Your array is actually effectively declared as:

Dim vol(0 to NoOfVol) As Integer

unless you have an Option Base 1 statement. Since your loop goes from 1 to 5, you never populate the first element of the array and it retains its default value of 0. Therefore your array is actually:

(0,-2500,-1250,0,1250,2500)

and since you are looking for an exact match, -1250 is found at the third position.

answered Jan 21, 2015 at 11:40

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

Getting the index of item in an array VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related