答复: Code snippet for application.run in VBA

Posted by cheng li on
URL: http://quantlib.414.s1.nabble.com/Code-snippet-for-application-run-in-VBA-tp13996p13997.html

Hi Rohan,

 

Pls see the sample code, be careful quantlibxl only accepte “dates” as long

 

Public Sub testMain()

 

 

    startDate = 40909

   

    Dim datesArray(0 To 19)

    Dim valueArray(0 To 19)

 

    For i = 0 To 19

        datesArray(i) = startDate + i

        valueArray(i) = i * i

    Next i

 

   

    sId = "myTimeSeries"

   

    timeSeriesObject = Application.Run("qlTimeSeries", "myTimeSeries", datesArray, valueArray, , , True)

   

    sampleData = Application.Run("qlTimeSeriesValue", timeSeriesObject, datesArray(2))

End Sub

 

 

Regards,

Cheng

 

发件人: Rohan TALWAR [mailto:[hidden email]]
发送时间: 2013年2月5日 9:04
收件人: quantlib-users
主题: [Quantlib-users] Code snippet for application.run in VBA

 

 

Hi

                I posted a query some days ago. Perhaps there aren’t many QLXL users out there. As a non-developer I use the end-user versions – i.e. compiled packages.

 

I’ve been trying to bridge the gap to avoid rendering the data providers data onto an xl sheet, so I can create an object in the object handler, by doing so in vba. Then persisting it in the normal way. 

 

In my research of the QL help, the coercion features support a string passed (or so it seems). Below is my code, but I’ve been unable to create a time series witht eh below code of anything greater than 41 date and value elements. Any ideas? I’ve tried passing various alternatives which are not mentioned in the coercion info (more of a trial and error). These are commented out for convenience only. 

My questions are as follows:

1.       Are there any ways to instantiate objects without using application.run(…)?

2.       Is there a way to reference the underlying classes (i.e. perhaps referencing a core dll)?

3.       Anything else you can suggest (perhaps without it being … become a developer J)

 

I’m referring to the help at http://quantlib.org/quantlibxl/faq.html under section 4.2 How do I call QuantLibXL functions from Excel VBA & http://quantlib.org/quantlibxl/manual.html under coercion (although the pages have vanished as of today)?

 

Code Snippet:

Public Function CreateTimeSeries_ql(sID As String, ddates() As Long, dvalue() As Double) As String

    Dim s As Variant

    Dim i As Long

    Dim rD As Range

    Dim rV As Range

    'Set rD.Value = dDates

    'Set rV.Value = dValue

   

    'rD.Value = dDates

    'rV.Value = dValue

   

    Dim sdates As String

    Dim sValues As String

    sdates = Array2String(ddates)

    sValues = Array2String(dvalue)

    Call debugPrint(sID, ddates, dvalue, sdates, sValues)

   

    Set s = Application.Run("qlTimeSeries", sID, sdates, sValues)

    's = Application.Run("qlTimeSeries", sID, "41006;41008", "41;2000", False)

    's = Application.Run("qlTimeSeries", sID, Join(dDates, ";"), Join(dValue, ";"), False)

    If UBound(ddates) <> UBound(dvalue) Then

        'then vector length is diff

        Exit Function

    Else

        'put in first member

        's = Application.Run("qlTimeSeries", sID, dDates(0), dValue(0), False)

        'For i = 1 To UBound(dValue)

            'add balance to ts (using loop)

            's = Application.Run("qlTimeSeries", "", Array(41006, 41008), Array(365#, 370), False)

            '

        'Next i

    End If

    CreateTimeSeries_ql = s

End Function

 

 Cheers

Rohan Talwar

------------------------------------------------------------------------------
Free Next-Gen Firewall Hardware Offer
Buy your Sophos next-gen firewall before the end March 2013
and get the hardware for free! Learn more.
http://p.sf.net/sfu/sophos-d2d-feb
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users