Please help me with a simple VBA function

[laurieb]

Hi! I'm trying to write a piece of code to calculate the gestational age of expectant mothers based on their estimated due date. This is a first attempt at making a user defined function in excel and I can't seem to figure out why it won't work. Any help you can give would be much appreciated.

Thank you!
Laurie

Function GA(edc As Date, dd As Date, today As Date) As String
'Gestational Age (GA)
If IsMissing(today) Then
today = Date
End If
If IsMissing(edc) Then
GA = ""
Else
Select Case dd
Case IsMissing
GA_Days_Total = 280 + Int(edc - today)
Case Is > today
GA_Days_Total = 280 + Int(edc - dd)
Case Is = today
GA_Days_Total = 280 + Int(edc - dd)
End Select

GA_Weeks = Fix(GA_Days_Total / 7)
GA_days = GA_Days_Total Mod 7

GA = CStr(GA_Weeks) & " " & CStr(GA_days) & "/7 weeks"
End If

End Function

[Barrie Davidson]

How about this (note that the inputs are dates entered as yyyymmdd - so October 1, 2006 would be 20061001):

Please Login or Register  to view this content.

Does this help?

[laurieb]

For some reason I can't get it to work. Did it work for you?

Thanks for your help!
Laurie

[MrShorty]

In what way is it not working? Error messages? Returning the wrong answer?

[laurieb]

It gives me a #name error. Not sure why.

Thanks again for the help everyone.
Laurie

[laurieb]

Also, I'd really like to keep the date in date format on my spreadsheet.

Thanks!

[MrShorty]

Where have you put the code? In order to be available to Excel, UDF code must be put in a regular module, not in the code page associated with a worksheet or this workbook.

[Barrie Davidson]

Also, I'd really like to keep the date in date format on my spreadsheet.

Thanks!

Then how about changing to:

Please Login or Register  to view this content.

Regards,

[laurieb]

IT'S WORKING!!!! THank you so much. I think the problem was that I was leaving today and delivery date blank. I need to somehow fix this so that they have defaults.

Otherwise, it's great. Thanks so much for the help!!!
Laurie

[MrShorty]

It looks to me like what you want to do is make all of your arguments optional.

Function GA(optional edc, optional dd, optional today)

see VBA help for the function statement