Call slope function from VBA

Dear Friends,

I have to local ranges, xL and yL that I have assigned some values to each of them. Now, I want to call the slope and the intercept functions to calculate their regression statistics.

I expected to do something like:

slopeL = "=SLOPE(yL,xL)"

Any ideas?

G.

Suppose the X is in A1:A10, and Y is in B1:B10, then use


set xL = Range("A10:A10")
set yL = Range("B10:B10")

slopeL = Application.WorksheetFunction.Slope(yL, xL)


- Mangesh

Hello G.,

The worksheet formulae are SLOPE and INTERCEPT.

Using the Excel Formula Bar :

=SLOPE(Known_Ys, Known_Xs)
=INTERCEPT(Known_Ys, Known_Xs)

Using VBA:

Worksheets("Sheet1").WorksheetFunction.Slope(Known_Ys, Known_Xs)
Worksheets("Sheet1").WorksheetFunction.Intercept(Known_Ys, Known_Xs)
_________________________________________________________________

This should get you going in the right direction,
Leith Ross

Thank you,

problem solved.

G.