Problem with VBA, inputs and loops not working

[Evil_Stepmother]

Nothing works with my code anymore. If i try to solve the first problem more promblems occour further down.  I whish to do three things: 1) determine the length of my input (number of cells) 2) A for-loop based on the length of input 3) find the average from the loop. Here is my code

Function TILLV(rng As Range) As Double

L = (UBound(rng, 1) - LBound(rng, 1) + 1)
Dim a() As Double
ReDim a(L)
Dim i As Integer

For i = 1 To L
        If rng(i) < 0 And rng(i + 1) < 0 Then
            a(i) = rng(i) / (rng(i + 1))
        ElseIf rng(i) > 0 And rng(1 + 1) > 0 Then
            a(i) = rng(i + 1) / rng(i)
        ElseIf rng(i) < 0 And rng(i + 1) > 0 Then
            a(i) = (rng(i + 1) - rng(i)) / (-rng(i))
        Else
            a(i) = 0
        End If
Next i

average = ... 
TILLV = average

End Function

Like I said, nothing works. I can't even figure out how to find the lenght of the input data (which is just a row of cells). When I try to use UBound i only get a message saying that it 'needs to be a matrix'.

[#2]

Hello there,

Can you give an example of how you're using the formula? When you say 'length of the input data', can you please be more specific and tell us exactly what that means? It's confusing. I'm not sure if you mean the actual length of the Range reference or the number of cells in the Range. Give a few examples of how it would work. For the best results, give us the cell values being referenced along with the desired output.