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:
When the calced portion (or if the whole formula is calced) contains two ATP functions, you get this:
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.
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.
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?
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
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.
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
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.)
In case you’re curious, it’s been known that you can get around some thing by typing Wildebeest!!