Excel VBA: Mysterious Zero is Being Added to Array

Question

Hey everyone: I'm trying to make a function that digs through an array and adds values from range2 when the corresponding value from range1 equals criteria1.

I'm relatively new to VBA, so it's not the most elegant function in the world, but here's my code:

Function SingleArray(range1 As Range, range2 As Range, criteria1 As String)

Dim newrange() As Double
Dim d As Integer
Dim g As Integer
Dim i As Integer

g = Application.WorksheetFunction.CountIf(range1, criteria1)

ReDim newrange(g)

d = 1

For i = 0 To (range1.Count)
    If range1(i) = criteria1 Then
        newrange(d) = range2.Item(i).Value
        d = d + 1
        End If
    Next i


SingleArray = newrange

End Function

Here is my data sample:

range2  range1
-5000   Bob
-5000   Jim 
 200    Bob 
 500    Jim 
 5000   Bob 
 200    Bob 
 300    Bob 
 1000   Bob

When I set the criteria as "Bob," the array that is returned is as follows:

{0,-5000,200,5000,200,300,1000}

I'm genuinely at a loss for how that zero is making it in there. Any thoughts you can provide would be most welcome!

Answer 1

1-D arrays default to a zero-based index structure (e.g. 0, 1, 2, 3, ....). You are looping through the ranges with a one based index (e.g. 1, 2, 3, 4, ...).

When you declare ReDim newrange(5) you are actually creating an array with six elements, not five (e.g. 0, 1, 2, 3, 4, 5)

You can make all arrays on that code sheet default to a one based index by putting this compiler directive at the top of the code sheet.

Option Base 1

You can also change the way the array is declared on the fly by specifying the Lower Boundary and the Upper Boundary.

ReDim newrange(1 to g)