Accessing individual items in an array returned by a function
 

In VBA, I create the following function that returns an array of
length 3.

Public Function RegionSizeStyle() As Variant
Dim results(2) As Variant
Region = "JP"
Size = "Small"
Style = "Core"

results(0) = Region
results(1) = Size
results(2) = Style

RegionSizeStyle = results

End Function

In Excel, if I select three cells in the same row, enter
=RegionSizeStyle() into the first one and then hit CTRL-SHIFT-ENTER,
the cells are filled with RegionSizeStyle(0), RegionSizeStyle(1), and
RegionSizeStyle(2) respectively, just as I would expect.

However, if I select a SINGLE cell and then try to extract a single
item form the array that is returned by typing =RegionSizeStyle()(1),
I get a #REF regardless of whether I finish by entry by hitting ENTER
or CTRL-SHIFT-ENTER. What is the problem? Is it just my syntax or is
there a deeper problem?

Thanks in advance for your assistance.

Thomas Philips

Accessing individual items in an array returned by a function
 

Hi,

You need to type just RegionSizeStyle() to fetch the first value. I think
you are calling the function in a wrong way.

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


" wrote:

In VBA, I create the following function that returns an array of
length 3.

Public Function RegionSizeStyle() As Variant
Dim results(2) As Variant
Region = "JP"
Size = "Small"
Style = "Core"

results(0) = Region
results(1) = Size
results(2) = Style

RegionSizeStyle = results

End Function

In Excel, if I select three cells in the same row, enter
=RegionSizeStyle() into the first one and then hit CTRL-SHIFT-ENTER,
the cells are filled with RegionSizeStyle(0), RegionSizeStyle(1), and
RegionSizeStyle(2) respectively, just as I would expect.

However, if I select a SINGLE cell and then try to extract a single
item form the array that is returned by typing =RegionSizeStyle()(1),
I get a #REF regardless of whether I finish by entry by hitting ENTER
or CTRL-SHIFT-ENTER. What is the problem? Is it just my syntax or is
there a deeper problem?

Thanks in advance for your assistance.

Thomas Philips

Accessing individual items in an array returned by a function
 

Got it by modifying the keywords in my Google search - The correct
syntax is
=Index(RegionSizeStyle(), i)
for i=1, 2 or 3.