Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 731
  • Last Modified:

Excell Financial Analisis Functions from Access VBA

Ola,

In Excell I have made use of the Yield function that resides in the financial Analisis add in.
When I remove the add in the worksheet still works but the function call now is changed to the following:

='C:\Program Files\Microsoft Office\OFFICE11\Library\Analysis\ATPVBAEN.XLA'!YIELD(A2,A3,A4,A5,A6,A7,A8)

In Access 97 I tried to make use of this Yield function in excell using the following code
Public Sub Tester()
    Dim lDblYield As Double
    Dim obj As Object
    Set obj = CreateObject("Excel.Application")
    lDblYield = obj.Yield(1, 2, 3, 4, 5, 6, 7)
    obj.Quit
    Set obj = Nothing
    MsgBox lDblYield
End Sub

But it breaks as It cant finds the function. Now how do I make Sure that the excell object I just instantiated recognises the Yield function that is available through the financial analisis add in?
0
DaFou
Asked:
DaFou
  • 7
  • 2
  • 2
  • +3
3 Solutions
 
ajsaastaCommented:
You can install the add-in in VBA:

Create a new module (or use an existing one) and make new Sub

Sub Auto_Open()
    ' ----- Loads the "Analysis ToolPak" -addin when document is opened
    Dim objATP As AddIn
    Set objATP = AddIns("Analysis ToolPak")
    If Not objATP.Installed Then
        AddIns("Analysis ToolPak").Installed = True
    End If
End Sub

This way Excel automaticly loads the addin when document is opened. You could also create a Sub Auto_Close() where you can unload the addin when document is closed:

Sub Auto_Close()
    ' ----- UnLoads the "Analysis ToolPak" -addin when document is closed
    Dim objATP As AddIn
    Set objATP = AddIns("Analysis ToolPak")
    If objATP.Installed Then
        AddIns("Analysis ToolPak").Installed = False
    End If
End Sub

Well, if the .xls sheet is moved to a computer where Analysis ToolPak is not availible, I really dont know how you can load it then. Maybe you have to copy the .xla also?
0
 
ajsaastaCommented:
okI did some testing...

If Analysis ToolPak is not availible (not installed when Excel was installed), Excel (2000) prompts:

"Microsoft Excel can't run this add-in.
This feature is not currently installed. Would you like to install it now?"

The rest is trivial: if you dont install the addin the .xls sheet wont open and if you do install then it works.
0
 
DaFouAuthor Commented:
Dim objATP As AddIn

Breaks :-(
User-defined type not defined

So you sugestion will make this work eventually:

Dim objATP As AddIn
    Set objATP = AddIns("Analysis ToolPak")
    If Not objATP.Installed Then
        AddIns("Analysis ToolPak").Installed = True
    End If
   
      Dim lDblYield As Double
   
    Dim obj As Object
    Set obj = CreateObject("Excel.Application")
   
    lDblYield = obj.Yield(1, 2, 3, 4, 5, 6, 7)
   
    obj.Quit
    Set obj = Nothing
   
    MsgBox lDblYield

?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
DaFouAuthor Commented:
This installs the add in  in the excell object. But why cant I use its Yield function?

Public Sub Tester()

  Dim lDblYield As Double
   
  Dim lObjExcel As Object
  Set lObjExcel = CreateObject("Excel.Application")
   
  Dim lObjAddIn As Object
  Set lObjAddIn = lObjExcel.AddIns("Analysis ToolPak")
   
  If Not lObjAddIn.Installed Then
      lObjAddIn.Installed = True
  End If
  lDblYield = lObjExcel.Yield(1, 2, 3, 4, 5, 6, 7)
   
  lObjExcel.Quit
  Set lObjExcel = Nothing
   
  MsgBox lDblYield
End Sub
0
 
DaFouAuthor Commented:
The code breaks here:
 lDblYield = lObjExcel.Yield(1, 2, 3, 4, 5, 6, 7)

Object does not support this property or method
0
 
srparrCommented:
0
 
DaFouAuthor Commented:
srparr,

in the Yield part of the code examples the following line breaks

objXL.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)

