INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Remember Me

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Just copy and paste the

# SLOPE function in VBA

Share

## 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?

Tim

### RE: SLOPE function in VBA

#### CODE

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

### RE: SLOPE function in VBA

(OP)
Zathras,
Thanks for the help. The solution you provided worked like a charm.

Thanks,
Tim

### RE: SLOPE function in VBA

Anyone know how I would put my array in here
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

how many items are in your array

Chris

### RE: SLOPE function in VBA

well it may change it depends on the users selection

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

try

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

slope is used to measure regression of a time period

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

well it should, however, you have to list the items inside the array()

Do you have them in an array in vba?

Do you have them in certain cell locations?

### RE: SLOPE function in VBA

I have them in an array in vba. By the way not that it matters this is in access on a command button

Click

the button collects the array items

then my slope function

which get present on a form

### RE: SLOPE function in VBA

I have tried your suggestion however the a and b variable are strings which the slope function does not like

### RE: SLOPE function in VBA

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

Is that what you wont?

Fane Duru

### RE: SLOPE function in VBA

Im note sure what it does (new to arrays)

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

it gives me "unable to get the Slope property of the WorksheetFunction class" error for both examples

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

How are arrGIC and arrTime declared and populated ?

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

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

i worked it out!!! it did not like the currency data type changed it to double and all is well thanks for those that replied it got the brain juices going

### RE: SLOPE function in VBA

A simpler way:
'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

OOps, replace this:
Dim arrGIC() As Long, arrtime() As Integer
By this:
Dim arrGIC() As Double, arrtime() As Integer

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

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!