ATPVBAEN Problems

[Dean]

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

 

[NickHK]

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]

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]

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]

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]

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

 

[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]

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]

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

 

[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?

 

[NickHK]

Dean,
With Security set to high (and unsigned/not trusted code), the code will not
run.
Reduced security to medium and click allow macros.

NickHK

 

[Dean]

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

 

[Dean]

Oh, I forgot to answer this. That fixed it for him. Thanks, but see my
other post today, please, Nick.

 

[Dean]

One other thing. Though I don't think anyone thinks it would matter, his
file was one I created in EXCEL 2002 but which he altered somewhat and
re-saved in EXCEL 2007.

 

[NickHK]

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, Tools>References ?
Any marlked as MISSING ?

NickHK

 

[Dean]

Actually, if I try it in a brand new file, it doesn't work there either
(though I no longer have that case sensitivity issue). It only works in the
one file I created last week. So, it seems to be a problem in my system too
(in fact, it may well work in this guy's system, I'll find out tomorrow),
but only in some EXCEL files! There files are open all at the same time,
all have the function copied into VBE, yet it only works in one.

Just to be clear, do I need to dimension anything in your function, or is it
self dimensioning, assuming that means anything? I do NOT have the analysis
toolpak add-in VBA installed, only the analysis toolpak without VBA. Is
that fine? And the public function can be inserted in any VBE sheet, right,
if under option explicit (or does it need to be in the "This Workbook"
sheet)?

Also, I'm not sure what a missing reference looks like. I see hundreds of
choices but only the first four are checked, but nothing says missing.
Kindly clarify.

Thanks, Nick!
Dean

 

[NickHK]

Dean,
To be visible as a worksheet function, it needs to be in a standard module,
NOT a worksheet, NOR ThisWorkbook.

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

it works as is. Only VBA so does not depend on other components.

If the module containing this function is called 'Module1" and in the
Immediate, if you type:
?Module1.
do you get Intellsense with the function nema then its signature ?

And references: You would one/some marked as "MISSING" if there was a
problem.

NickHK

Actually, if I try it in a brand new file, it doesn't work there either
(though I no longer have that case sensitivity issue). It only works in the
one file I created last week. So, it seems to be a problem in my system too
(in fact, it may well work in this guy's system, I'll find out tomorrow),
but only in some EXCEL files! There files are open all at the same time,
all have the function copied into VBE, yet it only works in one.

Just to be clear, do I need to dimension anything in your function, or is it
self dimensioning, assuming that means anything? I do NOT have the analysis
toolpak add-in VBA installed, only the analysis toolpak without VBA. Is
that fine? And the public function can be inserted in any VBE sheet, right,
if under option explicit (or does it need to be in the "This Workbook"
sheet)?

Also, I'm not sure what a missing reference looks like. I see hundreds of
choices but only the first four are checked, but nothing says missing.
Kindly clarify.

Thanks, Nick!
Dean

------------------ CUT ------------------------

 

[Dean]

Ok, I had it in ThisWorkbook, that was the problem. Thanks!

Dean

 

[Dean]

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

 

[rockhammer]

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.