"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