Search code examples
vbaexcel

Function Arguments dialog executes UDF when all non-optional arguments are filled

Let's say I have a function foo(bar as String, Optional baz as String), that does some really heavy lifting (connecting to database, etc ...). The problem we're seeing is that when the Function Arguments dialog is displayed (by clicking on Fx icon next to the formula to bring up that dialog) and the user puts a value of bar, Excel decides to execute the function right away, without waiting for the user to enter a value of baz or to press Ok.

Is there a way to detect in UDF that the user is using this dialog to basically not do anything?

In my screenshot, you can see that Excel shows Formula result in the bottom left corner once value for Bar is entered.

enter image description here

Solution

  • From my UDF page:
    Excel User-defined Functions
    To detect when a VBA UDf is being called from the function wizard you can add this code to the UDF

    If (Not Application.CommandBars("Standard").Controls(1).Enabled) Then Exit Function

    This code depends on the fact that when using the function wizard most icons in the toolbars are disabled.

    A solution is also possible by using the Windows API to check if the Function Wizard window is showing and has the same process ID as the current Excel process.