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