## SLOPE function in VBA

## SLOPE function in VBA

(OP)

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

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

## RE: SLOPE function in VBA

## CODE

## RE: SLOPE function in VBA

Thanks for the help. The solution you provided worked like a charm.

Thanks,

Tim

## RE: SLOPE function in VBA

my values are store would I have to move through the array

the example given is this

Application.WorksheetFunction.Slope(Array(1, 4, 7), Array(1, 2, 3))

Mine shold be somthing like this

Application.WorksheetFunction.Slope(Array(), Array1())

i need every item from the array

## RE: SLOPE function in VBA

Chris

## RE: SLOPE function in VBA

could be a 6 mth time period so 6 ot total from start to finish which every month grows 1 to whenever

## RE: SLOPE function in VBA

dim a,b,counter

a="array("

b="array("

for counter = 1 to 6

if array(counter) <> ""

a=a +yarray(counter) &","

b=b +xarray(counter) &","

end if

a=a &")"

b=b & ")"

next counter

Application.WorksheetFunction.Slope(a,b)

ck1999

## RE: SLOPE function in VBA

not sure this is going to work

Application.WorksheetFunction.Slope(Array(30, 35, 54), Array(1, 2, 3))

the first array are values which are taken from a table the second array is time periods so 3 time periods

in my function both arrays change depending on user selection.

this is way i would have thought my formula should be

Application.WorksheetFunction.Slope(Array(), Array1())

## RE: SLOPE function in VBA

Do you have them in an array in vba?

Do you have them in certain cell locations?

## RE: SLOPE function in VBA

Click

the button collects the array items

then my slope function

which get present on a form

## RE: SLOPE function in VBA

## RE: SLOPE function in VBA

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())

Is that what you wont?

Fane Duru

## RE: SLOPE function in VBA

my existing arrays have the items

my value array arrGIC(has the values)

my time series array arrTime(has values)

I understand that i need to get the values out of the arrays and put them in the slope function so

exApp.WorksheetFunction.Slope(arrGIC(all values),arrTime(all values))

I know how to load the variables into a string using For/Next but it doesn't accept the string data type

so FaneDuru im not sure what your code does except put my array into another array

## RE: SLOPE function in VBA

exApp.WorksheetFunction.Slope(arrGIC(), arrTime())

Or this ?

exApp.WorksheetFunction.Slope(arrGIC, arrTime)

Hope This Helps, PH.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

## RE: SLOPE function in VBA

however i know this function works cause i have manually put in some figurs eg

Slope = exApp.WorksheetFunction.Slope(Array(17729999, 17899896), Array(1, 2))

which gives me the answer i want but hardcoding does not work as sometimes there are 2 figures to calculate other times there might be 40

## RE: SLOPE function in VBA

Hope This Helps, PH.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

## RE: SLOPE function in VBA

Dim arrGIC() As Currency

rs.MoveFirst

Do Until rs.EOF

intUpBoundcause = rs.RecordCount

ReDim arrGIC(1 To intUpBoundcause) As Currency

For inCountercause = 1 To intUpBoundcause

arrGIC(inCountercause) = rs![12MoRollSUM]

rs.MoveNext

Next inCountercause

Loop

'loads time serires array

counter = 1

Store = 0

Dim arrtime() As Integer

rs.MoveFirst

Do Until rs.EOF

intUpBoundcause1 = rs.RecordCount

ReDim arrtime(1 To intUpBoundcause1) As Integer

For inCountercause1 = 1 To intUpBoundcause1

arrtime(inCountercause1) = counter + Store

Store = arrtime(inCountercause1)

rs.MoveNext

Next inCountercause1

Loop

## RE: SLOPE function in VBA

## RE: SLOPE function in VBA

'loads GIC and time series arrays

Dim arrGIC() As Long, arrtime() As Integer

rs.MoveLast

rs.MoveFirst

intUpBoundcause = rs.RecordCount

ReDim arrGIC(1 To intUpBoundcause)

ReDim arrtime(1 To intUpBoundcause)

counter = 1

Store = 0

For inCountercause = 1 To intUpBoundcause

arrGIC(inCountercause) = rs![12MoRollSUM]

arrtime(inCountercause) = counter + Store

Store = arrtime(inCountercause)

rs.MoveNext

Next inCountercause

Hope This Helps, PH.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

## RE: SLOPE function in VBA

Dim arrGIC() As Long, arrtime() As Integer

By this:

Dim arrGIC() As Double, arrtime() As Integer

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

## RE: SLOPE function in VBA

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