I'm tring to write an Excel (2013) function that would take a 1x2n range of cells and return 1xn vector of cells that are of even/odd index. So if I put some numbers in cells A1:F1 as this

And put this function in A2:C2, it should return

I wrote something like this, but it doesn't work (#Arg! error)

Public Function Even(X As Variant) As Variant
Dim N As Integer
N = UBound(X)
ReDim Y(N / 2)

For i = 1 To N
If i Mod 2 = 0 Then
Y(i / 2) = X(i)
End If
Next i

Even = Y
End Function

After @BigBen comments I've changed the code to

Public Function Even(X As Variant) As Variant
Dim N As Integer
N = Application.CountA(X.Value)
ReDim Y(N / 2)

For i = 1 To N
If i Mod 2 = 0 Then
Y(i / 2) = X(i)
End If
Next i

Even = Y
End Function

It now returns almost what I want, it returns:

where's 0 coming from

CodePudding user response：

Here is a possibility. EVEN is a spreadsheet function, so a different name is preferable. EveryOther seems natural, but with a name like that, why not make it flexible enough to select the odds if need be? A good way to do that is to make an optional Boolean argument which controls if even or odd indices are chosen:

Function EveryOther(Rng As Range, Optional Evens As Boolean = True) As Variant
    Dim i As Long, j As Long, n As Long
    Dim cell As Range
    Dim returnVals As Variant
    
    n = Rng.Cells.count
    ReDim returnVals(1 To n)
    i = 0
    j = 0
    
    For Each cell In Rng.Cells
        i = i   1
        If i Mod 2 = IIf(Evens, 0, 1) Then
            j = j   1
            returnVals(j) = cell.Value
        End If
    Next cell
    ReDim Preserve returnVals(1 To j)
    EveryOther = returnVals
End Function