Calling a Subroutine from a UDF

Yair has two macros in a workbook: a UDF and a subroutine that (among other things) adds a worksheet to the workbook. 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. If there is, he wonders why, and if there isn't, he wonders what he can do to call the subroutine.

You can, within a UDF (user-defined function) use the CALL statement; VBA (and Excel) allow you to do this. What you cannot do during the execution of a UDF or a subroutine called by a UDF is to modify the Excel environment. This means you cannot take an action that modifies the worksheet structure or the workbook structure. That's not all, though; there are other things that cannot be done while a UDF is running. These are detailed at this Microsoft site:

https://support.microsoft.com/en-us/topic/description-of-limitations-of-custom-functions-in-excel-f2f0ce5d-8ea5-6ce7-fddc-79d36192b7a1

Note that the article indicates it is applicable to Excel 2010, though it is also applicable to all other modern versions of Excel. 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. Functions (built-in or user-define) are intended to return values.

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. 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. Let's say, for instance, that Yair's subroutine (the one that, among other things, adds a worksheet) is called MyRoutine. Yair could structure that subroutine in this manner:

Dim bFlag As Boolean

Sub MyRoutine()

    ' define the variables and constants you want to use

    If bFlag Then
        ' add worksheet and do other things you cannot
        ' do in the UDF
        bFlag = False         ' reset the flag to False
    End If

    ' do other things you need to do

    ' reset the timer:
    Application.OnTime Now() + TimeValue("00:01:00"), "MyRoutine"
End Sub

In this instance, the bFlag variable is set outside of any procedure, but it is still within the module. That way the value of the variable can be accessed by all of the procedures within the module.

Next, a timer needs to be set (using the OnTime method) to run MyRoutine at some future point. If you wanted to set the timer manually, you could use a short macro such as this:

Sub SetTimer()
    Application.OnTime Now() + TimeValue("00:01:00"), "MyRoutine"
End Sub

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. Once the timer is set, then MyRoutine runs (in this case) every minute. 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.

All that is left to do is to add this line to your UDF:

bFlag = True

The Boolean flag is set, and the next time MyRoutine is executed, you'll get the desired changes to your workbook. And, since the changes occur outside of the UDF, then both VBA and Excel are happy.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12892) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.