Using a Macro as a fuction

[Happy_Dog]

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.

[NateO]

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

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

[Happy_Dog]

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.

[NateO]

Oh. Whoops . Sorry 'bout the misread, see

=Networkdays()

_________________
Cheers, NateO

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

[Mark O'Brien]

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

[Robb__]

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__

[Happy_Dog]

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.

[NateO]

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 ]

[Happy_Dog]

All right that will work then.
Thanks everyone.

[Mark O'Brien]

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.