Geting around XIRR error

H

Howard Kaikow

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.
2. The Tools |Addins menu is not available, in the GUI, until a WBK is
added, and then the addins are indicated as installed.
3. I included the following

'UNCOMMENT the following line to force a re-install
' bInstalled = vbFalse

Run the code with and without the above assignment line uncommented.
'-------------------------------------------------------------
Option Explicit
Private appExcel As Excel.Application
Private ExcelWbk As Excel.Workbook
Private sWorkbookfile As String

Private Declare Function PathFileExistsW Lib "shlwapi.dll" _
(ByVal pszPath As Long) As Long

Private Sub Form_Load()
Const sAnalysisName As String = "analys32.xll"

Dim bInstalled As Boolean
Dim i As Long
Dim sAppPath As String
Dim sPathATP As String
Dim sPathXLA As String
Dim sPathXLL As String
Dim xlAddin As Excel.AddIn

Set appExcel = New Excel.Application
On Error Resume Next
With appExcel
' Note that the Analysis ToolPak is installed in Excel BEFORE this
program runs,
For Each xlAddin In .AddIns
With xlAddin
Debug.Print .Installed, .Name, .Title
End With
Next xlAddin

bInstalled = .AddIns("Analysis ToolPak").Installed
With Err
If .Number = 0 Then
Debug.Print "(Invisible NO WBK)Installed", bInstalled
Else
Debug.Print "(Invisible NO WBK)", .Number, .Description
.Clear
End If
End With

.Visible = True

bInstalled = .AddIns("Analysis ToolPak").Installed
With Err
If .Number = 0 Then
Debug.Print "(Visible NO WBK)Installed", bInstalled
Else
Debug.Print "(Visible NO WBK)", .Number, .Description
.Clear
End If
End With

Set ExcelWbk = .Workbooks.Add()
' At this point, using the GUI, Tools | Addins shows the Analysis
Toolbox as installed.
bInstalled = .AddIns("Analysis ToolPak").Installed
With Err
If .Number = 0 Then
Debug.Print "(Visible WBK)Installed", bInstalled
Else
Debug.Print "(Visible WBK)", .Number, .Description
.Clear
End If
End With
' Even tho bInstalled is TRUE, i.e., XIRR is installed,
' the bug in http://support.microsoft.com/kb/291058
' occurs when running the program

'UNCOMMENT the following line to force a re-install
' bInstalled = vbFalse
If Not bInstalled Then
' Note: The following "fixes" things and the XIRR bug does not
occur.
.AddIns("Analysis ToolPak").Installed = vbFalse
' With Err
' If .Number = 0 Then
' Debug.Print "(9)Installed", bInstalled
' Else
' Debug.Print "(10)", .Number, .Description
' .Clear
' End If
' End With
.AddIns("Analysis ToolPak").Installed = vbTrue
' With Err
' If .Number = 0 Then
' Debug.Print "(11)Installed", bInstalled
' Else
' Debug.Print "(12)", .Number, .Description
' .Clear
' End If
' End With
bInstalled = .AddIns("Analysis ToolPak").Installed
With Err
If .Number = 0 Then
Debug.Print "(13)Installed", bInstalled
Else
Debug.Print "(14)", .Number, .Description
.Clear
End If
End With

