ExcelBanter - Using named ranges in a function

Hman

I don't get why you would want to pass a range of years to a function. The
function is only in one cell and can only return a value to one cell, unless
you are array entering it. It seems like it would be easier to use the cell
reference for the year that you want, then fill the formula across and the
let the cell reference change.

The problem that you are having is that you are passing an array of values
(e.g. 2204,2005,2006) to the function. It's trying to compare that array to
the results of the worksheetfunction calcs that you are doing, and is
getting an error. Any time you get an error in a UDF, you'll get the #Value
error.

If you really want to pass a range of years into your function, but only
want to use the cell in that range that's in the same column as the
function, then you need some code before your case statement that determines
what the value of year is. This function will accept a literal value
(2004), a cell reference (A1), or a range of cells (A1:D1 or a range name)
and convert that argument into the correct value.

Function GetYear(vYear As Variant) As Double

'Use a different variable internally for the year
Dim lYear As Long

'If we're passed a range
If TypeName(vYear) = "Range" Then

'If there's more than one cell in the range
If vYear.Cells.Count 1 Then

'Use the value of the cell that's in the same column
'as the cell where the function is.
lYear = vYear.Cells(1, Application.Caller.Column).Value
Else

'If only one cell, use that cell's value
lYear = vYear.Value
End If
Else

'If it's not a range, use whatever was passed
lYear = vYear
End If

GetYear = lYear

End Function

Note that the function doesn't have a lot of error checking in it. It
assumes the user is passing a number or a range, not text. Also, it assumes
that if it's passed a multicell range, that it's a single row of numbers and
that the function is called from the same column as one of the cells in the
range, like the multicell range is a heading and the function calls are
beneath it.

Hopefully that will get you started, even if it doesn't answer your
question.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Hman" wrote in message
om...
I have created a function that tells me how much of project's cost is
incurred in a particular year. So, all I need to do is enter the cost
of the project (Value), StartDate, EndDate and the Year, and, I get
the cost in that year as my output. If I want to see how the cost is
spread over several years, I create a row (e.g., 2004, 2005....2020)
and use the function to show me how the cost is spread over multiple
years.

The problem I have is in some of my models, the row of years
(2004-2020) is a named ranged called "Year". When I use the named
range in this formula, I get a #Value error. I need to have the
flexibility to use one number (e.g., 2005) or a named range. How can
I get rid of this #Value error. The actual function is shown below:

Function Spread(Value As Double, StartDate As Double, EndDate As
Double, Year)
'
'Takes a value (expense or income) and returns the amount incurred
'in a particular Year given the StartDate and EndDate (entered in
fractional
'terms e.g., 2010.50).

Select Case Year
'If Year is less than Start Year then enter 0
Case Is < Application.WorksheetFunction.Floor(StartDate, 1): Spread =
0
'If Year is greater than End Year then enter 0
Case Is Application.WorksheetFunction.Floor(EndDate, 1): Spread = 0
'If Year equals StartDate; and, StartDate and EndDate are both in the
same year then allocate Value to that year
Case Is = Application.WorksheetFunction.Floor(StartDate, 1) And
Application.WorksheetFunction.Floor(StartDate, 1) =
Application.WorksheetFunction.Floor(EndDate, 1):
Spread = Value
'If this is anything other than the first year, then take the minimum
of one (one full year) or EndDate - Year (fraction at the end) and
multiply it time the incremental Value
Case Is = Application.WorksheetFunction.Ceiling(StartDate, 1): Spread
= (Value / (EndDate - StartDate)) *
Application.WorksheetFunction.Min((EndDate - Year), 1)
'If this is the first year, then calculate the fraction of the year
times the incremental Value
Case Is = Application.WorksheetFunction.Floor(StartDate, 1): Spread =
(Application.WorksheetFunction.Ceiling(StartDate, 1) - StartDate) *
(Value / (EndDate - StartDate))
End Select
End Function

Thank you in advance