ATPVBAEN.XLA . . . One More Time

[Cyberdude]

Not only can the compiler not find function EOMONTH, it can't find functions DATE and LEFT. I get the error message "Can't find project or library".

When I look at the "References" list, I see this entry:
"MISSING: ATPVBAEN.XLA"
There IS an entry for atpvbaen.xls. So I put a check in its box. I run it again, and I get the same message AND my check mark has been removed from the box!

Regarding the References entry that says
"MISSING: ATPVBAEN.XLA",
what does "MISSING" mean? I'm guessing it means that the compiler looked for the "XLA" version and couldn't find it. I searched and I couldn't find it either. Does that mean that the XLA version doesn't exist in MY system?? If not, where can I get a copy of it?

I know I've been a pest about this, but I'm really hurting.
Sid

[Bob Phillips]

RE-install Excel and pick the Analysis Toolpak to be loaded in the custom install part.

[Cyberdude]

While I don't like this as a fix for the messages that ATPVBAEN.XLA sent me when executing Excel 2007, so far it seems to work. The fix consists of appending the following prefix to each item that the VBE compiler complains about:

WorksheetFunction.

Be careful to not omit the period (dot) at the end. For example, if the VBE compiler complains that it can't find the functions "DATE" or "TIME" currently in a statement like:

MyDate = DATE

then modify the statement to look likr this:

MyDate = WorksheetFunction.DATE

I don't know what all the rules are, but my compiler was also unhappy with some VBA functions that I had resident in a different workbook. So where I might have had

CALL MyMacro

I changed it to be:

CALL WorksheetFunction.MyMacro

and the compiler stopped complaining. It's possible that under some conditions you will have to write the revised statements with the word "Application." so that it will look like:

MyDate = Application.WorksheetFunction.DATE

If it works for you, then use it!
Sid

Merged from separate thread (geekgirlau)