QuantLib › quantlib-users

Code snippet for application.run in VBA

Classic

List

Threaded

6 messages Options

rohan talwar

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

cheng li

答复: Code snippet for application.run in VBA

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

rohan talwar

Re: 答复: Code snippet for application.run in VBA

Thanks, This worked fine. Much appreciated.

When I fed typed variables it failed in my earlier example, however yours works fine. Is it because its a variant array of variants?

Is there anywhere you can point me to which elaborates on this behaviour? If you can, great.

Kind regards

Rohan Talwar

On 06/02/2013, at 0:44, "Cheng Li" <[hidden email]> wrote:

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 [[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

cheng li

答复: 答复: Code snippet for application.run in VBA

No. The correct behavior doesn’t depend on variant type. Actually when you pass the dates or the values in the function “qlTimeSeries”, you should keep them as arrays. E.g. dates should be an array of long or variants, values should be an array of double value. In your example, you have adjusted the original dates array and values array into strings. This is not necessary and totally makes the function confused.

I am attaching another example. This is just the previous sample version with explicit data type declaration.

Option Explicit

Public Sub testMain()

Dim StartDate As Long

StartDate = 40909

Dim datesArray(0 To 19) As Long

Dim valueArray(0 To 19) As Double

Dim i As Integer

For i = 0 To 19

datesArray(i) = StartDate + i

valueArray(i) = i * i

Next i

Dim sID As String

sID = "myTimeSeries"

Dim timeSeriesObject As String

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

Dim sampleData As Double

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

End Sub

发件人: Rohan TALWAR [mailto:[hidden email]]
发送时间: 2013年2月9日 15:01
收件人: Cheng Li
抄送: quantlib-users
主题: Re: [Quantlib-users] 答复: Code snippet for application.run in VBA

Thanks, This worked fine. Much appreciated.

When I fed typed variables it failed in my earlier example, however yours works fine. Is it because its a variant array of variants?

Is there anywhere you can point me to which elaborates on this behaviour? If you can, great.

Kind regards

Rohan Talwar

On 06/02/2013, at 0:44, "Cheng Li" <[hidden email]> wrote:

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 [[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

cheng li

答复: 答复: Code snippet for application.run in VBA

In reply to this post by rohan talwar

In your example, below codes should work.

Dim sId as String

sID = “test”

Dim s as String

s = Application.Run("qlTimeSeries", sID, ddates, dvalue)

Thanks, This worked fine. Much appreciated.

When I fed typed variables it failed in my earlier example, however yours works fine. Is it because its a variant array of variants?

Is there anywhere you can point me to which elaborates on this behaviour? If you can, great.

Kind regards

Rohan Talwar

On 06/02/2013, at 0:44, "Cheng Li" <[hidden email]> wrote:

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 [[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

rohan talwar

Re: 答复: 答复: Code snippet for application.run in VBA

Thanks very much, Cheng.

Kind regards

Rohan Talwar

On 09/02/2013, at 20:30, "Cheng Li" <[hidden email]> wrote:

In your example, below codes should work.

Dim sId as String
sID = “test”
Dim s as String
s = Application.Run("qlTimeSeries", sID, ddates, dvalue)

发件人: Rohan TALWAR [[hidden email]]
发送时间: 2013年2月9日 15:01
收件人: Cheng Li
抄送: quantlib-users
主题: Re: [Quantlib-users] 答复: Code snippet for application.run in VBA

Thanks, This worked fine. Much appreciated.

When I fed typed variables it failed in my earlier example, however yours works fine. Is it because its a variant array of variants?

Is there anywhere you can point me to which elaborates on this behaviour? If you can, great.

Kind regards
Rohan Talwar

On 06/02/2013, at 0:44, "Cheng Li" <[hidden email]> wrote:
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 [[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