private function that returns an array (1 Viewer)

Magic200777

New member
Local time
Today, 08:54
Joined
Jun 13, 2019
Messages
2
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

AWF VIP
Local time
Today, 05:54
Joined
Mar 9, 2014
Messages
5,423
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

I’m here to help
Staff member
Local time
Today, 06:54
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to the forum. Besides, I am not sure functions can return arrays.
 

Magic200777

New member
Local time
Today, 08:54
Joined
Jun 13, 2019
Messages
2
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 here to help
Staff member
Local time
Today, 06:54
Joined
Oct 29, 2018
Messages
21,357
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

AWF VIP
Local time
Today, 14:54
Joined
Oct 27, 2015
Messages
998
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!
Code:
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

I’m here to help
Staff member
Local time
Today, 06:54
Joined
Oct 29, 2018
Messages
21,357
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!
Code:
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

AWF VIP
Local time
Today, 05:54
Joined
Mar 9, 2014
Messages
5,423
Alternative is to global declare array object and it can be referenced from multiple procedures/modules. I have done this.
 

sonic8

AWF VIP
Local time
Today, 14:54
Joined
Oct 27, 2015
Messages
998
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.
 

Users who are viewing this thread

Top Bottom