I am using Excel 2003 to programmatically create a workbook.
A bunch of cells contain the XIRR function..

MSFT KB article 291058 describes a bug in using XIRR.
3 solutions are offered. The 3rd solution does not work, at least for me.

I am trying to program the 1st two solutions so they will run by automating
Excel from VB 6.

The code below works when placed in Personal.XLS, and run from within Excel.

However, when moved to VB 6, the code is, in effect, a NO-OP.

From VB 6, using Method 1, I can see that the F2 gets sent, and works as
descibed in the KB article.
But the change does not stick.

From VB 6, Method 2 does not change anything

I have also tried using Application.Rin from with the VB 6 program, again
the code does not do the deed.

Public Sub FixXIRR()
' Method 1`:
' SendKeys "{F2}"
' SendKeys "{Enter}"

'Method 2:
With ActiveWorkbook.Worksheets(1)
.Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End With
End Sub

In VB 6. I am creating the workbook, saving and closing the workbook, then
re-opening the workbook,
using the following code. I ASSuME that I am screwing up somewhere, but
these old damaged eyes do not see it.

.Save
.Saved = True
.Close
Set ExcelWbk = appExcel.Workbooks.Open(sWorkbookfile)
With ExcelWbk
.Worksheets(1).Activate
' appExcel.Run "'J:\Documents and Settings\Howard
Kaikow\Application Data\Microsoft\Excel\XLSTART\Personal.XLS'!FixXIRR "
With appExcel.ActiveWorkbook.Worksheets(1)
.Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, ReplaceFormat:=False
End With
.Save
.Saved = True
.Close
End With

For some reason I cannot replicate the problem, XIRR cell gives correct
result on file open in a new instance of XL. Long shots -

Is your VB6 launching Excel, if so do you also explicitly load the ATP addin
rather than expect it to load as an installed addin.

I see you have later version Replace options, I take it you have updated
your XL. Does the Replace function work without error.

What happens if you write a formula with an ATP function to a new cell.

Regards,
Peter T

"Howard Kaikow" wrote in message
...
I am using Excel 2003 to programmatically create a workbook.
A bunch of cells contain the XIRR function..

MSFT KB article 291058 describes a bug in using XIRR.
3 solutions are offered. The 3rd solution does not work, at least for me.

I am trying to program the 1st two solutions so they will run by
automating
Excel from VB 6.

The code below works when placed in Personal.XLS, and run from within
Excel.

However, when moved to VB 6, the code is, in effect, a NO-OP.

From VB 6, using Method 1, I can see that the F2 gets sent, and works as
descibed in the KB article.
But the change does not stick.

From VB 6, Method 2 does not change anything

I have also tried using Application.Rin from with the VB 6 program, again
the code does not do the deed.

Public Sub FixXIRR()
' Method 1`:
' SendKeys "{F2}"
' SendKeys "{Enter}"

'Method 2:
With ActiveWorkbook.Worksheets(1)
.Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End With
End Sub

In VB 6. I am creating the workbook, saving and closing the workbook, then
re-opening the workbook,
using the following code. I ASSuME that I am screwing up somewhere, but
these old damaged eyes do not see it.

.Save
.Saved = True
.Close
Set ExcelWbk = appExcel.Workbooks.Open(sWorkbookfile)
With ExcelWbk
.Worksheets(1).Activate
' appExcel.Run "'J:\Documents and Settings\Howard
Kaikow\Application Data\Microsoft\Excel\XLSTART\Personal.XLS'!FixXIRR "
With appExcel.ActiveWorkbook.Worksheets(1)
.Cells.Replace What:="=", Replacement:="=",
LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, ReplaceFormat:=False
End With
.Save
.Saved = True
.Close
End With

Automating with VB6 had to do this -

xlApp.Workbooks.Open xlApp.LibraryPath & "\analysis\FUNCRES.XLA"
' runautomacros doesn't help
b = xl.RegisterXLL(xlapp.LibraryPath & "analysis\analys32.xll")
'b= false so need to explicitly open the xll

xlApp.Workbooks.Open xl.LibraryPath & "\analysis\analys32.xll"
b = xl.RegisterXLL("analys32.xll") ' true

Having done that I didn't then need to do your replace = stuff.

Guess the path may differ in setups/versions. Might be worth attempting to
open as above under an error handler. If it fails get the path like this.

sPath = xlApp.AddIns("Analysis ToolPak").Path

Then store the path in the registry, don't want to reference Addins each
time (slow)

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
For some reason I cannot replicate the problem, XIRR cell gives correct
result on file open in a new instance of XL. Long shots -

Is your VB6 launching Excel, if so do you also explicitly load the ATP
addin
rather than expect it to load as an installed addin.

I see you have later version Replace options, I take it you have updated
your XL. Does the Replace function work without error.

What happens if you write a formula with an ATP function to a new cell.

Regards,
Peter T

"Howard Kaikow" wrote in message
...
I am using Excel 2003 to programmatically create a workbook.
A bunch of cells contain the XIRR function..

