Not for the Faith of Heart - Macro Arrays

Sorry about the silly subject title, I'm trying to shrug my frustration with
good genuine quacky humor With that said, I have a difficult hurdle to
overcome...

I have a macro that returns, not one, but two arrays! Is this doable? The
macro returns an answer that contains two arrays, which reside in different
areas within the worksheet (i.e. the arrays are not adjacent to one another).

Returning one array is not the problem--that's been done. But returning
two, in different sections of the same worksheet--that's where the money's at!

I've tried just about everything know to mankind... ok, maybe not everything
Here's a list of my futile attempts:
-Wrote to different cells from within the macro function.
-Disabled Application.EnableEvents AND then wrote to different cells within
the macro function.
-Selected different areas of the worksheet before clicking Ctrl+****+Return.
-Returned an array of variants (instead of a single array variant).
-Used CalculateEvent to write to different cells.
-Wrote to different cells using DDE.
-Tried sending excel window message to change cells.
-Tried hacking excel's COM Object Model.

Nothing, zippo, nada worked! I sure would enjoy adding more failures to
my list so if you have any great ideas send them my way

At any rate, keep a good spirit and thanks in advance.

-Fabricio
[email protected]

Are you sure you don't mean "faint of heart"???


On Mon, 21 Feb 2005 11:47:03 -0800, "Fabricio"
<[email protected]> wrote:

>Sorry about the silly subject title, I'm trying to shrug my frustration with
>good genuine quacky humor With that said, I have a difficult hurdle to
>overcome...
>
>I have a macro that returns, not one, but two arrays! Is this doable? The
>macro returns an answer that contains two arrays, which reside in different
>areas within the worksheet (i.e. the arrays are not adjacent to one another).
>
>Returning one array is not the problem--that's been done. But returning
>two, in different sections of the same worksheet--that's where the money's
at!
>
>I've tried just about everything know to mankind... ok, maybe not everything
> Here's a list of my futile attempts:
>-Wrote to different cells from within the macro function.
>-Disabled Application.EnableEvents AND then wrote to different cells within
>the macro function.
>-Selected different areas of the worksheet before clicking Ctrl+****+Return.
>-Returned an array of variants (instead of a single array variant).
>-Used CalculateEvent to write to different cells.
>-Wrote to different cells using DDE.
>-Tried sending excel window message to change cells.
>-Tried hacking excel's COM Object Model.
>
>Nothing, zippo, nada worked! I sure would enjoy adding more failures to
>my list so if you have any great ideas send them my way
>
>At any rate, keep a good spirit and thanks in advance.
>
>-Fabricio
>[email protected]

Is this a Function or Sub? Can you post the code you have now? What happens
with each of the failed attempts that you list?

On Mon, 21 Feb 2005 11:47:03 -0800, "Fabricio"
<[email protected]> wrote:

>Sorry about the silly subject title, I'm trying to shrug my frustration with
>good genuine quacky humor With that said, I have a difficult hurdle to
>overcome...
>
>I have a macro that returns, not one, but two arrays! Is this doable? The
>macro returns an answer that contains two arrays, which reside in different
>areas within the worksheet (i.e. the arrays are not adjacent to one another).
>
>Returning one array is not the problem--that's been done. But returning
>two, in different sections of the same worksheet--that's where the money's
at!
>
>I've tried just about everything know to mankind... ok, maybe not everything
> Here's a list of my futile attempts:
>-Wrote to different cells from within the macro function.
>-Disabled Application.EnableEvents AND then wrote to different cells within
>the macro function.
>-Selected different areas of the worksheet before clicking Ctrl+****+Return.
>-Returned an array of variants (instead of a single array variant).
>-Used CalculateEvent to write to different cells.
>-Wrote to different cells using DDE.
>-Tried sending excel window message to change cells.
>-Tried hacking excel's COM Object Model.
>
>Nothing, zippo, nada worked! I sure would enjoy adding more failures to
>my list so if you have any great ideas send them my way
>
>At any rate, keep a good spirit and thanks in advance.
>
>-Fabricio
>[email protected]

Array formulas are not going to work in separate blocks.



--
Regards,
Tom Ogilvy


