atpvbaen link

[Dean]

I feel like I figured this out when I last saw it about 2 years ago, but
can't recall the answer:

When I open my workbook today, I get an update links query. When I go to
edit links it says the problem is atpvbaen.xla. I seem to recall this is
some sort of add in, but it shouldn't appear to be a link, I don't think.

What is causing this and how do I remove it?

It seems like it's caused by a new worksheet that has eomonth functions
which are an add-in. However, I already used this same function in other
old sheets and didn't have the problem (at least not in a couple of years of
my memory)!!

Thanks!
Dean

 

[Dave Peterson]

atpvbaen.xla is the VBA version of the analysis toolpak.

There's another addin (Funcres.xla) that will appear when you check the analysis
toolpak (not the VBA item) in Tools|Addins.

These are guesses, so they might not work.

First, open your workbook with the links to the atpvbaen.xla file. Then go into
the VBE.

Hit F4 to see the project explorer
select your project
Then click Tools|References
and look for a check mark in front of atpvbaen.xla
and uncheck it.

Then back to excel and save this workbook
and close it and reopen to see if that fixed it.

Creating a reference (in the VBE) to atpvbaen.xla means that your code can call
all the functions that are in that addin--just like they were built into VBA.

But if your code doesn't use these, you don't need the reference.

But if your code does use any of these,
double check that tools|addins Analysis Pak - VBA
is checked.

 

[Dean]

atpvbaen.xla is the VBA version of the analysis toolpak.

There's another addin (Funcres.xla) that will appear when you check the
analysis
toolpak (not the VBA item) in Tools|Addins.

These are guesses, so they might not work.

First, open your workbook with the links to the atpvbaen.xla file. Then
go into
the VBE.

Hit F4 to see the project explorer
select your project
Then click Tools|References
and look for a check mark in front of atpvbaen.xla
and uncheck it.

Then back to excel and save this workbook
and close it and reopen to see if that fixed it.

Creating a reference (in the VBE) to atpvbaen.xla means that your code can
call
all the functions that are in that addin--just like they were built into
VBA.

But if your code doesn't use these, you don't need the reference.

But if your code does use any of these,
double check that tools|addins Analysis Pak - VBA
is checked.

 

[Dean]

The box in VBE was unchecked and I do not use it in my one trivial macro, so
I did not bother to check the box. I notice that, after I choose "don't
update links", if I go to Tools Add-Ins, it immediately responds that I
can't run add-ins, that I need to install it. When I then answer "don't
install", then the add ins box pops up anyway, and the ATP box is checked,
consistent with the fact that my eomonth functions do work everywhere
(unless I update links, in which case the few new ones turn to nonsense).

What I did try is to change the source (under edit links) to a garbage file,
so I could see what cells got messed up. And, just as I suspected, it was a
few eomonth functions that I had added to a new worksheet that someone else
stuck in my file and sent back to me. When I first opened their sent-back
file, there was no such problem, adding a few new eomonth functions on their
new sheet, resaving, and re-opening caused the problems.

I think I read something about switching versions of EXCEL can cause this
kind of thing? Strangely, though the equations in these cells showed a link
to the new garbage file (after I changed the source to a garbage file), when
I hit edit links, it was hushed! Nevertheless, I simply retyped the eomonth
equations into the few affected cells and all is fine now. Also, I already
had tons of these eomonth functions already working in the workbook - it
seems that it was confused only by the new references on a new worksheet
that was later added in.

Though I am fine now, as an intellectual curiosity, and for possible future
reference, does any of this make sense?

Thanks!
Dean

 

[Dave Peterson]

I've never had any trouble upgrading different versions of excel (97 to 2002 to
2003).

But if I recall correctly, I've always loaded those addins before starting excel
(and checking that tools|addins option).

I don't understand why atpvbaen.xla would be sticking its head in any of this,
though.

 

[Dean]

Oh well, just one of those mysteries of life.

I've never had any trouble upgrading different versions of excel (97 to
2002 to
2003).

But if I recall correctly, I've always loaded those addins before starting
excel
(and checking that tools|addins option).

I don't understand why atpvbaen.xla would be sticking its head in any of
this,
though.