How to declare function that returns array of dynamic size

Vlip

New Member
Joined
Mar 10, 2002
Messages
49
Hi,

I searched the documentation I have and this web site but, I still cannot figure out how to declare a function that returns an array (in my case a array of strings) of an un-predetermined size. Currently, I use "Public Dim myArray() As String" and then ReDim the array in my Sub. This works but I would much rather declare the Sub as a Function that returns the array of strings. Does anyone know how to do this? Thanks in advance for the help.

Regards,
Vlip
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi Vlip,

Simply declare the function as Variant, then assign the array to the function value. A function cannot return an array. However, a function CAN return a variant that CONTAINS an array. I realize that this is a vague distinction, but it workds. For example:

Function StrArray( args.. ) As Variant

Dim myArray() As String

ReDim myArray(50)

'code that sets values into myArray and/or
'ReDims myArray

StrArray = myArray

End Function
 
Upvote 0
Thank you very much! I knew I could do what you suggested. I guess the answer I was looking for is "a function cannot return an array". It seems to me a limitation of VB.

Vlip
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top