Function that returns an array

[lefty2cox]

I have this function that does what it's supposed to do when I run it from the immediate window. The function has one parameter, a number, that it takes in.



Function FunctionName(Num)


The function then populates an array. I'd like the function to return said array but am getting a type mismatch error when I try to assign the array to the function name.

I'm using the line of code:

FunctionName = arrayname

at the end of the function. Function works fine until I add this line. let me know if you need to see the code. I didn't want to clutter up the post if not necessary.

[CJ_London]

I don't believe you can have a function of type array but you can return an array byref

e.g.

sub arrayfunction(num as long, byref arr() as long)
do stuff with array
end sub

and then call it
dim a() as array
dim l as long
arrayfunction(l,a)

[orange]

I'm not clear on your requirement. You may get some ideas from this Chip Pearson article regarding ParamArray. It mentions excel, but should work with Access vba.

You may also get ideas from this from http://stackoverflow.com/questions/2...-to-paramarray

 Sub test()
    p1 "test", "banane", "birne"
End Sub

Sub p1(ParamArray keys() As Variant)
    p2 keys
End Sub

Sub p2(ParamArray keys() As Variant)
    Dim key As Variant
    For Each key In keys
        Debug.Print key(0) '<- Give an Index here.
    Next key
End Sub

[lefty2cox]

I may be confused but I'm not passing an array to the function. The array is declared and populated in the function. The function then returns an array to a procedure.

I may be looking at this all wrong. Should I be using a public sub? Here's my code. The loop at the end involving "j" was just so I could see if it had the desired result....which is very close to what I'm looking for.

Function Gaps(curNum) As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim GapArray()
Dim strSQL As String
Dim GapCounter As Integer
Dim i As Integer
Dim j As Integer


GapCounter = 0
i = 1


strSQL = "select * from qrywinningnums"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
ReDim GapArray(1)
    
    Do While Not rs.EOF
           
        If rs!one = curNum Or rs!Two = curNum Or rs!three = curNum Or rs!four = curNum Or rs!five = curNum Then
        
              
              GapArray(i) = GapCounter
              
              GapCounter = 0
              i = i + 1
              ReDim Preserve GapArray(i)
              rs.MoveNext
              
        Else
              GapCounter = GapCounter + 1
              rs.MoveNext
        End If
        
    Loop
    
    
'this is the line that throws the error
'Gaps = GapArray
    
   
   
j = 1
For j = 1 To 100


Debug.Print GapArray(j)
Next j




End Function

[John_G]

You can have a function that returns an array. You can do something like this:

Function Extract_Keywords(ByVal strText As String) As String()

and then use something like this to assign the value to the function:

Extract_Keywords = arrWords

where arrWords is also an array declared with Dim arrWords() As String

[CJ_London]

@John_G - new one for me! after all these years, I'm still learning..

[lefty2cox]

You can have a function that returns an array. You can do something like this:

Function Extract_Keywords(ByVal strText As String) As String()

and then use something like this to assign the value to the function:

Extract_Keywords = arrWords

where arrWords is also an array declared with Dim arrWords() As String

Thanks John