Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-20-2019, 08:42 PM   #1
Magic200777
Newly Registered User
 
Join Date: Jun 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Magic200777 is on a distinguished road
private function that returns an array

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

Magic200777 is offline   Reply With Quote
Old 06-20-2019, 09:13 PM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,959
Thanks: 0
Thanked 463 Times in 459 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: private function that returns an array

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?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 06-21-2019, 02:34 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,063
Thanks: 36
Thanked 729 Times in 712 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: private function that returns an array

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

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-21-2019, 03:37 AM   #4
Magic200777
Newly Registered User
 
Join Date: Jun 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Magic200777 is on a distinguished road
Re: private function that returns an array

Quote:
Originally Posted by June7 View Post
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?
Magic200777 is offline   Reply With Quote
Old 06-21-2019, 07:09 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,063
Thanks: 36
Thanked 729 Times in 712 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: private function that returns an array

Quote:
Originally Posted by Magic200777 View Post
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-21-2019, 12:33 PM   #6
sonic8
Newly Registered User
 
Join Date: Oct 2015
Posts: 213
Thanks: 34
Thanked 62 Times in 59 Posts
sonic8 is on a distinguished road
Re: private function that returns an array

Quote:
Originally Posted by Magic200777 View Post
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.

Quote:
Originally Posted by theDBguy View Post
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
__________________
New video:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
The Following User Says Thank You to sonic8 For This Useful Post:
theDBguy (06-21-2019)
Old 06-21-2019, 12:46 PM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,063
Thanks: 36
Thanked 729 Times in 712 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: private function that returns an array

Quote:
Originally Posted by sonic8 View Post
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?

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-21-2019, 01:04 PM   #8
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,959
Thanks: 0
Thanked 463 Times in 459 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: private function that returns an array

Alternative is to global declare array object and it can be referenced from multiple procedures/modules. I have done this.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 06-21-2019, 11:46 PM   #9
sonic8
Newly Registered User
 
Join Date: Oct 2015
Posts: 213
Thanks: 34
Thanked 62 Times in 59 Posts
sonic8 is on a distinguished road
Re: private function that returns an array

Quote:
Originally Posted by theDBguy View Post
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.



Quote:
Originally Posted by June7 View Post
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.

__________________
New video:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
Reply

Tags
access vba array

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Alternative to Dlookup which returns an Array of values. jxaxmxixn Modules & VBA 10 01-14-2014 06:09 AM
Can't Find Private Function LarryB Forms 5 06-22-2012 07:33 PM
Exit Public Function without returning to Private Function LB79 Modules & VBA 9 06-04-2010 01:29 AM
function vs private sub 1jet Modules & VBA 2 09-29-2008 06:27 AM
Private Function help oxicottin Modules & VBA 15 12-18-2007 06:18 PM




All times are GMT -8. The time now is 02:38 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World