Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a user-defined function
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a user-defined function
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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Max" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a user-defined function
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. -- (please remember to click yes if replies you receive are helpful to you) "Max" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I create a user defined function in excel? | Excel Discussion (Misc queries) | |||
Create User Defined Folder | Excel Programming | |||
How to create User Defined function in Excel | Excel Programming | |||
How to create User Defined Function | Excel Programming | |||
Create help for user-defined function | Excel Programming |