Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array results not returned | Excel Worksheet Functions | |||
Accessing specific items in a range | Excel Programming | |||
PivotTable Does not refresh individual items | Excel Programming | |||
How Do I Reference Individual Dropdown Menu Items in VBA? | Excel Programming | |||
Preformatted array returned by custom function | Excel Programming |