MSFT KB article 291058 describes a bug in using XIRR.
3 solutions are offered. The 3rd solution does not work, at least for
me.

I am trying to program the 1st two solutions so they will run by
automating
Excel from VB 6.

The code below works when placed in Personal.XLS, and run from within
Excel.

However, when moved to VB 6, the code is, in effect, a NO-OP.

From VB 6, using Method 1, I can see that the F2 gets sent, and works as
descibed in the KB article.
But the change does not stick.

From VB 6, Method 2 does not change anything

I have also tried using Application.Rin from with the VB 6 program,
again
the code does not do the deed.

Public Sub FixXIRR()
' Method 1`:
' SendKeys "{F2}"
' SendKeys "{Enter}"

'Method 2:
With ActiveWorkbook.Worksheets(1)
.Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False,
ReplaceFormat:=False
End With
End Sub

In VB 6. I am creating the workbook, saving and closing the workbook,
then
re-opening the workbook,
using the following code. I ASSuME that I am screwing up somewhere, but
these old damaged eyes do not see it.

.Save
.Saved = True
.Close
Set ExcelWbk = appExcel.Workbooks.Open(sWorkbookfile)
With ExcelWbk
.Worksheets(1).Activate
' appExcel.Run "'J:\Documents and Settings\Howard
Kaikow\Application Data\Microsoft\Excel\XLSTART\Personal.XLS'!FixXIRR "
With appExcel.ActiveWorkbook.Worksheets(1)
.Cells.Replace What:="=", Replacement:="=",
LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, ReplaceFormat:=False
End With
.Save
.Saved = True
.Close
End With

"Peter T" <peter_t@discussions wrote in message
...
Automating with VB6 had to do this -

xlApp.Workbooks.Open xlApp.LibraryPath & "\analysis\FUNCRES.XLA"
' runautomacros doesn't help
b = xl.RegisterXLL(xlapp.LibraryPath & "analysis\analys32.xll")
'b= false so need to explicitly open the xll

xlApp.Workbooks.Open xl.LibraryPath & "\analysis\analys32.xll"
b = xl.RegisterXLL("analys32.xll") ' true

Having done that I didn't then need to do your replace = stuff.

Guess the path may differ in setups/versions. Might be worth attempting to
open as above under an error handler. If it fails get the path like this.

sPath = xlApp.AddIns("Analysis ToolPak").Path

Then store the path in the registry, don't want to reference Addins each
time (slow)

I'll give it a try, but I do not think that this is the problem because,
if I right click on the DEsktop, create a new Excel workbook, and open that
workbook, the add-in is already loaded.

"Howard Kaikow" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
Automating with VB6 had to do this -

xlApp.Workbooks.Open xlApp.LibraryPath & "\analysis\FUNCRES.XLA"
' runautomacros doesn't help
b = xl.RegisterXLL(xlapp.LibraryPath & "analysis\analys32.xll")
'b= false so need to explicitly open the xll

xlApp.Workbooks.Open xl.LibraryPath & "\analysis\analys32.xll"
b = xl.RegisterXLL("analys32.xll") ' true

Having done that I didn't then need to do your replace = stuff.

Guess the path may differ in setups/versions. Might be worth attempting
to
open as above under an error handler. If it fails get the path like
this.

sPath = xlApp.AddIns("Analysis ToolPak").Path

Then store the path in the registry, don't want to reference Addins each
time (slow)

I'll give it a try, but I do not think that this is the problem because,
if I right click on the DEsktop, create a new Excel workbook, and open
that
workbook, the add-in is already loaded.

Are you automating a new instance or picking up a user opened instance. If
the former I think you will need to explicitly load the addin even if
already installed. In addition also need to load and register the xll.
Actually not even sure if necessary to load the xla, maybe just the xll and
register.

Ignore the inconsistent "xl" & "xlApp" in my post, wasn't like that in my
code!

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Are you automating a new instance or picking up a user opened instance. If
the former I think you will need to explicitly load the addin even if
already installed. In addition also need to load and register the xll.
Actually not even sure if necessary to load the xla, maybe just the xll
and
register.

Yes, that may be the problem,

I'm creating a new instance of Excel.

The code below seems to work.

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

Set appExcel = New Excel.Application
With appExcel
sPathXLA = .LibraryPath & "\analysis\FUNCRES.XLA"
.Visible = True
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 Not bInstalled Then
.Workbooks.open sPathXLL
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
Else
sPathATP = .AddIns("Analysis ToolPak").Path
Debug.Print "ATP: "; sPathATP
End If
End If
End With

appExcel.Visible = True
sAppPath = App.Path
sWorkbookfile = sAppPath & "\" & "Howard.xls"
Set ExcelWbk = appExcel.Workbooks.Add()
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 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

Putting the code below in the Load event appears to do the deed.
I'll clean it up a bit later.

Note that, on my system, the addin does not get re-nstalled, but the mere
act of
adding the code eliminates the issue. So Excel has a dirty structure
somewhere,