' sPathXLA = .LibraryPath & "\analysis\FUNCRES.XLA"
' i = PathFileExistsW(StrPtr(.LibraryPath &
"\analysis\FUNCRES.XLA"))
' If i = 0 Then
' Debug.Print "Not Found: ";
' Else
' Debug.Print "Exists: ";
' End If
' Debug.Print sPathXLA
' If i <> 0 Then
' sPathXLL = .LibraryPath & "\analysis\analys32.xll"
' bInstalled = .RegisterXLL(sPathXLL)
' If bInstalled Then
' Debug.Print "Installed: ";
' Else
' Debug.Print "Not Installed: ";
' End If
' Debug.Print sPathXLL
'
' If bInstalled Then
' sPathATP = .AddIns("Analysis ToolPak").Path
' Debug.Print "ATP: "; sPathATP
' Else
' bInstalled = .RegisterXLL(sAnalysisName)
' If bInstalled Then
' Debug.Print "Installed(Yippee!): ";
' sPathATP = .AddIns("Analysis ToolPak").Path
' Debug.Print "ATP: "; sPathATP
' Else
' Debug.Print "Not Installed(Boo Hoo!): ";
' End If
' Debug.Print sPathXLL
' End If
' End If
Debug.Print "(AFTER STUFF!)Installed", bInstalled
'Note, the Analysis ToolPak is now installed and the XIRR bug
does not occur,
bInstalled = .AddIns("Analysis ToolPak").Installed
With Err
If .Number = 0 Then
Debug.Print "Installed", bInstalled
Else
Debug.Print .Number, .Description
.Clear
End If
End With
End If
End With
On Error GoTo 0

sAppPath = App.Path
sWorkbookfile = sAppPath & "\" & "Howard.xls"
i = PathFileExistsW(StrPtr(sWorkbookfile))
If i <> 0 Then
sWorkbookfile = sAppPath & "\" & CStr(CDbl(Now)) & "Howard.xls"
End If
End Sub

Private Sub btnByeBye_Click()
Unload Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
If Not ExcelWbk Is Nothing Then
With ExcelWbk
.SaveAs FileName:=sWorkbookfile
.Close
End With
Set ExcelWbk = Nothing
End If
If Not appExcel Is Nothing Then
appExcel.Quit
Set appExcel = Nothing
End If
End Sub

Private Sub btnRunMe_Click()
Dim rngXIRR As Excel.Range
Dim sCaption As String
Dim sColDate As String
Dim sColValue As String
Dim sXIRR As String

sColDate = "A"
sColValue = "B"
sXIRR = "=XIRR(B2:B4,A2:A4)"
With ExcelWbk
With .Worksheets(1)
Set rngXIRR = .Range("B1")

With rngXIRR
.Formula = sXIRR
.NumberFormat = "0.00000%"
End With

With .Range("A2")
.Value = DateValue("8 March 2008")
.NumberFormat = "d mmm yyyy"
End With
With .Range("B2")
.Value = -10000
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With

With .Range("A3")
.Value = DateValue("8 april 2008")
.NumberFormat = "d mmm yyyy"
End With
With .Range("B3")
.Value = -5000
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With

With .Range("A4")
.Value = DateValue("31 Dec 2008")
.NumberFormat = "d mmm yyyy"
End With
With .Range("B4")
.Value = 18000
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With
End With
With rngXIRR
' .Select
' .Application.SendKeys "{F2}"
' .Application.SendKeys "~"

' .Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, ReplaceFormat:=False
End With
.SaveAs FileName:=sWorkbookfile
.Close
End With
Set rngXIRR = Nothing
Set ExcelWbk = Nothing
appExcel.Quit
Set appExcel = Nothing
End Sub
 
R

Rob Bovey

Hi Howard,

The ATP appears to be installed because Excel is reading it's name from
the registry as an installed add-in. However, if you start Excel through
automation the ATP workbook will not actually be opened even if it looks
like the add-in is installed (this is the same for any add-ins in an
automated instance of the Excel application).

As Peter and me worked out, the following three lines of code should
create an instance of Excel and force the ATP to open and initialize itself
regardless of it's installed state prior to automating Excel:

Set appExcel = New Excel.Application
appExcel.AddIns("Analysis ToolPak").Installed = False
appExcel.AddIns("Analysis ToolPak").Installed = True

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Howard Kaikow said:
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.
2. The Tools |Addins menu is not available, in the GUI, until a WBK is
added, and then the addins are indicated as installed.
3. I included the following

'UNCOMMENT the following line to force a re-install
' bInstalled = vbFalse

