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 & "()" Else Selection.Formula = "=" & strFunc & "()" End If Application.OnTime Now + TimeValue("00:00:01") / 4, "RangeFuncWiz" End Sub Sub RangeFuncWiz() On Error Resume Next Selection.FunctionWizard 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.