Very nice to know that someone else has seen this, too. My files with
problems do not use any third party add ins so you can probably eliminate
that. Nick may have home grown functions for you - he had one for me for
eomonth. It worked nicely.


"rockhammer" wrote in message
...
Hi Nick & Dean,

I don't have a real solution to offer but just want to note that I've been
getting the same problem recently.

I've been using =eomonth(), =edate(), =yearfrac()= for years and never had
a
problem until recently starting may be two months or so ago. For
completely
unknown reasons, in select files where I use those date functions, excel
all
by itself started looking for them in atpvbaen.xla. But since those date
functions are not there, I get "c:\Program Files\MS
Office\Excel\atpvbaen.xla!eomonth" or something inserted in place of all
those date functions which shows #NAME as value.

To resolve it, I just did a global search & replace to take out the
"c:\Program Files\...
" bit and it worked. However, since doing that, one of these files I did
the
search/replace started showing the same problem again. I then did another
round of search/replace. For the last couple of weeks I have not seen this
recur.

I'd like to find out if anyone has a definitive way of reproducing this
problem. So far I've not the time to figure it out.

The only other observation I can make is that those files that showed this
problem uses 3rd party add-ins to grab data into excel over the web.


"Dean" wrote:

I am having some other flaky problems with analysis toolpak (aka
atpvbaen.xla). When I use some of its advanced functions, atpvbaen.xla
shows up under edit links, though it doesn't query me about updating that
link when I open the file. I assume it should not even show up under
edit
links, right?

Thanks!
Dean


"NickHK" wrote in message
...
Dean,
Not sure if the saving in 2007 version is an issue.
Possibly just general corruption of the WB. Maybe one of the Code
Cleaners
would help.

Have you checked for missing references under the VBE, ToolsReferences
?
Any marlked as MISSING ?

NickHK

"Dean" wrote in message
...
Nick,

The guy has sent me back the file (the one that, eventually,
mysteriously,
wipes out all eomonth functions, leaving only N/A in their wakes) and
I
have
attempted to use your myeomonth function in it, but all it returns is
#NAME.
I also have a very simple new file open that I created with your
function
in
it and it works fine there.

A clue may be that, in the file where it doesn't work, no matter how I
type
it in (lower case, upper case, etc), it changes it to:

=myEOMONTH(O3,P3)

that is, the first 2 letters are lower case and the next 7 letters are
upper
case. Seemingly, this is tacit recognition that it (the first file)
does
not comprehend the function.

Yes, the function is in a macro sheet within both files; also macro
security
is set to low.

What could explain this?

Dean


"NickHK" wrote in message
...
Dean,
Make it a Public function in a standard module, then you can call it
as
a
worksheet function.

As for the Lotus aspect, MS continued a bug that Lotus introduced in
calculating one Leap year (1900 ?). Whilst this is probably not a
problem
for you, you did ask <g.

NickHK

"Dean" wrote in message
...
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