variable xlAutoOpen undefined..
0
 
d_a_hCommented:
Here is a working version (at least in Access 2000)

Public Sub Tester()
    Dim lDblYield
    Dim obj As Object
    Set obj = CreateObject("Excel.Application")
   
    obj.Workbooks.Open (obj.Application.LibraryPath & _
       "\Analysis\atpvbaen.xla")
    ' Runs the AutoOpen macro in the add-in
    obj.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
   
    lDblYield = obj.Application.Run("atpvbaen.xla!Yield", "15/2/1999", "15/11/2007", 0.0575, 95.04287, 100, 2, 0)
    obj.Quit
    Set obj = Nothing
    MsgBox lDblYield
End Sub


**Please Note**
Your example would not work because the arguments were incorrect.  The Yield function requires the following:

YIELD (Settlement{DATE}, Maturity{DATE}, Rate{NUMBER}, Pr{NUMBER}, Redemption{NUMBER}, Frequency{NUMBER(Payments per year)}, Basis{NUMBER(Type of year count CAN ONLY BE 0 - 4)})

Your example had 1 and 2 rather than dates so it would come back with an error code (2016 I think!)

HTH
0
 
DaFouAuthor Commented:
I am sorry but ill need a working version that works in access 97

THe code you provided breaks at the same line telling me the xlAutoOpen variable is not defined
So I changed the xlAutoOpen for an Integer 1

Like this

Public Sub TestIt()
    MsgBox fXLYield("2/15/2008", "11/15/2016", 557, 95.04287, 100, 2, 1)
End Sub
Public Function fXLYield(dtmSettlement As Date, dtmMaturity As Date, dblRate As Double, dblPR As Double, dblRedemption As Double, bytFrequency As Byte, bytBasis As Byte) As Double
    Dim objXL As Object
    Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open (objXL.Application.LibraryPath & "\Analysis\atpvbaen.xla")
    objXL.Workbooks("atpvbaen.xla").RunAutoMacros (1)
    fXLYield = objXL.Application.Run("atpvbaen.xla!yield", dtmSettlement, dtmMaturity, dblRate, dblPR, dblRedemption, bytFrequency, bytBasis)
End Function

But now it breaks on the last statement telling me:
Type Mismatch

any ideas?
0
 
Simon BallCommented:
have you set the recerence to excel in the VBA tools, references microsoft excel 10 library?
0
 
DaFouAuthor Commented:
It also fails on access 2003.

The last line type mismatch
0
 
DaFouAuthor Commented:
Sudonim,

Yes I have set a reference now and am no longer using
objXL.Workbooks("atpvbaen.xla").RunAutoMacros (1)

WIth the reference using the following works fine
objXL.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)

But still somehow my parameters that i pass are not of the right type or something.
0
 
Simon BallCommented:
indeed, looks very tricky.  its beyond my field of experience, but i knew te reference bein set would assist vb in recognising the xlautopen constant...

is tere anyway you can break down your processes into smaller chunks and check each aspect is working...?
0
 
Gustav BrockCIOCommented:
This works fine here after adding a Reference to Excel:

Public Sub Tester()
 
  Dim lDblYield As Double
  Dim obj As Object   ' Note: Dim obj As Excel.Application may cause an exception error.
 
  Set obj = New Excel.Application
 
  obj.Workbooks.Open (obj.Application.LibraryPath & _
     "\Analysis\atpvbaen.xla")
  ' Runs the AutoOpen macro in the add-in
  obj.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
 
  lDblYield = obj.Application.Run("atpvbaen.xla!Yield", "15/2/1999", "15/11/2007", 0.0575, 95.04287, 100, 2, 0)
  obj.Quit
  Set obj = Nothing
 
  MsgBox lDblYield

End Sub

Note that the dates must be date string using local format, here d/m/yyyy.

/gustav
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 7
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now