Add-ins

[Brian]

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?

[Frank Kabel]

Hi
no chance. They need the add-in installed

--
Regards
Frank Kabel
Frankfurt, Germany

[Norman Harker]

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
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Brian" <bmarte...@yahoo.com> wrote in message
news:19ab501c41ca2$b88685c0$a101...@phx.gbl...

[anon...@discussions.microsoft.com]

Thanks. That is what I was afraid of.

>.
>

[Bob Phillips]

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" <bmarte...@yahoo.com> wrote in message
news:19ab501c41ca2$b88685c0$a101...@phx.gbl...