excel's SLOPE function: use in Access VB

[Guest]

Hi,

cen enyone tell me how to use excel's SLOPE() function directly in access VB
(without opening the excel application)?

thank you

S.

 

[Douglas J. Steele]

This is from a response I have saved in my files. I'm sorry, but I don't
know into which category the SLOPE function fits:

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel 10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for example if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel Add-in. If
you're *really* intent on using it, you can access it from automation, see:
http://support.microsoft.com/?id=198571

 

[Terry Kreft]

The problem with your post is you don't state how you want to use the
function.

The Slope function in Excel accepts two arguments, each of which is a range
of cells for the x and y coordinates respectively. If you don't want to use
automation you are really stuck with recreating it in Access.

If you look in Excel help for the function it gives you the formula they
use.

b = Sum((x - Avg(x)) * (y - Avg(y))) / Sum((x - Avg(x))^2)

Depending on how you pass your ranges of data into the function determines
how you would handle this calculation.

If on the other hand you simply want to work this out from some data in a
table then you can use a query to obtain the result., for example:-

Where
graph_slope is the table containing the data
x is the field in graph_slope which contains the x coordinates
y is the field in graph_slope which contains the y coordinates

SELECT
(
Sum(
(
[x]-(SELECT AVG(x) FROM graph_slope)
)*(
[y]-(SELECT AVG(y) FROM graph_slope)
)
)
)/(
Sum(
(
[x]-(SELECT AVG(x) FROM graph_slope)
)^2
)
) AS b
FROM graph_slope