ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Geting around XIRR error (https://www.excelbanter.com/excel-programming/410297-geting-around-xirr-error.html)

Howard Kaikow
Geting around XIRR error
 
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
Geting around XIRR error
 
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

Ralph
Geting around XIRR error
 
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

Peter T
Geting around XIRR error
 
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

Howard Kaikow
Geting around XIRR error
 
"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

Howard Kaikow
Geting around XIRR error
 
"Ralph" wrote in message
...
Have you tried appExcel.CalculateFull

Yes, it doesn't fix things.

Howard Kaikow
Geting around XIRR error
 
"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.

Peter T
Geting around XIRR error
 
"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

Howard Kaikow
Geting around XIRR error
 
"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

Howard Kaikow
Geting around XIRR error
 
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
Geting around XIRR error
 
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

"Howard Kaikow" wrote in message
...
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
Geting around XIRR error
 
"Howard Kaikow" wrote in message
...
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
Geting around XIRR error
 
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" wrote in message
...
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
Geting around XIRR error
 
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

--





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

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

"Rob Bovey" wrote in message
...
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

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

--











Peter T
Geting around XIRR error
 
Thank goodness for coffee <g

Regards,
Peter T

"Rob Bovey" wrote in message news:%
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.

Howard Kaikow
Geting around XIRR error
 
"Peter T" <peter_t@discussions wrote in message
...
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
Geting around XIRR error
 
"Howard Kaikow" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
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.


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

Howard Kaikow
Geting around XIRR error
 
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

Rob Bovey
Geting around XIRR error
 
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" wrote in message
...
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

Peter T
Geting around XIRR error
 
"Howard Kaikow" wrote in message

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.

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

Howard Kaikow
Geting around XIRR error
 
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
Geting around XIRR error
 
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.

Howard Kaikow
Geting around XIRR error
 
"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]

Peter T
Geting around XIRR error
 
"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

Howard Kaikow
Geting around XIRR error
 
"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.

Peter T
Geting around XIRR error
 
"Howard Kaikow" wrote in message
"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.

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


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

Howard Kaikow
Geting around XIRR error
 
"Peter T" <peter_t@discussions wrote in message
...
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.

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.

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


All times are GMT +1. The time now is 07:31 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com