"Angela" wrote:
I have date in any cell and if I want to test age,
I just use below excel formula to see the age bracket.
I have managed it in as an array formula in excel [...].
=LOOKUP(TODAY()-Y2,
{0,7.51,14.51,21.51,30.51,45.51,60.51;
"0-7","8-14","15-21","22-30","31-45","46-60","60"})

First, your Excel formula should be:

=LOOKUP(TODAY()-Y2,{0,8,15,22,31,46,61;
"0-7","8-14","15-21","22-30","31-45","46-60","60"})

This assumes that Y2 contains only an Excel date, not date and time. If the
latter, use TODAY()-INT(Y2).


Angela wrote:
now I need it to be a UDF.

Why? If you call the UDF from an Excel formula, it will be much slower than
LOOKUP.


Angela wrote:
Function AgeBucket(strValue) As String
Dim strValue As Date
Dim Rng As Range
Dim Rng2 As Range
Rng = Array(0, 7.51, 14.51, 21.51, 30.51, 45.51, 60.51)
Rng2 = Array("0-7", "8-14", "15-21", "22-30", "31-45", "46-60", "60")
AgeBucket = Application.WorksheetFunction.Lookup(Today - strValue,
Rng; Rng2)
End Function

Numerous syntax errors. Also, the variable name "strValue" is misleading.
I presume you call it using AgeBucket(Y2), and I assume that Y2 contains an
Excel date, not the string "7/9/2011" for example. In that case, write:


Function AgeBucket(origDate As Date) As String
Dim ageLimit As Variant
Dim ageRng As Variant
ageLimit = Array(0, 8, 15, 22, 31, 46, 61)
ageRng = Array("0-7", "8-14", "15-21", "22-30", _
"31-45", "46-60", "60")
AgeBucket= WorksheetFunction.Lookup(Date - origDate, _
ageLimit, ageRng)
End Function


If origDate might contain time as well as a date, use Date - Int(origDate).


Note:
1. Declare type of parameters (origDate) in the Function statement.
2. Assign Array(...) to a Variant variable.
3. Use VBA Date function instead of Today(), which is an Excel function.