Is the .Workbooks.Open sPathXLL needed?
The file's existence is verified via the PathFileExitsW API.

With appExcel
bInstalled = vbTrue
sPathXLA = .LibraryPath & "\analysis\FUNCRES.XLA"
i = PathFileExistsW(StrPtr(sPathXLA))
If i = 0 Then
bInstalled = False
LogMessage "File not found: " & sPathXLA
Else
sPathXLL = .LibraryPath & "\analysis\analys32.xll"
i = PathFileExistsW(StrPtr(sPathXLL))
If i = 0 Then
bInstalled = False
LogMessage "File not found: " & sPathXLL
Else
bInstalled = .RegisterXLL(sPathXLL)
If Not bInstalled Then
' Do we need the Open as we have already verified file's
existence
.Workbooks.Open sPathXLL
bInstalled = .RegisterXLL(sAnalysisName)
If bInstalled Then
LogMessage "Installed: " & .AddIns("Analysis
ToolPak").Path
Else
LogMessage "Could not install: " & sPathXLL
End If
End If
End If
End If
If Not bInstalled Then
Unload Me
Exit Sub
End If
End With

"Peter T" <peter_t@discussions wrote in message
...
For some reason I cannot replicate the problem, XIRR cell gives correct
result on file open in a new instance of XL. Long shots -

To reproduce the problem, one has to programmatically create the stuff using
VB 6.

Below, I give a full example that reproduces the problem using VB 6 to
automate Excel.

Is your VB6 launching Excel, if so do you also explicitly load the ATP
addin
rather than expect it to load as an installed addin.

On my system, the ATP does load automastically.
Later, I expect to add code to see whether the ATP is loaded.

I see you have later version Replace options, I take it you have updated
your XL. Does the Replace function work without error.

Works with Excel, but not when run from VB 6.


What happens if you write a formula with an ATP function to a new cell.

That's what I am doing. I am creating the workbook programmatically.
Although my test example uses only 1 XIRR function, the real code has about
25, and could have even more.
'-----------------------------------------------------
Option Explicit
Private appExcel As Excel.Application

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

Private Sub btnByeBye_Click()
Unload Me
End Sub

Private Sub btnRunMe_Click()
Dim ExcelWbk As Excel.Workbook
Dim i As Long
Dim rngXIRR As Excel.Range
Dim sAppPath As String
Dim sCaption As String
Dim sCaption1 As String
Dim sColDate As String
Dim sColValue As String
Dim sWorkbookfile As String
Dim sXIRR As String

Set appExcel = New Excel.Application
appExcel.Visible = True
sAppPath = App.Path
sWorkbookfile = sAppPath & "\" & "Howard.xls"
Set ExcelWbk = appExcel.Workbooks.Add()
i = PathFileExistsW(StrPtr(sWorkbookfile))
If i < 0 Then
sWorkbookfile = sAppPath & "\" & CStr(CDbl(Now)) & "Howard.xls"
End If
sCaption1 = Mid$(sWorkbookfile, InStrRev(sWorkbookfile, "\") + 1)
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

Have you tried appExcel.CalculateFull

"Howard Kaikow" wrote:

I am using Excel 2003 to programmatically create a workbook.
A bunch of cells contain the XIRR function..

MSFT KB article 291058 describes a bug in using XIRR.
3 solutions are offered. The 3rd solution does not work, at least for me.

I am trying to program the 1st two solutions so they will run by automating
Excel from VB 6.

The code below works when placed in Personal.XLS, and run from within Excel.

However, when moved to VB 6, the code is, in effect, a NO-OP.

From VB 6, using Method 1, I can see that the F2 gets sent, and works as
descibed in the KB article.
But the change does not stick.

From VB 6, Method 2 does not change anything

I have also tried using Application.Rin from with the VB 6 program, again
the code does not do the deed.

Public Sub FixXIRR()
' Method 1`:
' SendKeys "{F2}"
' SendKeys "{Enter}"

'Method 2:
With ActiveWorkbook.Worksheets(1)
.Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End With
End Sub

In VB 6. I am creating the workbook, saving and closing the workbook, then
re-opening the workbook,
using the following code. I ASSuME that I am screwing up somewhere, but
these old damaged eyes do not see it.

.Save
.Saved = True
.Close
Set ExcelWbk = appExcel.Workbooks.Open(sWorkbookfile)
With ExcelWbk
.Worksheets(1).Activate
' appExcel.Run "'J:\Documents and Settings\Howard
Kaikow\Application Data\Microsoft\Excel\XLSTART\Personal.XLS'!FixXIRR "
With appExcel.ActiveWorkbook.Worksheets(1)
.Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, ReplaceFormat:=False
End With
.Save
.Saved = True
.Close
End With

"Ralph" wrote in message
...
Have you tried appExcel.CalculateFull

Yes, it doesn't fix things.

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.

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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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]

"Howard Kaikow" wrote in message
"Peter T" <peter_t@discussions wrote in message
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"!

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

"Peter T" <peter_t@discussions wrote in message
...
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.