Inserting a UDF and Launching the Function Wizard from VBA

In a previous post I suggested you could do this by entering the function using VBA into the selected cell using a comma as the argument to the function, for example =LISTDISTINCTS.SUM(,) , and then calling the function wizard using Application.Dialogs(xlDialogFunctionWizard).Show.

This method mostly works but has 2 major drawbacks:

  • It won’t allow you to use F4 within the function wizard to change from relative to absolute.
  • You get #Value or #Name when launching an XLL-based UDF with only 1 argument.

After a lot of trial and error I eventually found a way that seems to work in all cases:

Sub GoFuncWiz(strFunc As String, blArray As Boolean)
On Error Resume Next
If blArray Then
Selection.FormulaArray = "=" & strFunc & "()"
Selection.Formula = "=" & strFunc & "()"
End If
Application.OnTime Now + TimeValue("00:00:01") / 4, "RangeFuncWiz"
End Sub
Sub RangeFuncWiz()
On Error Resume Next
End Sub

The simple (once you know how) solution is to launch the Range.FunctionWizard method, but with a quarter-second delay!
Oh, and you don’t need the dummy comma argument either.

This entry was posted in UDF, VBA, XLL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s