SLOPE function in VBA

[tmreic]

Hi,

I am creating a VBA application and would like to use do linear regression in VBA. I am trying to use the Slope( ) function, but it just doesn't seem to work no matter what I use for arguments.

Scenario:
Given three collinear data points {(1,1), (2,4), (3,7)}, you want to
determine the equation for the line. In a cell in an Excel spreadsheet, you
can put the following formula:

=Slope({1,4,7}, {1,2,3})

This will be evaluated to "3", because the formula of the line described by
those three points is
y = 3x -2

In VBA, you can access this function from the
slope = Application.WorksheetFunction.slope(y args, x args); however, I cannot figure out how to represent the Y-values and X-values so that the function will accept them. I have tried to minic the way you would do it in a worsheet but it does not work. I currently have my X and Y arguments stored in Array and would like retrieve the X and Y arguments from this array and use them in the slope function.


Anyone run into this and, if so, how did you solve it?

Thank you in advance,
Tim

 

[FaneDuru]

Option Base 1

Sub Test
Dim A(), B(), X As Long
A() = Array(1, 4, 10, 2)
X = 4
ReDim B(X)
For I = 1 To X
B(I) = I
Next I
Debug.Print Application.WorksheetFunction.Slope(A(), B())
End Sub

In order to work you have to put at the beginning of the module "Option base 1".

Fane Duru