Geting around XIRR error

[Howard Kaikow]

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]

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

 

[Ralph]

Have you tried appExcel.CalculateFull

 

[Peter T]

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

 

[Howard Kaikow]

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

 

[Howard Kaikow]

Have you tried appExcel.CalculateFull

Yes, it doesn't fix things.

 

[Howard Kaikow]

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.

 

[Peter T]

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

 

[Howard Kaikow]

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

 

[Howard Kaikow]

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

 

[Rob Bovey]

Hi Howard,

Assuming you can always be sure the analysis toolpak has been physically
installed on your computer, all you should need to do is this:

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

 

[Peter T]

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,

Not sure I follow. There is nothing in your code that attempts to re-install
the addin and neither is that necessary. What I assume is happening is that
the addin remains installed throughout but does not get loaded until you
explicitly load it. That's normal with automation. In passing any auto-run
macros need to be called on load & unload (before xl-quit) as applicable, as
they don't auto-run with automation. wb.RunAutoMacros().

Is the .Workbooks.Open sPathXLL needed?

Again not sure I follow. You will need to "open" the xll and so you will
need its path. Most likely it will be in the default, if not can be obtained
from the addins colllection irrespective as to whether or not it's installed
(assuming it has been unpacked and still in the colllection).

I know it's necessary to load & register the xll but I didn't get round to
checking if also necessary to first load Funcres.xla.

Regards,
Peter T

 

[Peter T]

Hi Rob,

I'd need to make a new VB6 demo to check (didn't keep the last one) but does
that actually load the XLL in an automated instance.

Previously when I simply loaded the ATP-xla (workbooks.open) the XLL did not
load and register until doing both with code.

Regards,
Peter T

 

[Rob Bovey]

Hi Peter,

As far as I know, loading the ATP using Addins.Installed runs everything
required to register its functions with Excel. I just whipped up the
following code in the click event of a button on a VB6 form:

Option Explicit

Private mxlApp As Excel.Application

Private Sub cmdStart_Click()
Set mxlApp = New Excel.Application
mxlApp.AddIns("Analysis Toolpak").Installed = True
mxlApp.Visible = True
End Sub

After this I was able to use several ATP functions in the Excel instance I'd
created without any problem.

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

 

[Peter T]

Hi again,

Everything works if I do it like this -

mXLApp.AddIns("Analysis Toolpak").Installed = False
mXLApp.AddIns("Analysis Toolpak").Installed = True

I wonder if when you tested your ATP addin was not previously installed. If
so, I would assume if you repeated the test the addin would not load a
second time.

Otherwise if for you it consistently works without doing the Installed =
False then = True I can only assume there's a version difference.

However the .Installed False/True approach is clearly much simpler than
loading the XLA from file, then loading and registering the XLL, as I had
previously proposed.

I'm curious though as to why loading the XLA from file does not also load
and register the XLL. When I tested events were enabled, there was an active
wb, and I also did workbooks("funcres.xla").RunAutoMacros xlAutoOpen.

Regards,
Peter T

 

[Rob Bovey]

Hi Peter,

I wonder if when you tested your ATP addin was not previously installed.
If
so, I would assume if you repeated the test the addin would not load a
second time.

I verified that the ATP was not previously installed and it works
correctly here in both Excel 2000 and 2003 with just AddIns("Analysis
Toolpak").Installed = True. Not sure why we're seeing different behavior.
Your solution is not a bad one, however. The extra Installed=False doesn't
hurt anything and if you're targeting a demand-loaded add-in like Solver
then it's required if the add-in was previously installed.

I'm curious though as to why loading the XLA from file does not also load
and register the XLL. When I tested events were enabled, there was an
active
wb, and I also did workbooks("funcres.xla").RunAutoMacros xlAutoOpen.

It's been so long since I've had to dig into the guts of Funcres.xla
that I honestly don't know. It certainly seems like it should work under
those circumstances. The only thing that wouldn't happen is that the
Workbook_AddinInstall event wouldn't fire as a result of simply opening a
workbook and using RunAutoMacros. I don't remember Funcres.xla using this
event procedure, though.

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

Hi again,

Everything works if I do it like this -

mXLApp.AddIns("Analysis Toolpak").Installed = False
mXLApp.AddIns("Analysis Toolpak").Installed = True

I wonder if when you tested your ATP addin was not previously installed.
If
so, I would assume if you repeated the test the addin would not load a
second time.

Otherwise if for you it consistently works without doing the Installed =
False then = True I can only assume there's a version difference.

However the .Installed False/True approach is clearly much simpler than
loading the XLA from file, then loading and registering the XLL, as I had
previously proposed.

I'm curious though as to why loading the XLA from file does not also load
and register the XLL. When I tested events were enabled, there was an
active
wb, and I also did workbooks("funcres.xla").RunAutoMacros xlAutoOpen.

Regards,
Peter T

 

[Rob Bovey]

Hi Peter,

Let's try this one more time with additional coffee consumed. I wasn't
quite awake last time around. As it turns out, the ATP not being installed
was indeed why the test worked on my computers. Your method of doing an
Installed=False followed by an Installed=True is definitely the correct one
because it will work if the ATP is already installed and it won't hurt
anything if it isn't. Thanks for pointing that out.

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

 

[Peter T]

Thank goodness for coffee <g>

Regards,
Peter T

 

[Howard Kaikow]

Thank goodness for coffee <g>

At home, I drink nothing but water, OJ, Glucerna, and V8.

On my systems, XIRR is installed, I use it every day.
The implication is that a NEW instance of Excel did not load the analysis
thingee.

I'll have to play wit this a bit.

 

[Peter T]

At home, I drink nothing but water, OJ, Glucerna, and V8.

On my systems, XIRR is installed, I use it every day.
The implication is that a NEW instance of Excel did not load the analysis
thingee.

I'll have to play wit this a bit.

The point is installed addins do not automatically load in an instance
created with automation, neither does Personal.xls.

As Rob has pointed out setting the addin's Installed property = true will
load the addin, providing it was previously False, hence the False/True
toggle.

I have also since had a good cup of coffee and a rethink. Although the
False/True toggle only requires two lines of code, the first time
referencing the Addin's collection can be quite slow. Loading the addin from
file and registering is more code but typically will work much faster.

Think I would start with the assumption funcres.xla & analys32.xll are in
the default location, but check with your file-exists function. If so load
each respectively and register the xll (btw seems the xla must be loaded
first).
If not, has the path been saved (see below), try similar with that.

If the files do not exist in expected location, then need to resort to using
the addins collection, eg

on error resume next
set adn = mXLApp.AddIns("Analysis Toolpak")
' resume normal error handling
if not adn is nothing then
if adn.installed then adn.installed = false
' or simply adn.installed = false
adn.installed = true
' store adn.path in registry for future use
else
' blah

If you need to load other addins in an automated instance call
wb.RunAutoMacros(xlAutoOpen) on open, and similarly xlAutoClose before quit
(not only addins, any wb that potentially has auto-run macros)

Regards,
Peter T