N00b problem with #NAME?

[tbnz]

For context, I am an experienced now retired programmer with expertise in Access VBA, and a some experience of Excel, but not Excel VBA.
I am trying to create a custom function. As a test, I created this code:

Function BIRTHDAY()
Dim answer As String
Dim Startdate, Enddate As Date

Startdate = "3/3/1970"
Enddate = "5/6/1970"
answer = Application.WorksheetFunction.Days360(Startdate, Enddate)
Debug.Print answer
BIRTHDAY = answer
End Function

If I run this code it correctly prints 92 in the immediate window.
If I put BIRTHDAY() in a cell, I get #NAME?

I suspect I'm missing something obvious, so please be kind
What am I missing?

 

[Michael M]

Try changing the startdate and enddate to a cell reference...then fill those cells with the dates

Startdate = Cells(1, 1)
Enddate = Cells(2, 1)

 

[JGordon11]

Do you have multiple workbooks open? The birthday function needs to be in a module in the same workbook from whence it is being called, otherwise you need to specify where to find the birthday function. Also, Startdate is dimensioned as variant. If you don't specifiy a type then Variant is the default used. Looks like it should be

Dim Startdate as Date, Enddate As Date

 

[tbnz]

Do you have multiple workbooks open? The birthday function needs to be in a module in the same workbook from whence it is being called, otherwise you need to specify where to find the birthday function. Also, Startdate is dimensioned as variant. If you don't specifiy a type then Variant is the default used. Looks like it should be

Dim Startdate as Date, Enddate As Date

The module is in "Thisworkbook". Thanks for the heads up about the declaration - I'm pretty rusty these days. In the "real" function the dates will be the date of birth from a cell reference and TODAY().

 

[JGordon11]

Put your code in a standard module, not the ThisWorkbook module. Use Insert Module to create a standard module.

 

[tbnz]

Try changing the startdate and enddate to a cell reference...then fill those cells with the dates

Startdate = Cells(1, 1)
Enddate = Cells(2, 1)

In the "real" function the dates will be the date of birth from a cell reference and TODAY(). So the function invocation in the spreadsheet will be =BIRTHDAY(D2) (for instance) and the function declaration will be function(DOB), so start date and end date will be redundant.

 

[tbnz]

OK, I solved it using the function wizard.

1. Selected the cell where I wanted the function.
2. On the "formulas" tab, clicked "insert function".
3. Selected the category "user defined"
4. Selected "PERSONAL.XLSB!BIRTHDAY()"

and that was inserted as the formula and returned the correct result,
Nothing I had read anywhere suggested I needed to code the formula that way.

Thanks for the help and suggestions.

 

[Alex Blakenburg]

Nothing I had read anywhere suggested I needed to code the formula that way.

I probably wouldn't consider putting a UDF in my personal macro workbook but since you have Mynda convers it on her MyOnlineTraining site here.
https://www.myonlinetraininghub.com...personal-xlsb-for-user-defined-functions-udfs
She also mentions renaming the Project and Adding a reference to the project in the workbook using the UDF under Tools > Reference.

 

[RoryA]

Nothing I had read anywhere suggested I needed to code the formula that way

You only have to do that if the function is in a different workbook that is not an add-in. Also bear in mind that if you need others to use the file, the function won't work for them.