Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

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

Join Tek-Tips
*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

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

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 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 (Programmer)
3 Mar 04 10:13

CODE

Application.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
thefox149 (TechnicalUser)
26 Jul 05 20:19
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
thefox149 (TechnicalUser)
26 Jul 05 20:56
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
thefox149 (TechnicalUser)
26 Jul 05 21:30
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?

thefox149 (TechnicalUser)
26 Jul 05 23:06
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 (TechnicalUser)
28 Jul 05 0:45
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
thefox149 (TechnicalUser)
28 Jul 05 18:52
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 Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

thefox149 (TechnicalUser)
28 Jul 05 19:26
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 Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

thefox149 (TechnicalUser)
28 Jul 05 20:20
'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 (TechnicalUser)
28 Jul 05 20:41
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 Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

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 Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

FaneDuru (TechnicalUser)
29 Jul 05 7:12
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close