Function to return array?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jaroslav - 27 Dec 2006 10:31 GMT

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?

Reply to this Message

John Nurick - 27 Dec 2006 13:03 GMT

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.

>Is it possible to write a function to return array?
>
[quoted text clipped - 40 lines]
>
>Can anyone help me, please?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

RoyVidar - 27 Dec 2006 13:25 GMT

"Jaroslav" <Jaroslav@discussions.microsoft.com> wrote in message
<865D27B8-2946-4258-BE91-515967299DDB@microsoft.com>:

> Is it possible to write a function to return array?
>
[quoted text clipped - 42 lines]
>
> 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.

Signature

Roy-Vidar

Reply to this Message