Missing:ATPVBAEN.XLA

[John P]

Hi. I have been trying to use the Dateadd function but a dialog box pops up
saying can't find project or library and missing ATPVBAEN.XLA

Following instructions from some of the forums, I clicked the Microsoft
Office button and went to the add-ins and loaded in the Analysis Toolpak and
Analysis Toolpak-VBA (atpvbaen.xlam). But the problem still persists.

In my VBE, the Tools/References dialog still shows missing ATPVBAEN.XLA. In
searching for this file, I found these 4 files in Office12/library/analysis:
Analy32 (blue square), atpvbaen (red square), funcres (red square) and procdb
(red square). Can anyone advise me what's wrong? Thank you for your advice.

 

[Joel]

in Reference select the library AND THEN USE BROWSE TO SELECT atpvbaen.XLA.
The reference is not selecting the correct folder where the file is located.
Make sure you press OK.

 

[Jacob Skaria]

DateAdd is a VBA function and not a function within ATP. Check out help on
DATEADD()

DateAdd(interval, number, date)

If this post helps click Yes

 

[Joel]

Jaob is right

It has the following format

DateAdd("m", 1, "31-Jan-95")

YOu may have a conflit if the Reference is added. Try unchecking the
reference, press OK and try again. You may have to exit excel and re-enter
to remove the reference.

 

[John P]

Thanks Jacob. I have been testing this DateAdd function in the immediate
window using various date formats with inverted commas, but each time a
dialog box will inform me of missing atpvbaen.xla

 

[Jacob Skaria]

--ATP functions are integrated in Excel 2007, and not an addin any longer.
Have you opened a workbook which was saved in 2003..

--If you open excel application with the default workbook and try the same
in VBE>Immediate window (i mean without opening any workbooks)...are you
getting the same message...

If this post helps click Yes

 

[John P]

Thanks Joel. The Reference look into the correct folder i.e.
Office12/Library/Analysis but it is searching for a type libraries file. The
atpvbaen is listed as Microsoft Office Excel Files.

When I opened the atpvbaen file, it appeared in the Reference window as
atpvbaen.xls with atpvbaen.xla still listed as missing. When I activated the
DateAdd function un the immediate window, a message says name conflict. When
I checked the Reference, atpvbaen.xls becomes unchecked and atpvbaen.xla is
still listed as missing. How does this tie up with atpvbaen.xlam listed as an
add-in in the Microsoft button/add-in window?

 

[John P]

Jacob you are right. When I open a new workbook, the DateAdd function could
work. Yes, mine was an 2003 workbook resaved as 2007 macro-enabled file. Does
it mean, there is no longer any need to load the add ins as they have been
integrated into Excel 2007? Thanks

 

[Rick Rothstein]

I don't know what is causing your problem (and, of course, you should
continue to look to solve it), but I *think* you might be able to bypass the
immediate problem by specifying the library to have the DateAdd function
reference, namely, VBA. See if this executes (in the Immediate window)
without error on your system...

? VBA.DateAdd("m", 24, Date)

If it works, remember, this doesn't solve whatever the underlying problem
is... it just sidesteps it.

 

[Rick Rothstein]

Most of the Analysis ToolPak functions were incorporated into XL2007, but
not all. See Step 5 in this link...

http://office.microsoft.com/en-us/excel/HP100215691033.aspx

 

[John P]

It is working after I uncheck the missing reference. Thanks for your help.