"Fabricio" <[email protected]> wrote in message
news:[email protected]...
> Sorry about the silly subject title, I'm trying to shrug my frustration
with
> good genuine quacky humor With that said, I have a difficult hurdle to
> overcome...
>
> I have a macro that returns, not one, but two arrays! Is this doable?
The
> macro returns an answer that contains two arrays, which reside in
different
> areas within the worksheet (i.e. the arrays are not adjacent to one
another).
>
> Returning one array is not the problem--that's been done. But returning
> two, in different sections of the same worksheet--that's where the money's
at!
>
> I've tried just about everything know to mankind... ok, maybe not
everything
> Here's a list of my futile attempts:
> -Wrote to different cells from within the macro function.
> -Disabled Application.EnableEvents AND then wrote to different cells
within
> the macro function.
> -Selected different areas of the worksheet before clicking
Ctrl+****+Return.
> -Returned an array of variants (instead of a single array variant).
> -Used CalculateEvent to write to different cells.
> -Wrote to different cells using DDE.
> -Tried sending excel window message to change cells.
> -Tried hacking excel's COM Object Model.
>
> Nothing, zippo, nada worked! I sure would enjoy adding more failures
to
> my list so if you have any great ideas send them my way
>
> At any rate, keep a good spirit and thanks in advance.
>
> -Fabricio
> [email protected]

....whoops, big waste of time checking my spelling and grammar... I screwed
things up from the beginning... yes, "Faint of Heart"

The code is big and complicated, so I'm not gonna post it, but here's a
representative example:

Function GimmeTwoArrays() As Variant()
Dim ans(1 To 2) As Variant
Dim array1(1 To 3, 1 To 3) As Variant
Dim array2(1 To 2, 1 To 2) As Variant

ans(1) = array1
ans(2) = array2

GimmeTwoArrays = ans
End Function

As for my failed attempts, here you go:
-Wrote to different cells from within the macro function.
+Excel doesn't allow this. You can't write to a cell from within a macro
function.

-Disabled Application.EnableEvents AND then wrote to different cells within
the macro function.
+Same as above can't write to cells. Also, explicitly writing to the source
cell(s) will create bottomless recursion, or as excel likes to put it
"circular reference".

-Selected different areas of the worksheet before clicking Ctrl+****+Return.
+Excel ignores my second area and only uses the first area.

-Returned an array of variants (instead of a single array variant).
+Excel doesn't like this one bit. It returns the infamous "#VALUE!" error.

-Used CalculateEvent to write to different cells.
+Excel gets into "circular reference" again.

-Wrote to different cells using DDE.
+Channel locking

-Tried sending excel window message to change cells.
+In the works, but not locking good so far.

-Tried hacking excel's COM Object Model.
+This was a desperation move, didn't even scratch the surface.

Thanks again,
-Fabricio
[email protected]


"Myrna Larson" wrote:

> Is this a Function or Sub? Can you post the code you have now? What happens
> with each of the failed attempts that you list?
>
> On Mon, 21 Feb 2005 11:47:03 -0800, "Fabricio"
> <[email protected]> wrote:
>
> >Sorry about the silly subject title, I'm trying to shrug my frustration with
> >good genuine quacky humor With that said, I have a difficult hurdle to
> >overcome...
> >
> >I have a macro that returns, not one, but two arrays! Is this doable? The
> >macro returns an answer that contains two arrays, which reside in different
> >areas within the worksheet (i.e. the arrays are not adjacent to one another).
> >
> >Returning one array is not the problem--that's been done. But returning
> >two, in different sections of the same worksheet--that's where the money's
> at!
> >
> >I've tried just about everything know to mankind... ok, maybe not everything
> > Here's a list of my futile attempts:
> >-Wrote to different cells from within the macro function.
> >-Disabled Application.EnableEvents AND then wrote to different cells within
> >the macro function.
> >-Selected different areas of the worksheet before clicking Ctrl+****+Return.
> >-Returned an array of variants (instead of a single array variant).
> >-Used CalculateEvent to write to different cells.
> >-Wrote to different cells using DDE.
> >-Tried sending excel window message to change cells.
> >-Tried hacking excel's COM Object Model.
> >
> >Nothing, zippo, nada worked! I sure would enjoy adding more failures to
> >my list so if you have any great ideas send them my way
> >
> >At any rate, keep a good spirit and thanks in advance.
> >
> >-Fabricio
> >[email protected]
>
>

....it's the beauty of software development--the challenge. There's always a
way... and I will eventually find it.

