If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
XIRR #NAME? error - Toolpak IS installed
Hi there,
I've discovered a worrying problem in a large model I'm building at the moment. I've used the XIRR function in the model without issue up until yesterday. Whilst trying to think of ways to speed up some iterative calculations by using macros, I added references (in the VBE) to funcres and atpvbaen.xls so that I would have references to the XIRR function. I then didn't do any further macro development as I had other work to do. Back in the model, I added some new formulae that used the XIRR function and noticed that when I entered them, the XIRR in the formula was converted to lower case. Normally functions are shown in upper case. I also noticed that when I recalculated the model, data was output to the Immediate window in the VBE, seemingly for each instance of the xirr formula. The output was as follows: [GetMacroRegId] 'XIRR' [GetMacroRegId] 'XIRR' - '-1540226983' Additionally, the recalculation was very slow. The next day I was thinking about this and realised that this might be due to the reference I had added in the VBE so I went and removed it. When I then went to recalculate the model (F9), all of the 'new' xirr formulae I had added came back with a #NAME? error. All the 'old' XIRR formulae (input before I added the reference in the VBE) were fine. I tried deleting the 'new' formulae and copying some of the 'old' formulae but that didn't work: the xirr was still shown in lower case and the formulae returned #NAME? errors. I then re-entered one of the old formulae (F2 then Return) and it no longer worked (lower case xirr and #NAME? error), although all of the remaining formulae along the same row were fine. I then tried Ctrl+Shift+Alt+F9 and nothing changed: new formulae broken, old formulae fine. I then closed the model, removed the analysis toolpak add-in and the analysis toolpak for VBA add-in and closed Excel. Then re-opened Excel, added the add-ins (both of them) and re-loaded the model. No dice: same problem. Now, if I add a reference in the VBE to atpvbaen.xls the xirr formulae work but very slowly (as described before). If I remove the reference, they don't work. As a summary the model is about 20MB in size, all the XIRR calcs are on the same sheet and in total (old and new) there are about 14,000 cells with XIRR formulae in them. If I do a search for XIRR I get a nice list of them, all the old ones shown with XIRR and the new ones with xirr. I know this is a slightly peculiar issue but I was wondering if anyone might be able to shed any light on this problem and more importantly how I might be able to solve it. I'm happy to try any solutions you might have, or do any testing you might want, or provide more information if needed. Many thanks in advance for you advice. Ben. |
#2
|
|||
|
|||
XIRR #NAME? error - Toolpak IS installed
One more bit of info I forgot to add:
If I open a new workbook and add a formula with XIRR it works just fine, even when open along side the model with the non-working XIRR formulae. HTH, Ben. |
Thread Tools | |
Display Modes | |
|
|