Error in passing data from a Function to a Subroutine

[Juggler_IN]

I have a function from which I am passing data to a subroutine and then populating the output. When I am using x, y as is, I am getting the output, but when I use arr1, arr2 instead I do not get an output. The code is:

Option Explicit
Option Base 1
Public Function Func1(ByVal x As Range, ByVal y As Range) As Variant


    Dim n As Long, i As Long, j As Long, k As Long, c As Long, n1 As Long, n2 As Long


    n = Application.WorksheetFunction.Max(Application.WorksheetFunction.Count(x), Application.WorksheetFunction.Count(y))
MsgBox n
    Dim arr1() As Double, arr2() As Double
    ReDim arr1(1 To n)
    ReDim arr2(1 To n)


    j = 0
    k = 1
    For i = 1 To n
        If Application.WorksheetFunction.IsNumber(x(i)) = False Or Application.WorksheetFunction.IsNumber(y(i)) = False Then
            j = j + 1
        Else
            arr1(k) = x.Cells(i, 1).Value2
            arr2(k) = y.Cells(i, 1).Value2
            k = k + 1
        End If
    Next


    ReDim Preserve arr1(1 To n - j)
    ReDim Preserve arr2(1 To n - j)


    Dim m As Double
    Dim d As Double


Call Proc1(x, y, m, d) ' This is working


Call Proc1(arr1, arr2, m, d) ' This is not working
    
    Func1 = (m / d)


End Function


Private Sub Proc1(ByVal x As Variant, ByVal y As Variant, ByRef m As Double, ByRef d As Double)


    Dim n As Long
    n = (x.Rows.Count + y.Rows.Count) / 2
    Dim c As Double    ' C(n,2)
    c = n * (n - 1) / 2


    d = Sqr((c) * (c - n / 2))
    m = Sqr(d + n)


End Sub

 

[RoryA]

You can't pass an array ByVal, it has to be ByRef. Also, an array doesn't have properties like Rows so your sub wouldn't work.

Edit: you can ignore the part about ByVal - I hadn't spotted you were using Variants.

 

[Juggler_IN]

So, how can I convert the array to a range which can then be passed?

 

[RoryA]

Only by putting the array into cells somewhere. Or just rewrite the sub to take an array.