Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Using a Macro as a fuction

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How can I call a vba function that I can use like the built in functions? for example I need the number of weekdays between two dates, similar to =DAYS360() which returns total number of days.

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Theoretically you can use the worksheetfunction class, but it was misfiring on my pc. So I settled on:

    Code:
    MsgBox Evaluate("=DAYS360(TODAY(),TODAY()-1)")

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the quick reply, but I'm not sure we're on the same track. I have created a function for the worksheet called GetWorkDays that takes two parameters, start date and end date. In a cell I want to do something like =GetWorkDays(4/26/2002,5/26/2002) and get the result in the cell.

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oh. Whoops . Sorry 'bout the misread, see

    =Networkdays()

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-26 10:38 ]

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You would do something like this:


    Public Function GetWorkDays (ByVal oStartDate As Date,ByVal oEndDate as Date)

    'Your code using oStartDate and oEndDate as you variables and then finally set GetWorkDays equal to your answer e.g.:

    GetWorkDays = oEndDate - oStartDate 'Would give you the time between your dates

    End Function


    To use this in your worksheet you would simply put in a cell:

    =GetWorkDays(4/26/2002,5/26/2002)

    HTH

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Happydog

    You don't really need to create a function for use in the worksheet. Use Networkdays.

    You do, however, need to have the Analysis Toolpak installed. To do this select Tools/AddIns and then select Analysis Toolpak.

    Once this is done, you will be able to us e the Networkdays function.

    Say you have a dtae in A1 and another in A2, in A3 enter =Networkdays(A1,A2) and the result will be the number of workdays between the two (A1 is start, A2 is end). If you want to range over a persiod containing public holidays, you may also allow for them. List the holidays, say in H1 to H5, the formula then becomes =Networkdays(A1,A2,H1:H5).

    Any help?

    Regards

    Robb__

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nate/Robb, I am looking into that as a possible solution. Will this work for other users if they do not have the Analysis ToolPak installed?

    Mark, I tried this and it does not seem to work for me. I'm just getting the #NAME?

    Thanks.

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Also take note, if you want to hard code the date's you'll need to do so using Excel's date serial number methodology, which is quite a bit of fun.

    I didn't realize this function was of the analysis toolpak variety. It comes with XL, just go tools->add ins, check the appropriate box and you're in like a dirty shirt, others should replicate this procedure...
    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-26 10:37 ]

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    All right that will work then.
    Thanks everyone.

  10. #10
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark, I tried this and it does not seem to work for me. I'm just getting the #NAME?

    Thanks.
    Probably because I just typed this one in the board and didn't test it, sorry. If you're interested I'll knock it up later, but it seems like there's a solution for you.

    Take it easy.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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