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

Dean,
No, I see no Edit>Link 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" <(E-Mail Removed)> wrote in message
news:l-adnXPFRp9ffcHbnZ2dnUVZ_u-(E-Mail Removed)...
> 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
>
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Dean,
> No, I see no Edit>Link 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" <(E-Mail Removed)> wrote in message
> news:l-adnXPFRp9ffcHbnZ2dnUVZ_u-(E-Mail Removed)...
>> 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
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Dean,
> > No, I see no Edit>Link 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" <(E-Mail Removed)> wrote in message
> > news:l-adnXPFRp9ffcHbnZ2dnUVZ_u-(E-Mail Removed)...
> >> 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
> >>
> >>
> >
> >
>
>

Wow, my memory is really bad. I had this problem only about 6 months ago
and, for some reasons, posted on yet another forum, answered by Dave
Peterson:

http://www.officehelp.in/1263734/atpvbaen-link

In that post, I even recalled having the problem even before that!
Nevertheless, Dave's suggestions did not help the problem, although he did
suggest that unless you use these add-in functions in your VBA code, which I
don't plan to, you don't really need this atpvbaen at all. So, it seems to
me that Dave would advise me that the best thing for me to do is to simply
uncheck the analysis toolpack for VBA. I may have to redo hundreds of
eomonth functions but, once done, it probably won't get confused again by
eomonth. Then, when I send the file somewhere, they probably won't see the
link either, as long as they either don't have it checked or, even if they
do have it checked, as long as they don't add any NEW eomonth functions.

Anyone care to agree, or disagree?

What I'd really like to know if why atpvbaen insists on stealing away the
eomonth function that apparently, is already in funcres.xls. If it is
really just for VBA, how does it even find it?

Dean


"Dean" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Dean,
>> No, I see no Edit>Link 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" <(E-Mail Removed)> wrote in message
>> news:l-adnXPFRp9ffcHbnZ2dnUVZ_u-(E-Mail Removed)...
>>> 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
>>>
>>>
>>
>>
>
>

Thanks Nick. I use too many of these add-ins to be willing to do away with
them, though I think I will uncheck the second Analysis Toolpak option, the
one for VBA, as ATPVBAEN seems to be the source of the problem, at least
when using the eomonth function. I also have EXCEL 2002 and, after now
having unchecked, then rechecked the box a few times to make sure the cause
and effect were still there, I too can no longer reproduce my symptom
either, though it was definitely there earlier - it would add the link for
any new eomonth functions I typed in, with the check box checked!

I'd attribute it to a going south installation of EXCEL on my machine but
this also happened to a colleague to whom I sent a raw file that did not
have the edit link initially either (he said he did have EXCEL 2007, which
may deal with add-ins a little bit differently), so it isn't just my version
of EXCEL or my computer. Very strange. I hope that someone can explain all
this.

Thanks again, Nick.
Dean


"NickHK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > Dean,
>> > No, I see no Edit>Link 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" <(E-Mail Removed)> wrote in message
>> > news:l-adnXPFRp9ffcHbnZ2dnUVZ_u-(E-Mail Removed)...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > Dean,
>> > No, I see no Edit>Link 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" <(E-Mail Removed)> wrote in message
>> > news:l-adnXPFRp9ffcHbnZ2dnUVZ_u-(E-Mail Removed)...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:%(E-Mail Removed)...
> >> > Dean,
> >> > No, I see no Edit>Link 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" <(E-Mail Removed)> wrote in message
> >> > news:l-adnXPFRp9ffcHbnZ2dnUVZ_u-(E-Mail Removed)...
> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

The same person who was having problems with EXCEL's eomonth functions
disappearing from the file I sent him (not immediately, but eventually), is
having problems with your function too (returns #NAME?), when I send him a
simple file that works for me. He has EXCEL 2007, but should that matter?
Both of us do NOT have Analysis ToolPak - VBA installed now. Your function
works for me, but apparently not for him when he receives the file and opens
it on his computer.

Does this make any sense?

Dean


"NickHK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:%(E-Mail Removed)...
>> >> > Dean,
>> >> > No, I see no Edit>Link 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" <(E-Mail Removed)> wrote in message
>> >> > news:l-adnXPFRp9ffcHbnZ2dnUVZ_u-(E-Mail Removed)...
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Actually, he is now telling me he has his macro security setting at high.
This would explain the problem with the homegrown function, right? But not
the problem with EXCEL's function, I think. Do you agree?


"Dean" <(E-Mail Removed)> wrote in message
news:2KudnaL2cpeXdPnbnZ2dnUVZ_t-(E-Mail Removed)...
> The same person who was having problems with EXCEL's eomonth functions
> disappearing from the file I sent him (not immediately, but eventually),
> is having problems with your function too (returns #NAME?), when I send
> him a simple file that works for me. He has EXCEL 2007, but should that
> matter? Both of us do NOT have Analysis ToolPak - VBA installed now. Your
> function works for me, but apparently not for him when he receives the
> file and opens it on his computer.
>
> Does this make any sense?
>
> Dean
>
>
> "NickHK" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>> > 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" <(E-Mail Removed)> wrote in message
>>> > news:(E-Mail Removed)...
>>> >> 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" <(E-Mail Removed)> wrote in message
>>> >> news:%(E-Mail Removed)...
>>> >> > Dean,
>>> >> > No, I see no Edit>Link 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" <(E-Mail Removed)> wrote in message
>>> >> > news:l-adnXPFRp9ffcHbnZ2dnUVZ_u-(E-Mail Removed)...
>>> >> >> 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
>>> >> >>
>>> >> >>
>>> >> >
>>> >> >
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>