Function to return array?

[Jaroslav]

Is it possible to write a function to return array?

I've tried to get some records from table and return them from function as
array:

Public Function Get_ZC_CURVE(paDATE As Date, paCURRENCY As Long) As Variant()

Dim rstZC_CURVE As ADODB.Recordset
Dim strSQLString As String
Dim fld As ADODB.Field
Dim nPiCounter As Integer

Set rstZC_CURVE = New ADODB.Recordset

strSQLString = "SELECT ZC_CURVES.MATURITY_DATE,
ZC_CURVES.DISCOUNT_FACTOR FROM ZC_CURVES WHERE (((ZC_CURVES.CURRENCY_ID)=" &
paCURRENCY & ") AND ((ZC_CURVES.INPUT_DATE)=" & paDATE & "));"

rstZC_CURVE.Source = strSQLString
rstZC_CURVE.CursorLocation = adUseClient
rstZC_CURVE.CursorType = adOpenDynamic
rstZC_CURVE.ActiveConnection = CurrentProject.Connection
rstZC_CURVE.Open

ReDim Get_ZC_CURVE(rstZC_CURVE.RecordCount, 2)

nPiCounter = 1
rstZC_CURVE.MoveFirst
Do Until rstZC_CURVE.EOF
Get_ZC_CURVE(nPiCounter, 1) =
rstZC_CURVE.Fields.Item("MATURITY_DATE").Value
Get_ZC_CURVE(nPiCounter, 2) =
rstZC_CURVE.Fields.Item("DISCOUNT_FACTOR").Value
nPiCounter = nPiCounter + 1
rstZC_CURVE.MoveNext

End Function

But it is not possible to do it this way. When I've tried to declare local
array within the function and assign it as return value by the Return
statement, I realized that in the VBA it is not possible (or I did it wrong
way).

Can anyone help me, please?

 

[John Nurick]

Hi Jaroslav,

This works fine for me:

Function TestArray97(N As Long, X As Long) As Variant
Dim A() As Long 'can also use static array
Dim j As Long

ReDim A(N)
For j = 0 To UBound(A)
A(j) = X + j
Next
TestArray97 = A
End Function


Maybe your problem is that VBA doesn't have a Return statement.

 

[RoyVidar]

Is it possible to write a function to return array?

I've tried to get some records from table and return them from
function as array:

Public Function Get_ZC_CURVE(paDATE As Date, paCURRENCY As Long) As
Variant()

Dim rstZC_CURVE As ADODB.Recordset
Dim strSQLString As String
Dim fld As ADODB.Field
Dim nPiCounter As Integer

Set rstZC_CURVE = New ADODB.Recordset

strSQLString = "SELECT ZC_CURVES.MATURITY_DATE,
ZC_CURVES.DISCOUNT_FACTOR FROM ZC_CURVES WHERE
(((ZC_CURVES.CURRENCY_ID)=" & paCURRENCY & ") AND
((ZC_CURVES.INPUT_DATE)=" & paDATE & "));"

rstZC_CURVE.Source = strSQLString
rstZC_CURVE.CursorLocation = adUseClient
rstZC_CURVE.CursorType = adOpenDynamic
rstZC_CURVE.ActiveConnection = CurrentProject.Connection
rstZC_CURVE.Open

ReDim Get_ZC_CURVE(rstZC_CURVE.RecordCount, 2)

nPiCounter = 1
rstZC_CURVE.MoveFirst
Do Until rstZC_CURVE.EOF
Get_ZC_CURVE(nPiCounter, 1) =
rstZC_CURVE.Fields.Item("MATURITY_DATE").Value
Get_ZC_CURVE(nPiCounter, 2) =
rstZC_CURVE.Fields.Item("DISCOUNT_FACTOR").Value
nPiCounter = nPiCounter + 1
rstZC_CURVE.MoveNext

End Function

But it is not possible to do it this way. When I've tried to declare
local array within the function and assign it as return value by the
Return statement, I realized that in the VBA it is not possible (or
I did it wrong way).

Can anyone help me, please?

Starting with the 2000 version, functions can return arrays. The
following small sample should illustrate one way of doing so.

In previous versions, you'd use Variants.

Private Function GetStringArray() As String()

Dim arr() As String
Dim lngCount As Long

ReDim arr(10)
For lngCount = 0 To 10
arr(lngCount) = "test" & CStr(lngCount)
Next lngCount

GetStringArray = arr

End Function

Private Sub TestStringArray()

Dim arr() As String
Dim lngCount As Long

arr() = GetStringArray

For lngCount = 0 To UBound(arr)
Debug.Print arr(lngCount),
Next lngCount

End Sub

But why return an array, why not just use the recordset?

BTW, you lack a Loop between the .MoveNext and End Function.