Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel invoke REST service function with Xpath fn parse result! | Excel Worksheet Functions | |||
How do I invoke a call in Excel | Excel Discussion (Misc queries) | |||
How can I invoke running a macro from within an "IF" function. | Excel Worksheet Functions | |||
VBA code to invoke the Print dialog box. | Excel Programming | |||
invoke macro from outside excel - auto macros | Excel Programming |