Auto invoke of a VBA Sub function
 

Suppose I have defined VBA Sub myFunct that picks up the contents of several
cells,
and processes these items to produce contents to be written to several other
cells,
IOW a function producing more than one value. How can I invoke this function
as part
of the normal worksheet execution cycle?

TIA

Ed

Auto invoke of a VBA Sub function
 

Jag Man wrote:
Suppose I have defined VBA Sub myFunct that picks up the contents of several
cells,
and processes these items to produce contents to be written to several other
cells,
IOW a function producing more than one value. How can I invoke this function
as part
of the normal worksheet execution cycle?


You could call your Sub procedure from the Worksheet_Calculate event (taking
care to avoid a recursive loop by using Application.EnableEvents = False).

But you would probably be better to remake it as a Function which takes the
input cells as arguments and returns the results as an array. Then
array-enter the function call into the output cells (using Ctrl+Shift+Enter).

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

Auto invoke of a VBA Sub function
 

Thnaks, Bill. I don't quite understand the last sentence. Do you mean one
would have to
do a keyboard action to distribute the array?

Ed


You could call your Sub procedure from the Worksheet_Calculate event
(taking
care to avoid a recursive loop by using Application.EnableEvents = False).

But you would probably be better to remake it as a Function which takes
the
input cells as arguments and returns the results as an array. Then
array-enter the function call into the output cells (using
Ctrl+Shift+Enter).

Bill Manville

Auto invoke of a VBA Sub function
 

Jag Man wrote:
Do you mean one would have to
do a keyboard action to distribute the array?


An array formula is entered from the keyboard into the selected cells
using Ctrl+Shift+Enter (instead of simply Enter).

(From VBA you set the FormulaArray property)

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup