Results 1 to 11 of 11
  1. #1
    Join Date
    May 2004
    Posts
    8

    Unanswered: Importing Excel function days360()

    Can anyone help me on this subject?

  2. #2
    Join Date
    Dec 2002
    Posts
    12
    This tip requires that you're somehow familiar with VBA, cause I don't know if there's an easy way out.

    You'll have to go to Modules (quick key alt-f11). Tools->References: select Microsoft Excel 9.0 (or whatever number it is) and then OK. The code to use in your (for example) Module1 is:

    Function D360(begin_date, end_date)
    D360=Excel.WorksheetFunction.Days360(begin_date, end_date)
    end function

    After this you can use this own function in your project. For example, right-click on text field on a form, select first ("create event" or something?) then OK. Open Functions folder and then select "your filename" folder, then you'll get Module1 showing.

    Hope this helps.

    JN

  3. #3
    Join Date
    Apr 2009
    Posts
    3

    Days360 Access

    Hi,

    So I found the thread how to incorporate Excel functions into Access, specifically Days360. Unfortunately, I am having a hard time making it work. following does not work:


    As the instructions said, I went to tools/references and enabled Microsoft Excel 12.0 Object Library and then pasted

    Function D360(begin_date, end_date)
    D360 = Excel.WorksheetFunction.days360(begin_date, end_date)
    End Function

    into the module body.

    When I try using it in a query, the following message comes up "Undefined function 'D360' in expression". Not too sure what to do.

    Could definitely use the help.

    Thanks.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by weberdw
    As the instructions said, I went to tools/references and enabled Microsoft Excel 12.0 Object Library and then pasted

    Function D360(begin_date, end_date)
    D360 = Excel.WorksheetFunction.days360(begin_date, end_date)
    End Function

    into the module body.

    When I try using it in a query, the following message comes up "Undefined function 'D360' in expression". Not too sure what to do.
    What module did you put this in? If you're using it in a query, I think you need it in a public module. You may also need to explicitly define the function as public.
    Me.Geek = True

  5. #5
    Join Date
    Apr 2009
    Posts
    3
    Nick,

    Apologies but I am very much a novice when it comes to modules. Where do I go to make it a "public" module?

  6. #6
    Join Date
    Apr 2009
    Posts
    3
    progress....?

    Nick, so I changed the module to the following:
    Option Compare Database

    Function Day360(begin_date, end_date)
    Day360 = Excel.WorksheetFunction.Days360(begin_date, end_date)
    End Function


    and now I am getting this response:

    "Run-time error '1004':
    Invalid number of arguments."'

    Below this message are a few button choices: End, Debug, and help

    When I click on "end" it goes to the query and it seems the query is actually running and I from a first glance it seems to be calculating Days360 correctly

    When I click on "debug"

    It takes me to the module code:
    Function Day360(begin_date, end_date)
    Day360 = Excel.WorksheetFunction.Days360(begin_date, end_date)
    End Function

    with the second line highlighted.

    Thanks for you help!
    Dan

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What expression is in your query?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Do not forget that in a query the arguments of a function must be separated by a ; not a ,

    Have a nice day!

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    They do? That's a new one on me.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Hi StarTrekker

    Indeed if you use the query designer to create a query and use a function with parameters in it, those parameters must be seperated by a semicolon, not by a comma. In my example a French version of Access was used so VraiFaux stands for IIf and Comme stands for Like.

    Have a nice day!
    Attached Thumbnails Attached Thumbnails IIf.jpg  

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Interesting. I use commas and it works, so I guess it doesn't really matter?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •