Array functions inVB code

[Savanur]

Hi all,

How to use Array functions in VB code. We use Worksheetfunction.xlfunction to access xl functions.

But how about array functons..?

Thanks in advance.

Savanur

 

[Corticus]

I use DAO 3.6.

In the VB editor, go to Tools|References, and uncheck the ActiveX 2.1 library reference and add Microsoft DAO 3.6.

Now in code, you can do something like this:

Dim db As Database
Dim rs As Recordset
Dim selSQL As String
Dim myArray() As Variant
Dim recCount As Integer, i As Integer

    Set db = CurrentDb()
    
    'enter the sql to generate what's going into the array
    'adjust to suit
    selSQL = "SELECT [myTable].* FROM [myTable];"
    
    Set rs = db.OpenRecordset(selSQL)
    rs.MoveLast
    recCount = rs.RecordCount
    
    'size the array
    ReDim myArray(1 To recCount)
    rs.MoveFirst
    
    'stack the array, adjust myField to suit
    For i = 1 To recCount
        myArray(i) = rs!myField
        rs.MoveNext
    Next i

This will fill an array up with all the data from myField in myTable.

 

[mdmilner]

I think he may mean "how do you setup a function/subroutine to receive a variable number of values"

If yes, you use this as your Function/Sub line:

Public Sub subName(ParamArray strVal() As Variant)

You must use a Variant array to do this.
I believe you can specify other variables to be passed to the function, but the last on the list must be the Parameter Array.

Mike