Custom Function

[2077delta]

This should be simple for the experts out there. I have a worksheet formula that looks like this

=CHOOSE(weekday(B8)),"M","T","W","TH","F","S","SU")

where cell b8 is the day of the month.

How do I write a VBA function to substitute for this formula so all i have to reference is the date cell, example "=dayofweek(ref)"?

[Jay Petrulis]

Hi,

Try...

Function DayOfWeek(RefCell)
Dim fn As Object, wkday As Byte

Set fn = Application.WorksheetFunction
wkday = fn.WeekDay(RefCell, 2)
DayOfWeek = fn.Choose(wkday, "M", "T", "W", "TH", "F", "S", "SU")

End Function

Bye,
Jay

EDIT: Variables, fn and wkday declared. RefCell kept as variant for flexibility. Change to "RefCell As Range" to force the function to reference a cell range.

[ This Message was edited by: Jay Petrulis on 2002-05-24 12:16 ]

[2077delta]

Thanks for the answer. I did get the function to work another way. If you have the time could you please explain the difference in your code and mine and and advantage of one over the other. I'm a firm believer in learning to do things the right way (although frequently what I learn is through trial and error), so if there is some potential error in what I did I'd like to know. Here is my code.

Function DOW(ref) As String

DOW = Choose(Weekday(ref), "M", "T", "W", "TH", "F", "S", "SU")

End Function

Thanks for your time

[Jay Petrulis]

On 2002-05-24 12:42, 2077delta wrote:
Thanks for the answer. I did get the function to work another way. If you have the time could you please explain the difference in your code and mine and and advantage of one over the other. I'm a firm believer in learning to do things the right way (although frequently what I learn is through trial and error), so if there is some potential error in what I did I'd like to know. Here is my code.

Function DOW(ref) As String

DOW = Choose(Weekday(ref), "M", "T", "W", "TH", "F", "S", "SU")

End Function

Thanks for your time

Nothing is wrong with your code. I forgot that both Choose and Weekday are VB functions as well as Excel functions. I have both of these used in another file, but completely blew it here. Yours is more efficient.

One thing, though. Do you want this to return the day after the reference cell date?

Function DOW(ref) As String

DOW = Choose(WeekDay(ref, vbMonday), "M", "T", "W", "TH", "F", "S", "SU")

End Function

If I have Jan 1, 2002 in the cell, your function returns "W" although it was a Tuesday. The reference to the second argument above starts the week on a Monday, rather than the default Sunday.

[ This Message was edited by: Jay Petrulis on 2002-05-24 12:56 ]

[2077delta]

Thanks for the time and the tip.