Geting around XIRR error

[Peter T]

Now that I've had my first glass of OJ today, I created the code below which
demonstrates the following:

1. The analysis toolpak is part of the addins collection, and installed,
when I create the NEW instance of Excel.

Results are conclusive. Coffee works better than OJ for sorting out addins
<g>

In addition to Rob's adjacent post see the top of my first post in this
thread, written when only second guessing what you were doing.

Regards,
Peter T

 

[Howard Kaikow]

Eureka!

I found out what was killing performance.

I am using PageSet and forgot to set Zoom = vbFalse.
By itself, this added about 19 seconds.

 

[Peter T]

What about the main issue, did you manage to load the ATP addin in an
automated instance; either by loading the xla & xll from file and
registering the xll, or un-installing and re-installing from the addins
collection.

Regards,
Peter T

 

[Howard Kaikow]

What about the main issue, did you manage to load the ATP addin in an
automated instance; either by loading the xla & xll from file and
registering the xll, or un-installing and re-installing from the addins
collection.

uinstalling/installing works.

I consider this to be a bug, as when creating a NEW instance of Excel, the
addins collection lists the critter as being
installed, tho it's not. Bad Excel "design"!

Remaing unresolved issues are in my topics:

1. "Are the Windows.Arrange ArrangeStyle:=xlHorizontal settings persistent?"
2. [GetMacroRegId]

 

[Peter T]

uinstalling/installing works.

I consider this to be a bug, as when creating a NEW instance of Excel, the
addins collection lists the critter as being
installed, tho it's not. Bad Excel "design"!

There is nothing inconsistent about an addin being in the collection and
installed yet not automatically loading in an automated instance.
Personal.xls does not load either. It's only opinion but I don't see it as a
bug. If running say a dictator app the last thing you'd want is user's
unknown addins auto loading. However the developper has the option to load
one/all installed addins if/as requied.

Remaing unresolved issues are in my topics:

1. "Are the Windows.Arrange ArrangeStyle:=xlHorizontal settings

persistent?"

Don't think so. Of all Excel objects I have looked at, its Windows object is
the one I least understand.

2. [GetMacroRegId]

I saw your post but didn't understand the question.

Regards,
Peter T

 

[Howard Kaikow]

There is nothing inconsistent about an addin being in the collection and
installed yet not automatically loading in an automated instance.
Personal.xls does not load either. It's only opinion but I don't see it as a
bug. If running say a dictator app the last thing you'd want is user's
unknown addins auto loading. However the developper has the option to load
one/all installed addins if/as requied.

Yes, but the "docimentation" states that the Installed property of Addin
returns "True if the add-in is installed".
That's the bug.

Don't think so. Of all Excel objects I have looked at, its Windows object is
the one I least understand.

I believe the setting is persistent if changed via the GUI.

I saw your post but didn't understand the question.

When using code such as that given below, the following is output in the
Immediate window as soon as .Formula is executed.

[GetMacroRegId] 'XIRR' <
[GetMacroRegId] 'XIRR' -> '875692121' >

With rngXIRR
.Formula = sXIRR ' SXIRR has a valid XIRR formula
.NumberFormat = "0.000%"
.Name = "'" & sThisSheet & "'!" & "WattEver"
End With

Using Google, I found postings indicating that such statements are produced
by Debug.Print in the Analysis ToolPak
If true, all I can say is @$@$%@!!@#.

The suggested solution was to crack the password for the .XLA and modify the
code.
If the password for an XLA is as easy to crack as that of an .XLS, this
would be doable.
MSFT should fix this themselves if the onlty problem is the stupidity of the
developers leaving in such code.
Guess they never heard of comments or conditional compile.

 

[Peter T]

as

Yes, but the "docimentation" states that the Installed property of Addin
returns "True if the add-in is installed".
That's the bug.

Sorry I don't follow, what is the bug. An addin's installed property reads
the same in a normally opened instance or in an automated instance. Only
difference is in the automated instance any installed addins do not
automatically load on startup.

When using code such as that given below, the following is output in the
Immediate window as soon as .Formula is executed.

[GetMacroRegId] 'XIRR' <
[GetMacroRegId] 'XIRR' -> '875692121' >

With rngXIRR
.Formula = sXIRR ' SXIRR has a valid XIRR formula
.NumberFormat = "0.000%"
.Name = "'" & sThisSheet & "'!" & "WattEver"
End With

I was unaware of this 'feature' that appears to have been introduced in
XL2002. Providing the IDE is not open I wonder what the negative impact is.

I agree it does seem odd a replacement was not made available and/or revised
for distribution with XL2003.

Regards,
Peter T

 

[Howard Kaikow]

I was unaware of this 'feature' that appears to have been introduced in
XL2002. Providing the IDE is not open I wonder what the negative impact

is.

None.

But if debuggin within the VBA IDE, it's a pain in the back rank, to use
polite chess terminolgy.

I agree it does seem odd a replacement was not made available and/or revised
for distribution with XL2003.

Too easy to do, n'est-ce pas?