private function that returns an array

[Magic200777]

I have a problem with a function that is suppose to return an array. I have defined array A() as Variant then populated it based on how many elements are needed. When I try to assign the first element with A(1) = "my text", I get index out of range error. I tried A(0) and get the same results. I used Option
Base 0 then Option Base 1 to no avail... What am I missing? Thanks!!!

private function Tasks(X as integer) as variant
dim A() as Variant

A(0) = "My text"

Tasks = A

end function

 

[June7]

You have not defined how many elements the array can have. How many will there be?
What is the X variable for? I tried using it to pass a number for the Dim statement but the Dim won't allow variable, must be constant. Can use it in ReDim statement.

Tasks = A triggers "type mismatch" error.

What exactly are you trying to accomplish?

 

[theDBguy]

Hi. Welcome to the forum. Besides, I am not sure functions can return arrays.

 

[Magic200777]

You have not defined how many elements the array can have. How many will there be?
What is the X variable for? I tried using it to pass a number for the Dim statement but the Dim won't allow variable, must be constant. Can use it in ReDim statement.

Tasks = A triggers "type mismatch" error.

What exactly are you trying to accomplish?

I'm trying to pass the function the customer number so the function can "collect" the record(s) in an array that the customers had ordered so the number of elements are unknown. I was hoping to have a variant array that the function can pass back... do I need to do this in a subroutine and have the array passed back instead of a function?

 

[theDBguy]

I'm trying to pass the function the customer number so the function can "collect" the record(s) in an array that the customers had ordered so the number of elements are unknown. I was hoping to have a variant array that the function can pass back... do I need to do this in a subroutine and have the array passed back instead of a function?

Hi. As I was saying earlier, I think functions can only return simple data types. I don't think it can return complex data types. What you could do is pass a recordset object to the function and let the function modify it. The calling routine can then use the recordset to work with the records found. Hope it helps...

 

[sonic8]

I have defined array A() as Variant then populated it based on how many elements are needed.

A() is an array without any element in it. You cannot "populate" it before resizing it first. Otherwise there is no "space" in it where you could put anything.
Use ReDim to resize an array at run time.

Hi. As I was saying earlier, I think functions can only return simple data types. I don't think it can return complex data types.

Of course they can!

Private Function Tasks(x As Integer) As Variant
    Dim A() As Variant
    ReDim A(0) As Variant   '<- this was missing in the original code!
    A(0) = "My text"
    Tasks = A
End Function

Public Sub testArr()
    Dim x As Variant
    x = Tasks(9999)
    Dim i As Long
    For i = LBound(x) To UBound(x)
        MsgBox x(i)
    Next
End Sub

 

[theDBguy]

A() is an array without any element in it. You cannot "populate" it before resizing it first. Otherwise there is no "space" in it where you could put anything.
Use ReDim to resize an array at run time.


Of course they can!

Private Function Tasks(x As Integer) As Variant
    Dim A() As Variant
    ReDim A(0) As Variant   '<- this was missing in the original code!
    A(0) = "My text"
    Tasks = A
End Function

Public Sub testArr()
    Dim x As Variant
    x = Tasks(9999)
    Dim i As Long
    For i = LBound(x) To UBound(x)
        MsgBox x(i)
    Next
End Sub

Hi Phil. Thanks for the correction. Do you have a list of what cannot be returned by a function, if any at all?

 

[June7]

Alternative is to global declare array object and it can be referenced from multiple procedures/modules. I have done this.

 

[sonic8]

Hi Phil. Thanks for the correction. Do you have a list of what cannot be returned by a function, if any at all?

Right now I cannot think of any type that can NOT be returned by a function. So, no list...

Of course, there are some limitations, like a Public function cannot return a Private type or you can only use functions returning primitive types in Access expressions. - These are no limitations of the function, but of the calling context.

Alternative is to global declare array object and it can be referenced from multiple procedures/modules. I have done this.

Sure, this would "work". However, the fact that any global variable can be referenced (r/w) from multiple procedures is a strong point against using it.