Calculating Two ATP Functions in the Formula Bar

Dean found this bug and I found Dean in the .programming newsgroup. From what I can tell, if you have two or more functions from the Analysis Toolpak, you get a #NAME? error when you try to calculate in the formula bar.

To calculate in the formula bar, you can use F9 or Cntl+= while a cell’s in edit mode. Here’s how it works, when it works right:

formula in formula bar
select part of a formula
F9 part of a formula

When the calced portion (or if the whole formula is calced) contains two ATP functions, you get this:

select part of a formula with two ATP functions
f9 two atp function give name error

I’ve tested this only in Excel 2000. Maybe someone can test it in some other versions and post a comment. Also, I have a hard time believing this is only in the ATP. I tried it on a UDF that’s in a regular old xla file and did not have a problem. Honestly, I don’t know what kind of add-in the ATP is, but I’ll bet dollars to donuts it’s not written in VBA. Perhaps this is true of all COM add-ins or all xlls. If someone has those handy to test, let me know what you find.

Posted in Uncategorized

7 thoughts on “Calculating Two ATP Functions in the Formula Bar

  1. I tried using MoreFunc, which is another XLL. Just using:


    produces the same thing, but a UDF that does the same thing:


    works fine. So it may be XLLs.

  2. One thing I have noticed with the atp is that the listed project in the VBE (atpvbaen) prompts for a password. This, along with the fact that it’s called atpVBAen, suggests that it’s partially VBA.

    Question is: what’s the password for atpvbaen? Surely someone must know?

  3. Same error for XL2003 – I tested it using RANDBETWEEN. I did some digging in the google archives and found an old posting by Chip Pearson discussing the ATP add-in:

    Atlanta, GA

  4. Charlie: atpvbaen is a different add-in than atp. Namely, it’s the add-in that allows you to use the atp in vba. So I guess you’re right that at least those pass through functions are vba.

    As for the password, the copyright owners passworded it so you couldn’t see it.:) If you know how to circumvent Excel security you could certainly have a look. I, of course, don’t know how to do that because it’s never come up in my virtuous life.

  5. When I really want to see VBA code w/out paying for commercial password-breaking software, I refer to the instructions here:

    [censored until further notice]


  6. Note that you can get around this by first doing F9 on the first ATP function, then selecting the other and doing F9 again.

    (Virtuous life? Yeah, right.)

Leave a Reply

Your email address will not be published. Required fields are marked *