Here is a UDF that uses a delayed Sub to add a worksheet to the active workbook (as requested by Yair): Macro must not be defined in a Class Module or UserForm. Macro = "MyMacro ""Hello World"",3.14,FALSE" Macro can include text, numeric, and/or logical arguments. My Excel Toolbox includes Sub RunMacroDelayed_Do(Macro) to add the procedure named Macro to a FIFO queue of delayed procedures that will run after a 1 millisecond delay. However, a UDF can use Windows API functions SetTimer and KillTimer for delayed initiation of a procedure that might otherwise be prohibited. And OnTime cannot start a Sub that takes parameters. The Tip's OnTime resolution is 1 minute (a significant delay) the minimum OnTime resolution is about 1 second. This is not very efficient, especially if a worksheet references several such UDFs that have different purposes. The Tip cleverly avoids this problem by continuously initiating OnTime before (and after) a UDF might want to use it. Excel imposes several restrictions as described in the Tip.Ī UDF will return an error if it initiates Application.OnTime directly or indirectly (by use of a Sub). And, since the changes occur outside of the UDF, then both VBA and Excel are happy.Ī UDF is a VBA Function that is referenced in a cell formula. The Boolean flag is set, and the next time MyRoutine is executed, you'll get the desired changes to your workbook. The value of the bFlag variable is checked in the subroutine, and if it is True, then the things you had wanted to do in the UDF are executed.Īll that is left to do is to add this line to your UDF: Once the timer is set, then MyRoutine runs (in this case) every minute. Or, you could add the single line of code to an event handler, such as the Workbook_Open so that it is automatically executed when the workbook is opened. If you wanted to set the timer manually, you could use a short macro such as this: Next, a timer needs to be set (using the OnTime method) to run MyRoutine at some future point. That way the value of the variable can be accessed by all of the procedures within the module. In this instance, the bFlag variable is set outside of any procedure, but it is still within the module. ' add worksheet and do other things you cannotĪpplication.OnTime Now() + TimeValue("00:01:00"), "MyRoutine" ' define the variables and constants you want to use Yair could structure that subroutine in this manner: Let's say, for instance, that Yair's subroutine (the one that, among other things, adds a worksheet) is called MyRoutine. So, how would you go about doing what Yair wants done? I think the best way would be to restructure the relationship between the UDF and the subroutine. The bottom line in Yair's case is that the CALL function is just fine, but what is being done during the CALL function (adding a worksheet to the workbook) is not fine-that is where the hang-up is. Functions (built-in or user-define) are intended to return values. Note, as well, that the reason a UDF cannot modify the Excel environment is because that is not their purpose-they are intended to augment Excel's built-in suite of functions, which also don't modify the Excel environment. Note that the article indicates it is applicable to Excel 2010, though it is also applicable to all other modern versions of Excel. These are detailed at this Microsoft site: That's not all, though there are other things that cannot be done while a UDF is running. This means you cannot take an action that modifies the worksheet structure or the workbook structure. What you cannot do during the execution of a UDF or a subroutine called by a UDF is to modify the Excel environment. You can, within a UDF (user-defined function) use the CALL statement VBA (and Excel) allow you to do this. If there is, he wonders why, and if there isn't, he wonders what he can do to call the subroutine. He cannot seem to call the subroutine from within the UDF, so he wonders if there is a restriction about using CALL within a UDF. Yair has two macros in a workbook: a UDF and a subroutine that (among other things) adds a worksheet to the workbook.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |