Create a user-defined function

[Max]

Am more familiar with Access, but have created a function in XL07 as follows:

Public Function AgeIt()
Selection.NumberFormat = "0.00"
ActiveCell.FormulaR1C1 = "=(TODAY()-RC[-1])/365"
AgeIt = ActiveCell.FormulaR1C1
End Function

to calculate a person's age when the birthday is in the cell to the left.

When I select the function and insert it into a cell, I get =AgeIt() on the
formula bar, but #VALUE! in the cell.

What am I missing???

Thanks in advance for your help.

 

[Bernard Liengme]

Functions cannot change the content or format of a cell
They can return a value
Try this

Public Function AgeIt(mycell)
AgeIt = (Date - mycell.Value) / 365
End Function

In the worksheet call this with, for example, =AGEIT(A1)

See also:
1) David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm
2) The DATEDIF function
http://www.cpearson.com/excel/datedif.aspx
best wishes

 

[SeanC UK]

Hi Max,

Here is a ay to do it:

Public Function AgeIt(ByRef rngMyCell As Range) As Double
AgeIt = (Date - rngMyCell.Value) / 365
End Function

This way you must use the function as:

=AgiIt(B6)

where B6 is the cell with the date in.

Firstly, you need to return something to the cell calling the function,
which you do with your final line, although your previous lines should not
work (unless being called from within code that has not been initiated by a
worksheet function. The reason is that worksheet functions can only return
values to a cell, but the worksheet (including the contents of the cell - the
formula) cannot be changed by a worksheet function. Therefore, the formatting
of the cell and attempting to enter a formula in the cell (which is
attempting to replace your AgeIt formula with "=(TODAY()-RC[-1])/365") will
not work.

If the cells are already in General format then you should see the result as
"0.00" anyway.

I hope this helps,

Sean.