If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add-ins
I am have created a workbook that uses an add-in function
(the YEARFRAC function). This workbook will be distributed to other users that may not have the add-in loaded into their Excel program. Is there any way to make the function work within the workbook without the user needing to load the add-in? |
#2
|
|||
|
|||
Add-ins
Hi
no chance. They need the add-in installed -- Regards Frank Kabel Frankfurt, Germany Brian wrote: I am have created a workbook that uses an add-in function (the YEARFRAC function). This workbook will be distributed to other users that may not have the add-in loaded into their Excel program. Is there any way to make the function work within the workbook without the user needing to load the add-in? |
#3
|
|||
|
|||
Add-ins
Hi Brian!
I don't think so. But you can use formula to replace YEARFRAC and indeed you might not be aware that YEARFRAC produces some rather weird results for periods of more than a year. The formula to use is: =DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1) ))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY( A1))-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1) )) This assumes use of YEARFRACs third argument of 3 (Actual / Actual) Myrna Larsen has implemented the same formula in a VBA function and you can use that: Function YearDiff(ByVal StartDate As Date, _ Optional ByVal EndDate As Date = #1/1/100#) As Double 'modified 02/01/2003 Dim AnnDay As Long Dim AnnMonth As Long Dim AnnYear As Long Dim ltemp As Date Dim NextAnn As Date Dim PrevAnn As Date If EndDate = #1/1/100# Then EndDate = Date 'put in right order if necessary If StartDate EndDate Then ltemp = StartDate StartDate = EndDate EndDate = ltemp End If 'get anniversary date in ending year AnnYear = Year(EndDate) AnnMonth = Month(StartDate) AnnDay = Day(StartDate) 'assume it's already occurred PrevAnn = DateSerial(AnnYear, AnnMonth, AnnDay) If PrevAnn = EndDate Then 'assumption that it's past was correct 'next anniversary is 1 year in the future NextAnn = DateSerial(AnnYear + 1, AnnMonth, AnnDay) Else 'wrong -- we calculated the *next* anniversary NextAnn = PrevAnn AnnYear = AnnYear - 1 PrevAnn = DateSerial(AnnYear, AnnMonth, AnnDay) End If YearDiff = AnnYear - Year(StartDate) + _ (EndDate - PrevAnn) / (NextAnn - PrevAnn) End Function 'YearDiff But whatever you do, I'd make sure that you thoroughly test or exclude inputs where there is more than a year between dates. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Brian" wrote in message ... I am have created a workbook that uses an add-in function (the YEARFRAC function). This workbook will be distributed to other users that may not have the add-in loaded into their Excel program. Is there any way to make the function work within the workbook without the user needing to load the add-in? |
#4
|
|||
|
|||
Add-ins
Thanks. That is what I was afraid of.
-----Original Message----- Hi no chance. They need the add-in installed -- Regards Frank Kabel Frankfurt, Germany Brian wrote: I am have created a workbook that uses an add-in function (the YEARFRAC function). This workbook will be distributed to other users that may not have the add-in loaded into their Excel program. Is there any way to make the function work within the workbook without the user needing to load the add-in? . |
#5
|
|||
|
|||
Add-ins
Brian,
You could put it in the XLStart directory under the Microsoft Office/Office and then when anybody start Excel it will automatically load.. It won't be an installed add-in, but it will still function correctly. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Brian" wrote in message ... I am have created a workbook that uses an add-in function (the YEARFRAC function). This workbook will be distributed to other users that may not have the add-in loaded into their Excel program. Is there any way to make the function work within the workbook without the user needing to load the add-in? |
Thread Tools | |
Display Modes | |
|
|