Run the code with and without the above assignment line uncommented.
'-------------------------------------------------------------
Option Explicit
Private appExcel As Excel.Application
Private ExcelWbk As Excel.Workbook
Private sWorkbookfile As String

Private Declare Function PathFileExistsW Lib "shlwapi.dll" _
(ByVal pszPath As Long) As Long

Private Sub Form_Load()
Const sAnalysisName As String = "analys32.xll"

Dim bInstalled As Boolean
Dim i As Long
Dim sAppPath As String
Dim sPathATP As String
Dim sPathXLA As String
Dim sPathXLL As String
Dim xlAddin As Excel.AddIn

Set appExcel = New Excel.Application
On Error Resume Next
With appExcel
' Note that the Analysis ToolPak is installed in Excel BEFORE this
program runs,
For Each xlAddin In .AddIns
With xlAddin
Debug.Print .Installed, .Name, .Title
End With
Next xlAddin

bInstalled = .AddIns("Analysis ToolPak").Installed
With Err
If .Number = 0 Then
Debug.Print "(Invisible NO WBK)Installed", bInstalled
Else
Debug.Print "(Invisible NO WBK)", .Number, .Description
.Clear
End If
End With

.Visible = True

bInstalled = .AddIns("Analysis ToolPak").Installed
With Err
If .Number = 0 Then
Debug.Print "(Visible NO WBK)Installed", bInstalled
Else
Debug.Print "(Visible NO WBK)", .Number, .Description
.Clear
End If
End With

Set ExcelWbk = .Workbooks.Add()
' At this point, using the GUI, Tools | Addins shows the Analysis
Toolbox as installed.
bInstalled = .AddIns("Analysis ToolPak").Installed
With Err
If .Number = 0 Then
Debug.Print "(Visible WBK)Installed", bInstalled
Else
Debug.Print "(Visible WBK)", .Number, .Description
.Clear
End If
End With
' Even tho bInstalled is TRUE, i.e., XIRR is installed,
' the bug in http://support.microsoft.com/kb/291058
' occurs when running the program

'UNCOMMENT the following line to force a re-install
' bInstalled = vbFalse
If Not bInstalled Then
' Note: The following "fixes" things and the XIRR bug does
not
occur.
.AddIns("Analysis ToolPak").Installed = vbFalse
' With Err
' If .Number = 0 Then
' Debug.Print "(9)Installed", bInstalled
' Else
' Debug.Print "(10)", .Number, .Description
' .Clear
' End If
' End With
.AddIns("Analysis ToolPak").Installed = vbTrue
' With Err
' If .Number = 0 Then
' Debug.Print "(11)Installed", bInstalled
' Else
' Debug.Print "(12)", .Number, .Description
' .Clear
' End If
' End With
bInstalled = .AddIns("Analysis ToolPak").Installed
With Err
If .Number = 0 Then
Debug.Print "(13)Installed", bInstalled
Else
Debug.Print "(14)", .Number, .Description
.Clear
End If
End With

' sPathXLA = .LibraryPath & "\analysis\FUNCRES.XLA"
' i = PathFileExistsW(StrPtr(.LibraryPath &
"\analysis\FUNCRES.XLA"))
' If i = 0 Then
' Debug.Print "Not Found: ";
' Else
' Debug.Print "Exists: ";
' End If
' Debug.Print sPathXLA
' If i <> 0 Then
' sPathXLL = .LibraryPath & "\analysis\analys32.xll"
' bInstalled = .RegisterXLL(sPathXLL)
' If bInstalled Then
' Debug.Print "Installed: ";
' Else
' Debug.Print "Not Installed: ";
' End If
' Debug.Print sPathXLL
'
' If bInstalled Then
' sPathATP = .AddIns("Analysis ToolPak").Path
' Debug.Print "ATP: "; sPathATP
' Else
' bInstalled = .RegisterXLL(sAnalysisName)
' If bInstalled Then
' Debug.Print "Installed(Yippee!): ";
' sPathATP = .AddIns("Analysis ToolPak").Path
' Debug.Print "ATP: "; sPathATP
' Else
' Debug.Print "Not Installed(Boo Hoo!): ";
' End If
' Debug.Print sPathXLL
' End If
' End If
Debug.Print "(AFTER STUFF!)Installed", bInstalled
'Note, the Analysis ToolPak is now installed and the XIRR bug
does not occur,
bInstalled = .AddIns("Analysis ToolPak").Installed
With Err
If .Number = 0 Then
Debug.Print "Installed", bInstalled
Else
Debug.Print .Number, .Description
.Clear
End If
End With
End If
End With
On Error GoTo 0