-Fabricio
[email protected]

"Tom Ogilvy" wrote:

> Array formulas are not going to work in separate blocks.
>
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Fabricio" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry about the silly subject title, I'm trying to shrug my frustration
> with
> > good genuine quacky humor With that said, I have a difficult hurdle to
> > overcome...
> >
> > I have a macro that returns, not one, but two arrays! Is this doable?
> The
> > macro returns an answer that contains two arrays, which reside in
> different
> > areas within the worksheet (i.e. the arrays are not adjacent to one
> another).
> >
> > Returning one array is not the problem--that's been done. But returning
> > two, in different sections of the same worksheet--that's where the money's
> at!
> >
> > I've tried just about everything know to mankind... ok, maybe not
> everything
> > Here's a list of my futile attempts:
> > -Wrote to different cells from within the macro function.
> > -Disabled Application.EnableEvents AND then wrote to different cells
> within
> > the macro function.
> > -Selected different areas of the worksheet before clicking
> Ctrl+****+Return.
> > -Returned an array of variants (instead of a single array variant).
> > -Used CalculateEvent to write to different cells.
> > -Wrote to different cells using DDE.
> > -Tried sending excel window message to change cells.
> > -Tried hacking excel's COM Object Model.
> >
> > Nothing, zippo, nada worked! I sure would enjoy adding more failures
> to
> > my list so if you have any great ideas send them my way
> >
> > At any rate, keep a good spirit and thanks in advance.
> >
> > -Fabricio
> > [email protected]
>
>
>

I never use worksheet arrays, but if you could explain what you are trying
to do
I am sure there is a solution.

RBS


"Fabricio" <[email protected]> wrote in message
news:[email protected]...
> Sorry about the silly subject title, I'm trying to shrug my frustration
> with
> good genuine quacky humor With that said, I have a difficult hurdle to
> overcome...
>
> I have a macro that returns, not one, but two arrays! Is this doable?
> The
> macro returns an answer that contains two arrays, which reside in
> different
> areas within the worksheet (i.e. the arrays are not adjacent to one
> another).
>
> Returning one array is not the problem--that's been done. But returning
> two, in different sections of the same worksheet--that's where the money's
> at!
>
> I've tried just about everything know to mankind... ok, maybe not
> everything
> Here's a list of my futile attempts:
> -Wrote to different cells from within the macro function.
> -Disabled Application.EnableEvents AND then wrote to different cells
> within
> the macro function.
> -Selected different areas of the worksheet before clicking
> Ctrl+****+Return.
> -Returned an array of variants (instead of a single array variant).
> -Used CalculateEvent to write to different cells.
> -Wrote to different cells using DDE.
> -Tried sending excel window message to change cells.
> -Tried hacking excel's COM Object Model.
>
> Nothing, zippo, nada worked! I sure would enjoy adding more failures
> to
> my list so if you have any great ideas send them my way
>
> At any rate, keep a good spirit and thanks in advance.
>
> -Fabricio
> [email protected]

Not if you are talking about array formulas - returning and array from a UDF
(or in your case, returning two).

