Geting around XIRR error

H

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
 
P

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
 
P

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
 
H

Howard Kaikow

Peter T said:
For some reason I cannot replicate the problem, XIRR cell gives correct
result on file open in a new instance of XL. Long shots -
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
 
H

Howard Kaikow

Peter T said:
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)
Click to expand...

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

Peter T

Howard Kaikow said:
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.
Click to expand...

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
 
H

Howard Kaikow

Peter T said:
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.
Click to expand...

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
 
H

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
 
P

Peter T

Howard Kaikow said:
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,
Click to expand...

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?
Click to expand...

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
 
P

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
 
R

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
 
P

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
 
R

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.
Click to expand...

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.
Click to expand...

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 said:
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
Click to expand...
 
H

Howard Kaikow

Peter T said:
Thank goodness for coffee <g>
Click to expand...

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

Peter T

Howard Kaikow said:
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.
Click to expand...

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
 
H

Howard Kaikow

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
 
R

Rob Bovey

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 said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Printing Macro 4
Using Find and Replace to replace " in a macro 1
Macro for deleting non-printable characters from EXCEL 2
find and replace multiple values in one statement 3
Help with a loop please. 10
VBA to Fix Dates 5
Need help with a "If" "Else" VB code 4
How to add "'" into string? 8

Top