indyng
asked on
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'
LinearRegress = Excel.WorksheetFunction.LI
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
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())
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'
LinearRegress = Excel.WorksheetFunction.LI
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
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())
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Lin
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.Lin
Now, the equation would be: y = -2x + 16
If you want only the slope, you can use:
? Join(WorksheetFunction.Lin
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.Li
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.LinEs
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°)
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.Lin
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.Lin
Now, the equation would be: y = -2x + 16
If you want only the slope, you can use:
? Join(WorksheetFunction.Lin
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.Li
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.LinEs
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°)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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