but please come back and report your success. (not one huge array with two
sections populated - that wouldn't be two arrays).

--
Regards,
Tom Ogilvy

"Fabricio" <[email protected]> wrote in message
news:[email protected]...
> ...it's the beauty of software development--the challenge. There's always
a
> way... and I will eventually find it.
>
> -Fabricio
> [email protected]
>
> "Tom Ogilvy" wrote:
>
> > Array formulas are not going to work in separate blocks.
> >
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Fabricio" <[email protected]> wrote in message
> > news:[email protected]...
> > > Sorry about the silly subject title, I'm trying to shrug my
frustration
> > with
> > > good genuine quacky humor With that said, I have a difficult hurdle
to
> > > overcome...
> > >
> > > I have a macro that returns, not one, but two arrays! Is this doable?
> > The
> > > macro returns an answer that contains two arrays, which reside in
> > different
> > > areas within the worksheet (i.e. the arrays are not adjacent to one
> > another).
> > >
> > > Returning one array is not the problem--that's been done. But
returning
> > > two, in different sections of the same worksheet--that's where the
money's
> > at!
> > >
> > > I've tried just about everything know to mankind... ok, maybe not
> > everything
> > > Here's a list of my futile attempts:
> > > -Wrote to different cells from within the macro function.
> > > -Disabled Application.EnableEvents AND then wrote to different cells
> > within
> > > the macro function.
> > > -Selected different areas of the worksheet before clicking
> > Ctrl+****+Return.
> > > -Returned an array of variants (instead of a single array variant).
> > > -Used CalculateEvent to write to different cells.
> > > -Wrote to different cells using DDE.
> > > -Tried sending excel window message to change cells.
> > > -Tried hacking excel's COM Object Model.
> > >
> > > Nothing, zippo, nada worked! I sure would enjoy adding more
failures
> > to
> > > my list so if you have any great ideas send them my way
> > >
> > > At any rate, keep a good spirit and thanks in advance.
> > >
> > > -Fabricio
> > > [email protected]
> >
> >
> >

Sorry, but there's absolutely NO WAY that this can work. Array formulas return
a single array, to a single rectangular block of contiguous cells.

On Mon, 21 Feb 2005 14:11:01 -0800, "Fabricio"
<[email protected]> wrote:

>...whoops, big waste of time checking my spelling and grammar... I screwed
>things up from the beginning... yes, "Faint of Heart"
>
>The code is big and complicated, so I'm not gonna post it, but here's a
>representative example:
>
>Function GimmeTwoArrays() As Variant()
> Dim ans(1 To 2) As Variant
> Dim array1(1 To 3, 1 To 3) As Variant
> Dim array2(1 To 2, 1 To 2) As Variant
>
> ans(1) = array1
> ans(2) = array2
>
> GimmeTwoArrays = ans
>End Function
>
>As for my failed attempts, here you go:
>-Wrote to different cells from within the macro function.
>+Excel doesn't allow this. You can't write to a cell from within a macro
>function.
>
>-Disabled Application.EnableEvents AND then wrote to different cells within
>the macro function.
>+Same as above can't write to cells. Also, explicitly writing to the source
>cell(s) will create bottomless recursion, or as excel likes to put it
>"circular reference".
>
>-Selected different areas of the worksheet before clicking Ctrl+****+Return.
>+Excel ignores my second area and only uses the first area.
>
>-Returned an array of variants (instead of a single array variant).
>+Excel doesn't like this one bit. It returns the infamous "#VALUE!" error.
>
>-Used CalculateEvent to write to different cells.
>+Excel gets into "circular reference" again.
>
>-Wrote to different cells using DDE.
>+Channel locking
>
>-Tried sending excel window message to change cells.
>+In the works, but not locking good so far.
>
>-Tried hacking excel's COM Object Model.
>+This was a desperation move, didn't even scratch the surface.
>
>Thanks again,
>-Fabricio
>[email protected]
>
>
>"Myrna Larson" wrote:
>
>> Is this a Function or Sub? Can you post the code you have now? What happens
>> with each of the failed attempts that you list?
>>
>> On Mon, 21 Feb 2005 11:47:03 -0800, "Fabricio"
>> <[email protected]> wrote:
>>
>> >Sorry about the silly subject title, I'm trying to shrug my frustration
with
>> >good genuine quacky humor With that said, I have a difficult hurdle to
>> >overcome...
>> >
>> >I have a macro that returns, not one, but two arrays! Is this doable?
The
>> >macro returns an answer that contains two arrays, which reside in
different
>> >areas within the worksheet (i.e. the arrays are not adjacent to one
another).
>> >
>> >Returning one array is not the problem--that's been done. But returning
>> >two, in different sections of the same worksheet--that's where the money's
>> at!
>> >
>> >I've tried just about everything know to mankind... ok, maybe not
everything
>> > Here's a list of my futile attempts:
>> >-Wrote to different cells from within the macro function.
>> >-Disabled Application.EnableEvents AND then wrote to different cells
within
>> >the macro function.
>> >-Selected different areas of the worksheet before clicking
Ctrl+****+Return.
>> >-Returned an array of variants (instead of a single array variant).
>> >-Used CalculateEvent to write to different cells.
>> >-Wrote to different cells using DDE.
>> >-Tried sending excel window message to change cells.
>> >-Tried hacking excel's COM Object Model.
>> >
>> >Nothing, zippo, nada worked! I sure would enjoy adding more failures
to
>> >my list so if you have any great ideas send them my way
>> >
>> >At any rate, keep a good spirit and thanks in advance.
>> >
>> >-Fabricio
>> >[email protected]
>>
>>

Have at it, RB, and please post your solution for the edification of Tom and
me <g>.

On Mon, 21 Feb 2005 22:22:26 -0000, "RB Smissaert"
<[email protected]> wrote:

>I never use worksheet arrays, but if you could explain what you are trying
>to do
>I am sure there is a solution.
>
>RBS
>
>
>"Fabricio" <[email protected]> wrote in message
>news:[email protected]...
>> Sorry about the silly subject title, I'm trying to shrug my frustration
>> with
>> good genuine quacky humor With that said, I have a difficult hurdle to
>> overcome...
>>
>> I have a macro that returns, not one, but two arrays! Is this doable?
>> The
>> macro returns an answer that contains two arrays, which reside in
>> different
>> areas within the worksheet (i.e. the arrays are not adjacent to one
>> another).
>>
>> Returning one array is not the problem--that's been done. But returning
>> two, in different sections of the same worksheet--that's where the money's
>> at!
>>
>> I've tried just about everything know to mankind... ok, maybe not
>> everything
>> Here's a list of my futile attempts:
>> -Wrote to different cells from within the macro function.
>> -Disabled Application.EnableEvents AND then wrote to different cells
>> within
>> the macro function.
>> -Selected different areas of the worksheet before clicking
>> Ctrl+****+Return.
>> -Returned an array of variants (instead of a single array variant).
>> -Used CalculateEvent to write to different cells.
>> -Wrote to different cells using DDE.
>> -Tried sending excel window message to change cells.
>> -Tried hacking excel's COM Object Model.
>>
>> Nothing, zippo, nada worked! I sure would enjoy adding more failures
>> to
>> my list so if you have any great ideas send them my way
>>
>> At any rate, keep a good spirit and thanks in advance.
>>
>> -Fabricio
>> [email protected]

in your function, add a "switch" parameter which will control which of
the two arrays is returned.

eg:

Function GetArray(RngIn as range, ArrayNum as integer) as variant

'.....calculate the arrays

if ArrayNum=1 then
'return array1
elseif ArrayNum=2 then
'return array2
else
'handle error (bad parameter)
end if

end function


then to get your first array:
=GetArray([range],1) 'entered as array formula
=GetArray([range],2) 'entered as array formula

Tim.



"Fabricio" <> wrote in message
news:[email protected]...
> Sorry about the silly subject title, I'm trying to shrug my
> frustration with
> good genuine quacky humor With that said, I have a difficult
> hurdle to
> overcome...
>
> I have a macro that returns, not one, but two arrays! Is this
> doable? The
> macro returns an answer that contains two arrays, which reside in
> different
> areas within the worksheet (i.e. the arrays are not adjacent to one
> another).
>
> Returning one array is not the problem--that's been done. But
> returning
> two, in different sections of the same worksheet--that's where the
> money's at!
>
> I've tried just about everything know to mankind... ok, maybe not
> everything
> Here's a list of my futile attempts:
> -Wrote to different cells from within the macro function.
> -Disabled Application.EnableEvents AND then wrote to different cells
> within
> the macro function.
> -Selected different areas of the worksheet before clicking
> Ctrl+****+Return.
> -Returned an array of variants (instead of a single array variant).
> -Used CalculateEvent to write to different cells.
> -Wrote to different cells using DDE.
> -Tried sending excel window message to change cells.
> -Tried hacking excel's COM Object Model.
>
> Nothing, zippo, nada worked! I sure would enjoy adding more
> failures to
> my list so if you have any great ideas send them my way
>
> At any rate, keep a good spirit and thanks in advance.
>
> -Fabricio
> [email protected]

Trouble is, he says he wants both, and he's bound and determined to get that
result. I'm not holding my breath.

On Mon, 21 Feb 2005 19:46:58 -0800, "Tim Williams" <saxifrax@pacbell*dot*net>
wrote:

>in your function, add a "switch" parameter which will control which of
>the two arrays is returned.
>
>eg:
>
>Function GetArray(RngIn as range, ArrayNum as integer) as variant
>
>'.....calculate the arrays
>
>if ArrayNum=1 then
> 'return array1
>elseif ArrayNum=2 then
> 'return array2
>else
> 'handle error (bad parameter)
>end if
>
>end function
>
>
>then to get your first array:
>=GetArray([range],1) 'entered as array formula
>=GetArray([range],2) 'entered as array formula
>
>Tim.
>
>
>
>"Fabricio" <> wrote in message
>news:[email protected]...
>> Sorry about the silly subject title, I'm trying to shrug my
>> frustration with
>> good genuine quacky humor With that said, I have a difficult
>> hurdle to
>> overcome...
>>
>> I have a macro that returns, not one, but two arrays! Is this
>> doable? The
>> macro returns an answer that contains two arrays, which reside in
>> different
>> areas within the worksheet (i.e. the arrays are not adjacent to one
>> another).
>>
>> Returning one array is not the problem--that's been done. But
>> returning
>> two, in different sections of the same worksheet--that's where the
>> money's at!
>>
>> I've tried just about everything know to mankind... ok, maybe not
>> everything
>> Here's a list of my futile attempts:
>> -Wrote to different cells from within the macro function.
>> -Disabled Application.EnableEvents AND then wrote to different cells
>> within
>> the macro function.
>> -Selected different areas of the worksheet before clicking
>> Ctrl+****+Return.
>> -Returned an array of variants (instead of a single array variant).
>> -Used CalculateEvent to write to different cells.
>> -Wrote to different cells using DDE.
>> -Tried sending excel window message to change cells.
>> -Tried hacking excel's COM Object Model.
>>
>> Nothing, zippo, nada worked! I sure would enjoy adding more
>> failures to
>> my list so if you have any great ideas send them my way
>>
>> At any rate, keep a good spirit and thanks in advance.
>>
>> -Fabricio
>> [email protected]
>

Tim,

Thanks for the reply. I like your suggestion. It's not what I had in mine,
but if I don't figure out a way to simultaneously return two arrays (in two
different areas) in a single macro call, then this just might be the kicker.

