AutoExpanding User Defined Function Output

[MikeCave]

Hi there

I have been trying to find a way to auto expand the output of
function which returns a variable matrix result.

For example I want the user to be able to put a formula in one cell
and then the function auto populates the correct number of columns an
rows pertaining to the output.

From what I've seen it doesn't seem possible. What I wanted to do wa
simply:

In the function was the results have been obtained, right each row
column entry to the correct cell manually.

The problem i ran into is that it seems VBA functions are like littl
black boxes which can simply return a result. Excel would not allow m
to interact with the worksheet directly. I tried doing the same thin
through a sub procedure and it worked perfectly.

A sub procedure is not really an option as I want the user to be abl
to go =<function name>(parameters) in a cell to invoke a function.

Can anyone tell me if this is possible?

Thanks a million
Mike Cav

 

[Arvi Laanemets]

Hi

In Excel, functions can't change anything (cursor location, cell contents,
cell formatting etc.), they only return result value. NB! When you p.e.
change some parameter, and as result the value returned by function,
changes, it doesn't mean that cell content changes, it was and remains the
same - your function!

To change comething you have to use procedure/macro, or some event like
workbook's Open event, or worksheet's Change event, or worksheet's
SelectionChange event. (In Excel, you have 2 event-levels available -
workbook and worksheet events.)

 

[Frank Kabel]

Hi Mike
you won't be able to do this with function as these are not able to
change the Excel environment. That is changes of format, other cells,
etc. are not possible.
the only way to achieve something similar to this would be using a
worksheet_change event. But this is not that user friendly acording to
your specification

 

[Charles Williams]

If your function assigns an array to the function then a user can enter it
as an array formula into multiple cells (ctrl-shift-Enter) and it will
return multiple results.

But your user has to know how many cells to put the array formula into, so
this only really works for you if your function always returns the same
number of results.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

Hi

In Excel, functions can't change anything (cursor location, cell contents,
cell formatting etc.), they only return result value. NB! When you p.e.
change some parameter, and as result the value returned by function,
changes, it doesn't mean that cell content changes, it was and remains the
same - your function!

To change comething you have to use procedure/macro, or some event like
workbook's Open event, or worksheet's Change event, or worksheet's
SelectionChange event. (In Excel, you have 2 event-levels available -
workbook and worksheet events.)

 

[MikeCave]

Thanks for the input guys. Well, if it can't be done it can't be done.
The users will just have to learn to do it manually with ctrl shift
enter.

p.s. Sorry for the multipost! New to this forum, but hope to make use
of it in the future, and I won't multipost again.