How to call an entire array into an argument

[Reset]

I am trying to use the slope function where the known x and y values are placed in an array of size i. How do I reference the entire array in the function call?

s = Application.WorksheetFunction.Slope(y, x)

Where y and x are arrays of size i.

 

[shg]

That should work as is, assuming both are 1D arrays.

 

[Rick Rothstein]

I agree with shg, that should work as you have written it. Here are two examples which work for me using it (depending on how you load the arrays)...

Dim Xs As Variant, Ys As Variant
Xs = Range("A1:A4")
Ys = Range("B1:B4")
MsgBox WorksheetFunction.Slope(Xs, Ys)

Dim Xs() As Variant, Ys() As Variant
Xs = Array(1, 3, 11, 16)
Ys = Array(2, 6, 22, 32)
MsgBox WorksheetFunction.Slope(Xs, Ys)

 

[Reset]

Nope, I get "Compile error: Expected Function or variable"

I am doing this to multiple rows with different amounts of data. It is arranged in x/y pairs across the row, so have to piecewise fill the arrays. Here's the full sub

Sub t()
For x = 2 To Application.WorksheetFunction.CountA(Columns(1))
MsgBox x
    c = Application.WorksheetFunction.CountA(Rows(x))
    ReDim d((c - 1) / 2) As Variant
    ReDim v((c - 1) / 2) As Variant
    For y = 2 To c Step 2
        d(y / 2) = Cells(x, y)
        v((y / 2) + 1) = Cells(x, y + 1)
    Next y
    s = Application.WorksheetFunction.Slope(v, d)
    Cells(x, y + 1) = s
Next x
End Sub

 

[shg]

This worked fine for me:

Sub t()
    Dim iRow        As Long
    Dim iCol        As Long
    Dim nCol        As Long
    Dim adX()       As Double
    Dim adY()       As Double
    Dim dSlp        As Double
 
    For iRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        nCol = Cells(iRow, Columns.Count).End(xlToLeft).Column
        ReDim adX(1 To nCol \ 2)
        ReDim adY(1 To nCol \ 2)
 
      For iCol = 1 To nCol Step 2
            adX(iCol \ 2 + 1) = Cells(iRow, iCol).Value2
            adY(iCol \ 2 + 1) = Cells(iRow, iCol + 1).Value2
        Next iCol
 
        dSlp = WorksheetFunction.Slope(adY, adX)
        Cells(iRow, iCol) = dSlp
    Next iRow
End Sub

 

[Rick Rothstein]

Nope, I get "Compile error: Expected Function or variable"

I am doing this to multiple rows with different amounts of data. It is arranged in x/y pairs across the row, so have to piecewise fill the arrays. Here's the full sub

Sub t()
For x = 2 To [COLOR=darkred]Application.WorksheetFunction.CountA(Columns(1))[/COLOR]
MsgBox x
    c = [COLOR=green]Application.WorksheetFunction.CountA(Rows(x))[/COLOR]
    ReDim d((c - 1) / 2) As Variant
    ReDim v((c - 1) / 2) As Variant
    For [COLOR=darkorchid]y = 2[/COLOR] To c Step 2
        d(y / 2) = Cells(x, y)
        v((y / 2) + 1) = Cells(x, y + 1)
    Next y
    s = Application.WorksheetFunction.Slope(v, d)
    Cells(x, y + 1) = s
Next x
End Sub

I think we need to know what data is where. The call in red looks down Column A to see how many rows are involved. The For loop index starts counting from 2 meaning the first d value is in Column 2. That must mean Column 1 contains non-data headers. However, the call shown in green counts everything on each row including the header (because you used COUNTA). I think your subscript overflow is being generated here.