INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a Computer / IT professional? Join TekTips now!
 Talk With Other Members
 Be Notified Of Responses
To Your Posts
 Keyword Search
 OneClick Access To Your
Favorite Forums
 Automated Signatures
On Your Posts
 Best Of All, It's Free!
*TekTips's functionality depends on members receiving email. By joining you are opting in to receive email.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden.

tmreic (TechnicalUser) (OP) 
3 Mar 04 9:59 
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 Yvalues and Xvalues 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 (Programmer) 
3 Mar 04 10:13 
CODEApplication.WorksheetFunction.Slope(Array(1, 4, 7), Array(1, 2, 3)) 

tmreic (TechnicalUser) (OP) 
3 Mar 04 11:14 
Zathras, Thanks for the help. The solution you provided worked like a charm.
Thanks, Tim 

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 (TechnicalUser) 
26 Jul 05 20:39 
how many items are in your array
Chris 

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 (TechnicalUser) 
26 Jul 05 21:05 
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 

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 (TechnicalUser) 
26 Jul 05 21:50 
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?


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 

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

FaneDuru (TechnicalUser) 
28 Jul 05 11:28 
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 

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 (MIS) 
28 Jul 05 19:21 
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 TekTips questions? Have a look at FAQ2192884 or FAQ1812886 

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 (MIS) 
28 Jul 05 19:33 
How are arrGIC and arrTime declared and populated ? Hope This Helps, PH. Want to get great answers to your TekTips questions? Have a look at FAQ2192884 or FAQ1812886 

'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 

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 (MIS) 
28 Jul 05 21:01 
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 TekTips questions? Have a look at FAQ2192884 or FAQ1812886 

PHV (MIS) 
28 Jul 05 21:02 
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 TekTips questions? Have a look at FAQ2192884 or FAQ1812886 

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 



