Using a Macro as a fuction

Happy_Dog

New Member
Joined
Apr 25, 2002
Messages
16
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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)")
 
Upvote 0
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.
 
Upvote 0
Oh. Whoops :oops:. Sorry 'bout the misread, see

=Networkdays()

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-26 10:38
 
Upvote 0
You would do something like this:

<pre>
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 </pre>

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

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

HTH
 
Upvote 0
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__
 
Upvote 0
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.
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-26 10:37
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top