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

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!!


"(E-Mail Removed)" 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
>

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