Thanks again for taking the time

-Fabricio
[email protected]

"Tim Williams" wrote:

> in your function, add a "switch" parameter which will control which of
> the two arrays is returned.
>
> eg:
>
> Function GetArray(RngIn as range, ArrayNum as integer) as variant
>
> '.....calculate the arrays
>
> if ArrayNum=1 then
> 'return array1
> elseif ArrayNum=2 then
> 'return array2
> else
> 'handle error (bad parameter)
> end if
>
> end function
>
>
> then to get your first array:
> =GetArray([range],1) 'entered as array formula
> =GetArray([range],2) 'entered as array formula
>
> Tim.
>
>
>
> "Fabricio" <> wrote in message
> news:[email protected]...
> > Sorry about the silly subject title, I'm trying to shrug my
> > frustration with
> > good genuine quacky humor With that said, I have a difficult
> > hurdle to
> > overcome...
> >
> > I have a macro that returns, not one, but two arrays! Is this
> > doable? The
> > macro returns an answer that contains two arrays, which reside in
> > different
> > areas within the worksheet (i.e. the arrays are not adjacent to one
> > another).
> >
> > Returning one array is not the problem--that's been done. But
> > returning
> > two, in different sections of the same worksheet--that's where the
> > money's at!
> >
> > I've tried just about everything know to mankind... ok, maybe not
> > everything
> > Here's a list of my futile attempts:
> > -Wrote to different cells from within the macro function.
> > -Disabled Application.EnableEvents AND then wrote to different cells
> > within
> > the macro function.
> > -Selected different areas of the worksheet before clicking
> > Ctrl+****+Return.
> > -Returned an array of variants (instead of a single array variant).
> > -Used CalculateEvent to write to different cells.
> > -Wrote to different cells using DDE.
> > -Tried sending excel window message to change cells.
> > -Tried hacking excel's COM Object Model.
> >
> > Nothing, zippo, nada worked! I sure would enjoy adding more
> > failures to
> > my list so if you have any great ideas send them my way
> >
> > At any rate, keep a good spirit and thanks in advance.
> >
> > -Fabricio
> > [email protected]
>
>
>

