Do User-Defined-Functions accept named arrays as arguments?

Do User-Defined-Functions accept arrays as arguments?

I am trying to create a simple formula to count the number of dates in a named array that fall within a specified month. I created a User Defined Function to help simplify the formula. The UDF is:

Function FOMONTH(inputdate) As Date ‘First of Month
FOMONTH = DateSerial(Year(inputdate), Month(inputdate), 1)
End Function

The UDF appears to work when its argument is a single value, but NOT when its argument is a named array. In contrast, Excel built-in functions work either way.

For example, this formula works and returns the correct value:
{=COUNT(IF(( DATE(YEAR(SaleDate),MONTH(SaleDate),1)=FOMONTH($A20)),SaleDate,FALSE))}

However, this formula does NOT work, even though the only difference is that I have used my UDF instead of Excel built-in functions:
{=COUNT(IF(( FOMONTH(SaleDate)=FOMONTH($A20) ),SaleDate,FALSE))}

Is there any way to make a UDF accept an array as an argument, just like the Excel built-in functions ? or am I doing something wrong?

Thanks for any response

Thanks Jbeaucaire and Andy, for your responses.
Jbeaucaire: I will try to attach my test workbook file to this note. The workbook has two worksheets; the first one contains the data; the second one contains the array formulas in two columns. The array formulas in the first column use Excel built-in functions and give correct answers. The array formulas in the second column use my User Defined Function but do NOT give correct answers. Thanks for any suggestions, corrections, or improvements.
Andy: My original question was that Excel built-in functions seem to handle either single values or arrays as arguments. My UDF will handle single values as arguments correctly but not arrays as arguments. I can make it work using only Excel built-in functions, but would prefer to use my UDF as indicated above. This formula is part of more complicated calculations so that I would prefer to use UDF's as much as I can. So maybe the question is: is there something that I need to do within my UDF that will allow it to process correctly BOTH single values and arrays as arguments, just like Excel built-in functions apparently do?
Thanks again .

Thanks so much Andy ! Your formula works great. Now I want to understand what you did. I’m not very familiar with VBA. Here are some questions:

1. Your module includes two functions, XFOMONTH and FOMONTH. Is that common ? I thought that each function had to have its own module, and each module could only contain one function. Is that wrong?

2. Your function XFOMONTH accepts a single variable as an argument, but the formula only calls FOMONTH, not XFOMONTH. How does the function (or module) know when to use XFOMONTH instead of FOMONTH? or does the module try them both and take whichever one works? or is there some test that’s not obvious that determines which function to use?

3. What was the reason why you used ReDim instead of Dim for the array inside the function?

Thanks again, I really appreciate it.

Answered my first question in the previous note with further research:

From: http://www.cpearson.com/excel/writin...ionsinvba.aspx
“A module can contain any number functions, so you can put many functions into a single code module...Do not give the same name to both a module and a function (regardless of whether that module contains that function). Doing so will cause an untrappable error.”

I'm still wondering about #2 and #3 from previous note. Thanks !!

Answered 2nd question, also: I just removed the old function xfomonth and everything still worked, so xfomonth apparently wasn't used at all. Also, a user defined function written to accept an array as an argument will apparently ALSO work with a single value as an argument, since that's what's happening now. I didn't know that.