Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

excel's SLOPE function: use in Access VB

Discussion in 'Information Technology' started by steven, Jul 28, 2006.

  1. steven

    steven
    Expand Collapse
    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.
     
  2. Loading...

    Similar Threads Forum Date
    The Slippery Slope of Religious Symbols in France Hard Talk Feb 10, 2010

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "steven" <steven@discussions.microsoft.com> wrote in message
    news:8A537615-47F7-49E2-9167-D38EB62C445E@microsoft.com...
    > Hi,
    >
    > cen enyone tell me how to use excel's SLOPE() function directly in access
    > VB
    > (without opening the excel application)?
    >
    > thank you
    >
    > S.
     
  4. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    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


    --

    Terry Kreft


    "steven" <steven@discussions.microsoft.com> wrote in message
    news:8A537615-47F7-49E2-9167-D38EB62C445E@microsoft.com...
    > Hi,
    >
    > cen enyone tell me how to use excel's SLOPE() function directly in access

    VB
    > (without opening the excel application)?
    >
    > thank you
    >
    > S.
     

Share This Page