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
| Free forum by Nabble | Edit this page |