Why not use a Sub rather than a function?

a Sub has no problem about returning data to two different ranges.
You could call it from the calculation event (have to switch off events
within the sub to prevent an infinite loop, and call .Calculate if you have
dependencies downstream of the the modified ranges)

--
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Fabricio" <[email protected]> wrote in message
news:[email protected]...
> Sorry about the silly subject title, I'm trying to shrug my frustration
> with
> good genuine quacky humor With that said, I have a difficult hurdle to
> overcome...
>
> I have a macro that returns, not one, but two arrays! Is this doable?
> The
> macro returns an answer that contains two arrays, which reside in
> different
> areas within the worksheet (i.e. the arrays are not adjacent to one
> another).
>
> Returning one array is not the problem--that's been done. But returning
> two, in different sections of the same worksheet--that's where the money's
> at!
>
> I've tried just about everything know to mankind... ok, maybe not
> everything
> Here's a list of my futile attempts:
> -Wrote to different cells from within the macro function.
> -Disabled Application.EnableEvents AND then wrote to different cells
> within
> the macro function.
> -Selected different areas of the worksheet before clicking
> Ctrl+****+Return.
> -Returned an array of variants (instead of a single array variant).
> -Used CalculateEvent to write to different cells.
> -Wrote to different cells using DDE.
> -Tried sending excel window message to change cells.
> -Tried hacking excel's COM Object Model.
>
> Nothing, zippo, nada worked! I sure would enjoy adding more failures
> to
> my list so if you have any great ideas send them my way
>
> At any rate, keep a good spirit and thanks in advance.
>
> -Fabricio
> [email protected]

