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

 

[Zathras]

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

 

[tmreic]

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

Thanks,
Tim

 

[thefox149]

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

 

[ck1999]

how many items are in your array

Chris

 

[thefox149]

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

 

[ck1999]

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

 

[thefox149]

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

 

[ck1999]

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?

 

[thefox149]

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

 

[thefox149]

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

 

[FaneDuru]

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

 

[thefox149]

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

 

[PHV]

And what about this ?
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

 

[thefox149]

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

 

[PHV]

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

 

[thefox149]

'loads GIC array
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

 

[thefox149]

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

 

[PHV]

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

 

[PHV]

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