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:

    =MCONCAT(A1:A3)&MCONCAT(B1:B3)

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

    =MyMCONCAT(A1:A3)&MyMCONCAT(B1:B3)

    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:

    http://tinyurl.com/4mr5p

    HTH
    Jason
    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]

    Regards,
    Jason

  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.)


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.