The problem with doing it Tim's way is possibly speed. If, say, each array
contains 100 numbers, and you must calculate all 200 each time, then for each
area you calculate 200 numbers and throw 100 away. To populate both areas, you
calculate the 200 numbers twice, which takes twice as long as calculating them
just once.

Again, you CAN'T do this with a function. You could with a Sub.


On Mon, 21 Feb 2005 20:13:02 -0800, "Fabricio"
<[email protected]> wrote:

>Tim,
>
>Thanks for the reply. I like your suggestion. It's not what I had in mine,
>but if I don't figure out a way to simultaneously return two arrays (in two
>different areas) in a single macro call, then this just might be the kicker.
>
>Thanks again for taking the time
>
>-Fabricio
>[email protected]
>
>"Tim Williams" wrote:
>
>> in your function, add a "switch" parameter which will control which of
>> the two arrays is returned.
>>
>> eg:
>>
>> Function GetArray(RngIn as range, ArrayNum as integer) as variant
>>
>> '.....calculate the arrays
>>
>> if ArrayNum=1 then
>> 'return array1
>> elseif ArrayNum=2 then
>> 'return array2
>> else
>> 'handle error (bad parameter)
>> end if
>>
>> end function
>>
>>
>> then to get your first array:
>> =GetArray([range],1) 'entered as array formula
>> =GetArray([range],2) 'entered as array formula
>>
>> Tim.
>>
>>
>>
>> "Fabricio" <> wrote in message
>> news:[email protected]...
>> > Sorry about the silly subject title, I'm trying to shrug my
>> > frustration with
>> > good genuine quacky humor With that said, I have a difficult
>> > hurdle to
>> > overcome...
>> >
>> > I have a macro that returns, not one, but two arrays! Is this
>> > doable? The
>> > macro returns an answer that contains two arrays, which reside in
>> > different
>> > areas within the worksheet (i.e. the arrays are not adjacent to one
>> > another).
>> >
>> > Returning one array is not the problem--that's been done. But
>> > returning
>> > two, in different sections of the same worksheet--that's where the
>> > money's at!
>> >
>> > I've tried just about everything know to mankind... ok, maybe not
>> > everything
>> > Here's a list of my futile attempts:
>> > -Wrote to different cells from within the macro function.
>> > -Disabled Application.EnableEvents AND then wrote to different cells
>> > within
>> > the macro function.
>> > -Selected different areas of the worksheet before clicking
>> > Ctrl+****+Return.
>> > -Returned an array of variants (instead of a single array variant).
>> > -Used CalculateEvent to write to different cells.
>> > -Wrote to different cells using DDE.
>> > -Tried sending excel window message to change cells.
>> > -Tried hacking excel's COM Object Model.
>> >
>> > Nothing, zippo, nada worked! I sure would enjoy adding more
>> > failures to
>> > my list so if you have any great ideas send them my way
>> >
>> > At any rate, keep a good spirit and thanks in advance.
>> >
>> > -Fabricio
>> > [email protected]
>>
>>
>>