user defined functions - Register Excel UDF without arguments

Question

I has many user define function with arguments and without. I use CUdfHelper from this article http://www.jkp-ads.com/articles/RegisterUDF00.asp for register function.

Registered function ask arguments for arguments, even if they are not.

Example my fuction without arguments:

Public Function getProjects()
   getProjects = Utils.execute("getProjects", "getWSEntitiesData")
End Function

On MyFunction.c

#include <windows.h>

#define DLL_EXPORT __declspec(dllexport)


DLL_EXPORT void getProjects() {
    return;
}

compile on MyFunction.dll

I register the function with these parameters.

SetGlobalName = Application.ExecuteExcel4Macro("MyFunction.dll", "getProjets", "P", "getProjects", "", 1, "MyFunctionCategory", "", "", "Return list projects")

If I register as

SetGlobalName = Application.ExecuteExcel4Macro("MyFunction.dll", "getProjets", "P", "getProjects",, 1, "MyFunctionCategory", "", "", "Return list projects")

Function argument dialog is displayed all the same.

If I register as

SetGlobalName = Application.ExecuteExcel4Macro("MyFunction.dll", "getProjets", "P", "getProjects",, 1, "MyFunctionCategory")

Function argument dialog isn't displayed, but the description is no longer available.

REGISTER() Arguments

1. Path and name of the dll
2. Name of the function you wish to call
3. Type string
4. The name you want to use in Excel cells
5. A list of arguments to use in the function wizard
6. The Macro type (2 for a function, 2 for a command)
7. Which function wizard category to add the function to
8. Short cut text if the function being registered is a command
9. Path to help file
10. Function help to show in the function wizard

11-30 onwards help text for each argument in the function wizard.

I think the problem is in the arguments, as by default it is set to an empty string, and I can not figure out how to change the parameters to the function.

On CUdfHelper

' structure definition
Private Type REGARG
    sDllName As String
    sDllProc As String
    sArgType As String
    sFunText As String
    **sArgText As String**
    iMacType As Integer
    vCatName As Variant
    sKeyText As String
    sHlpPath As String
    **sFunHelp As String**
    aArgHelp(1 To 20) As String
End Type

How to correctly set the parameters so that the window does not appear, and stores the description?

Answer 1

If register function with ExecuteExcel4Macro and set Function Description. In an XLL add-in, however, you may run into a bug in the Excel API. The bug shows itself if a parameterless function is mentioned in an ADXExcelFunctionDescriptor that has a non-empty string in the Description property. If this is the case, you'll get another version of the Function Arguments dialog.

That is, to bypass that issue, you need to leave the ADXExcelFunctionDescriptor.Description property empty.

Find on https://www.add-in-express.com/docs/net-excel-udf-tips.php