T
Thinqer
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.
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.