I'm not sure what you are saying about Lotus. I'm using EXCEL - does is not
work for leap years in EXCEL?

I forget where to put such a function - is it on the "This Workbook" page?


"NickHK" wrote in message
...
Dean,
As it is using VBA's date calculation engine, rather than my own, it will
return the correct date (apart from the built-in Lotus compatibility leap
year/non leap year).
Basically, all it is goes to the first of the month after the one we want,
then comes back 1 day. Hence the last day of the month we want.

As for all the add-in stuff, I tend to avoid them, unless they provide
something that is absolutely essential.
From your description, I cannot reproduce these problem by
installing/uninstalling either addin, in XL2002.

NickHK

"Dean" wrote in message
...
Does your home grown function work for every date, leap year or not?

Just to be sure, are you saying that, if you go to a blank spreadsheet
and
type in an eomonth function into some cell, then go to edit links, there
is
no link? Do you have the analysis toolpak FOR VBA installed also, or
just
the regular analysis toolpak?

If just the regular one, then I might have some clues. I did some
googling
and found a post by our own Tom Ogilvy on some other forum which told me
something I didn't know, that atpvbaen is really the add in for the VBA
analysis toolpak, not the regular toolpack. So. I unchecked the VBA
version
in my EXCEL and then noticed that the eomonth function died, however if
I
retyped it in, it worked, and there was no edit link. However, as soon
as
I
added the VBA add-in back in and typed a new eomomth function in another
cell, I got the edit link back again - just for that one new eomonth
function, not the others. So it seems that, at least on my computer, if
I
use the eomonth function, it looks to find it first in the analysis
toolpak
VBA, not just the regular analysis toolpak.

I'm not sure I really need the analysis toolpak VBA, but it seems like it
might be of some use! Does anyone have any idea how to tell EXCEL to
look
at the regular analysis toolpak first, or does the VBA one supersede the
regular one, making this edit link unavoidable? The link seems to be
problematic in that, if you send a file to someone else, it wants to
update
that atpvbaen link, which they may not even have.

Thanks much, Nick!
Dean

"NickHK" wrote in message
...
Dean,
No, I see no EditLink using this function.
By the way, if this is the only function you use, you can roll your own
quite easily, then drop the dependence on this add-in:

Public Function MyEOMonth(FromDate As Date, MonthsToAdd As Long) As
Date
MyEOMonth = DateSerial(Year(FromDate), Month(FromDate) + MonthsToAdd +
1,
0)
End Function

NickHK

"Dean" wrote in message
...
I sometimes use some functions that require the analysis toolpack, the
basic
EXCEL one, not the VBA one. A typical function is eomonth(date, # of
months), which finds the ending date of the month that is so maybe
months
later.

I notice that, when I save a file with this function, though there is
no
message asking me to update links, when I re-open this file, there is
a
link
when I go to edit links, showing ATPVBAEN.xla. I'm not sure it was
this
way
before. I occasionally get messages suggesting the add-ins are not
installed even though clearly they are, since the equation is still
there
and changes when I change the date it is computing from. In any
event,
I
also sent such a file to someone else to use and he ended up losing
all
cells that had this eomonth function in them - they all show up as
=#N/A,
though the right answer is still there, probably because he did not
update
links, or maybe because he did. In any event, he loses the
equations.
I
even notice that when I open this file, sometimes, I see the formula
change
so that, in the formula bar, it shows something like
atpvbaen.xls//eomonth(x,y), which seems the cause of the problem.

My first question is whether this link to ATPVBAEN.xla should show up
as
a
link, under edit links - can someone else try it on their computer -
just
type =eomonth (a1,1) into cell a2 with any date in cell a1 - and tell
me
if
a link to atpvbaen.xla shows up for them (assuming you have the
analysis
toolpak installed)? Also, how can I fix things so that, when I send
the
file to someone else, he won't have the problem with his computer not
being
able to find this link? Can he just change the source, assuming he
can
find
his own ATPVBAEN.xla file? Also, I think he has upgraded to EXCEL
2007,
which may not deal with add-ins in the same way.

Help, please!

Thanks
Dean