• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 872
  • Last Modified:

How to use this function in Access?

Hi Experts,

I have the following. How can this work? Thanks


Public Function LinearRegress(arrx() As Double, arry() As Double) As Double

'LINEST(known_y's,known_x's,const,stats)


     LinearRegress = Excel.WorksheetFunction.LINEST(arrx(), arry())
     
End Function


Public Sub Import_S1_BW()

'Used to import multiple BW S1 Reports

Dim arrx() As Variant
Dim arry() As Variant

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim tbl As TableDef


Dim strSQL As String
Dim strFilename As String

Dim PathIs As String

'Detect path
PathIs = Application.CurrentProject.Path

Set db = CurrentDb()

strSQL = "DELETE FROM [BW S1 Data]"
CurrentDb.Execute (strSQL)

arrx(0) = 1
arrx(1) = 2

arry(0) = 2
arry(1) = 4

SlopeIs = LinearRegress(arrx(), arry())

0
indyng
Asked:
indyng
  • 2
  • 2
2 Solutions
 
donaldmaloneyCommented:
The function would be added to modules.
use a different hname for the name of the module and paste the cod in the module.

The Sub is not complete I dont see and End Sub   statement.
If you created a form and added a button.
on the On_click of the button add
Call  Import_S1_BW()
after the End sub of the On Click sub  add the:

Public Sub Import_S1_BW()
etc
etc


THen when you open tyhe form the code should run and use the excel function

I'm going to test it.

Don


a call
0
 
donaldmaloneyCommented:
Code should be something like this:
Still working on the results.

Also need to have Microsoft excel in the  references.
(click on modules, open a function , on top click Tools>references and check the MS excel modules line)


Private Sub Command4_Click()

        Stop
    'Screen.PreviousControl.SetFocus
    'DoCmd.FindNext
Call Import_S1_BW

   
End Sub
Public Sub Import_S1_BW()

'Used to import multiple BW S1 Reports

Dim arrx() As Double
Dim arry() As Double

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim tbl As TableDef


Dim strSQL As String
Dim strFilename As String

Dim PathIs As String

'Detect path
PathIs = Application.CurrentProject.Path

'Set db = CurrentDb()

'strSQL = "DELETE FROM [BW S1 Data]"
'CurrentDb.Execute (strSQL)
ReDim arrx(2)
ReDim arry(2)
arrx(0) = 1
arrx(1) = 2

arry(0) = 2
arry(1) = 4

SlopeIs = LinearRegress(arrx(), arry())
Stop

End Sub
0
 
harfangCommented:
Several comments here...


LineEst() returns two numbers as an array. It can return more than two numbers, depending on the arguments (see Excel help on that function).

Open Excel, switch to VB (Alt+F11) and open the immediate pane (Ctrl+G) to test the following:

    ? Join(WorksheetFunction.LinEst(array(3,2)))

You will get two numbers: the slope and the intercept. The formula for the regression line is: y = -x + 4

You can also provide X values, as in:

    ? Join(WorksheetFunction.LinEst(array(10,12),array(3,2)))

Now, the equation would be: y = -2x + 16

If you want only the slope, you can use:

    ? Join(WorksheetFunction.LinEst(array(10,12),array(3,2)))(1)

To get the number -2.


Another potential problem is that you are calling an Excel function from Access. You cannot use the name of the library (Excel) in that way, or at least expect problems down the road. If you simply call:

    Excel.WorksheetFunction.LinEst()

Access will attempt to find Excel and memory or load an instance silently. At the next run, it will use the same pointer (unless you have reset you VB code), which might no longer be valid.

When programming from Access, always refer to a valid object pointer to an open instance of Excel.

    Dim XL As Excel.Application

    ' open or find an instance of Excel here, and then use:
    XL.WorksheetFunction.LinEst()


Finally, it's really quite easy to calculate a linear regression in a query or on an array. You might not need Excel at all.

Cheers!
(°v°)
0
 
harfangCommented:
Correction above. To get only the slope, use:

    ? WorksheetFunction.LinEst(array(10,12),array(3,2))(1)

(°v°)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now