none
Access 2010: Workaround for YIELD function missing from Excel WorksheetFunction Object model? RRS feed

  • Question

  • Trying to convert a bond trading app from Access 2003 to 2010, and have hit the wall on the Excel YIELD function.

    Obviously it is there in Excel, but it seems pretty clear that Microsoft dropped it from the VBA object model when they went to Office 2007 and did not see fit to remedy the situation in Office 2010.

    i.e. in VBA code  myExcelObject.WorksheetFunction.Yield fails and the autocomplete after WorksheetFunction does not offer up Yield as a choice.

    Have I got it right so far?

    The obvious workaround would seem to be to create a dummy Excel worksheet, open up an object linking to it, feed the parameters to cells in that sheet, and then pull the strings to make Excel do the YIELD calc and copy the results back into Access.... But for tens or even hundreds of thousands of calculations back-to-back, I have to wonder about speed....

    If so, can anybody suggest something better?


    -- PeteCresswell



    • Edited by PeteCress Thursday, February 1, 2018 6:43 PM
    Thursday, February 1, 2018 2:25 PM

All replies

  • See if the below link helps:

    https://stackoverflow.com/questions/45036586/using-yield-function-in-vba


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, February 1, 2018 4:09 PM
  • Hi PeteCress,

    I can see that You can easily use the Yield function from Excel UI.

    But you are not able to use the same function in VBA code.

    As a work around , To execute Yield function in VBA code and get the value you can do something like below.

    Sub demo()
      
        Debug.Print Evaluate("=YIELD( B1, B2, 10%, 101, 100, 4 )")
        
    End Sub

    Output:

    Below code example from Access VBA.

    Sub demo()
    Dim xlBook As Object
    Dim xlApp As Object
    Dim sht As Worksheet
        Set xlApp = CreateObject("Excel.Application")
        Set xlBook = xlApp.Workbooks.Add
      Set sht = xlBook.Sheets(1)
        sht.Range("B1").Value = "1-Jan-10"
        sht.Range("B2").Value = "30-Jun-15"
      Debug.Print xlApp.Evaluate("=YIELD(B1,B2, 10, 101, 100, 4) ")
        'xlBook.SaveAs ("C:\Documents and Settings\Lol\Desktop\Trial.xls")
    
    End Sub
    

    Output:

    Further, You can modify the code based on your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, February 2, 2018 5:37 AM
    Moderator
  • Hi PeteCress,

    Is your issue solved?

    I find that you did not follow up this thread after posting the issue.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 7, 2018 9:41 AM
    Moderator