Return array from worksheet function

[Steve Lloyd]

Hi there,

I am trying to return an array of dates and prices from a worksheet function
and display them starting at the selected item.

This is the function I am using to test. I've seen this done by people such
as Bloomberg but cannot work how to do it at all. Any help would be much
appreciated.

Thanks,

Steve

Public Function ReturnArray() As Variant

Dim tempArray() As String
ReDim tempArray(1, 1)

tempArray(0, 1) = "Price 1"
tempArray(0, 1) = "17/07/2006"
tempArray(1, 0) = "Price 2"
tempArray(1, 1) = "14/07/2006"

ReturnArray = tempArray

End Function

[Chris Marlow]

Steve,

Looks Ok to me. Are you using CTRL-SHIFT-ENTER to commit?

Additionally maybe you want;

tempArray(0, 0) = "Price 1"

- but I suspect that is just a typo.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Steve Lloyd" wrote:

> Hi there,
>
> I am trying to return an array of dates and prices from a worksheet function
> and display them starting at the selected item.
>
> This is the function I am using to test. I've seen this done by people such
> as Bloomberg but cannot work how to do it at all. Any help would be much
> appreciated.
>
> Thanks,
>
> Steve
>
> Public Function ReturnArray() As Variant
>
> Dim tempArray() As String
> ReDim tempArray(1, 1)
>
> tempArray(0, 1) = "Price 1"
> tempArray(0, 1) = "17/07/2006"
> tempArray(1, 0) = "Price 2"
> tempArray(1, 1) = "14/07/2006"
>
> ReturnArray = tempArray
>
> End Function
>
>
>

[Steve Lloyd]

Hi Chris,

Thanks very much for your response.

I'm really struggling here, I enter the function call in a cell and then
press Ctrl-Shft-Ent, this enters the value in the selected cell but does not
update the additional cells that are part of the array, I presume I am
missing something here, could you help please.

In addition, I want to add the formula from a command button/combo box on a
toolbar as I suspect the users will not be able to understand the
Crtl-Shft-Entr thing (they are traders!) Can you suggest a way of doing
this?

Thanks again,

Steve

"Chris Marlow" <[email protected]> wrote in message
news:[email protected]...
> Steve,
>
> Looks Ok to me. Are you using CTRL-SHIFT-ENTER to commit?
>
> Additionally maybe you want;
>
> tempArray(0, 0) = "Price 1"
>
> - but I suspect that is just a typo.
>
> Regards,
>
> Chris.
>
> --
> Chris Marlow
> MCSD.NET, Microsoft Office XP Master
>
>
> "Steve Lloyd" wrote:
>
>> Hi there,
>>
>> I am trying to return an array of dates and prices from a worksheet
>> function
>> and display them starting at the selected item.
>>
>> This is the function I am using to test. I've seen this done by people
>> such
>> as Bloomberg but cannot work how to do it at all. Any help would be much
>> appreciated.
>>
>> Thanks,
>>
>> Steve
>>
>> Public Function ReturnArray() As Variant
>>
>> Dim tempArray() As String
>> ReDim tempArray(1, 1)
>>
>> tempArray(0, 1) = "Price 1"
>> tempArray(0, 1) = "17/07/2006"
>> tempArray(1, 0) = "Price 2"
>> tempArray(1, 1) = "14/07/2006"
>>
>> ReturnArray = tempArray
>>
>> End Function
>>
>>
>>

[MrShorty]

Are you entering the function in only one cell, or are you selecting a 2x2 range before entering the function? As with Excel's built in functions that return an array, you have to select the entire output range when entering the function to get the entire output array returned.

[Steve Lloyd]

OK, i am there, fantastic, thankyou...



"MrShorty" <[email protected]> wrote in
message news:[email protected]...
>
> Are you entering the function in only one cell, or are you selecting a
> 2x2 range before entering the function? As with Excel's built in
> functions that return an array, you have to select the entire output
> range when entering the function to get the entire output array
> returned.
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile:
> http://www.excelforum.com/member.php...o&userid=22181
> View this thread: http://www.excelforum.com/showthread...hreadid=562104
>