sAppPath = App.Path
sWorkbookfile = sAppPath & "\" & "Howard.xls"
i = PathFileExistsW(StrPtr(sWorkbookfile))
If i <> 0 Then
sWorkbookfile = sAppPath & "\" & CStr(CDbl(Now)) & "Howard.xls"
End If
End Sub

Private Sub btnByeBye_Click()
Unload Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
If Not ExcelWbk Is Nothing Then
With ExcelWbk
.SaveAs FileName:=sWorkbookfile
.Close
End With
Set ExcelWbk = Nothing
End If
If Not appExcel Is Nothing Then
appExcel.Quit
Set appExcel = Nothing
End If
End Sub

Private Sub btnRunMe_Click()
Dim rngXIRR As Excel.Range
Dim sCaption As String
Dim sColDate As String
Dim sColValue As String
Dim sXIRR As String

sColDate = "A"
sColValue = "B"
sXIRR = "=XIRR(B2:B4,A2:A4)"
With ExcelWbk
With .Worksheets(1)
Set rngXIRR = .Range("B1")

With rngXIRR
.Formula = sXIRR
.NumberFormat = "0.00000%"
End With

With .Range("A2")
.Value = DateValue("8 March 2008")
.NumberFormat = "d mmm yyyy"
End With
With .Range("B2")
.Value = -10000
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With

With .Range("A3")
.Value = DateValue("8 april 2008")
.NumberFormat = "d mmm yyyy"
End With
With .Range("B3")
.Value = -5000
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With

With .Range("A4")
.Value = DateValue("31 Dec 2008")
.NumberFormat = "d mmm yyyy"
End With
With .Range("B4")
.Value = 18000
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With
End With
With rngXIRR
' .Select
' .Application.SendKeys "{F2}"
' .Application.SendKeys "~"

' .Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, ReplaceFormat:=False
End With
.SaveAs FileName:=sWorkbookfile
.Close
End With
Set rngXIRR = Nothing
Set ExcelWbk = Nothing
appExcel.Quit
Set appExcel = Nothing
End Sub
Click to expand...
 
P

Peter T

Howard Kaikow said:
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.
Click to expand...

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
 
H

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

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
 
H

Howard Kaikow

Peter T said:
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.
Click to expand...

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]
 
P

Peter T

Howard Kaikow said:
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"!
Click to expand...

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
Click to expand...
persistent?"

Don't think so. Of all Excel objects I have looked at, its Windows object is
the one I least understand.
2. [GetMacroRegId]
Click to expand...

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

Regards,
Peter T
 
H

Howard Kaikow

Peter T said:
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.
Click to expand...

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.
Click to expand...

I believe the setting is persistent if changed via the GUI.
I saw your post but didn't understand the question.
Click to expand...

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

Peter T

Howard Kaikow said:
as

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

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
Click to expand...

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
 
H

Howard Kaikow

Peter T said:
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
Click to expand...
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.
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Printing Macro 4
find and replace multiple values in one statement 3
Array code only works on selected sheet 14
How to add "'" into string? 9
Using Cell.Replace 1
Macro for deleting non-printable characters from EXCEL 2
Need help with correct syntax please 2
Macro to select columns and replace data 0

Top