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



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