I have an add-in that registers UDFs using ExecuteExcel4Macro. this is working fine. The add-in only registers when it detects a compatible workbook in the current session.
The problem is I want to change the current UDF from returning just a char but a char or an excel error.
I found some articles about using the Type library but I believe that means my UDF will always be registered and I want to control when I register them.
I currently Register with
ExcelApp.ExecuteExcel4Macro('REGISTER("filepath","add-in-name","AccountDescription","CC","Account Id","My Addin group")');
function AccountDescription ( sAccountId: PChar ) : PChar ; stdcall ;
I am using add-in-express to help with excel automation. The CC indicates that the function accepts a null terminated string and expects a null terminated string returned. I am not sure if I can specify a variant so I can return Excel2000.xlErrNa.
Edit: Fixed function call above I forgot REGISTER.
Edit (2): The 4th parameter in the register function contains the udf parameter and return value types. I'm not sure how to indicate a variant.
Edit (3): The dll / project is called DynacViews2 and in the DynacViews2.dll source file there is :
exports AccountDescription;
Sorry if this is a little fragmented. I want to emphasis that this is working with the code above currently. The issue is trying to get change the signature of AccountDescription to return an OleVariant instead of a PChar.
The reason for the change is I want to return a string or an Excel Error. Other examples online including add-in-express, show using the TypeLibrary to add the function and specify the return type as variant. This creates a signature using OleVariant and a string or Excel Error(Excel2000.xlErrNa) can be returned. The problem is I am using the VBA REGISTER function to only add the UDF's if the workbook is compatible.
I may be tackling this problem incorrectly.
Edit 4: Example of Register usage - This link shows the register usage but doesn't link to any microsoft site so there is no explanation of how to specify the parameters.
According to Using the CALL and Register Functions, Variant is not among the supported data types.
Code Description Pass by C Declaration
A Logical (FALSE = 0), TRUE = 1) Value short int
B IEEE 8-byte floating-point number Value (Windows) double (Windows)
Reference (Macintosh) double * (Macintosh)
C Null-terminated string Reference char *
(maximum length=255)
D Byte-counted string (first byte Reference Unsigned char *
contains length of string, maximum
string length = 255 characters)
E IEEE 8-byte floating-point number Reference double *
F Null-terminated string (maximum Reference (modify char *
string length = 255 characters) in place)
G Byte-counted string (first byte Reference (modify
contains length of string, maximum in place) unsigned char *
string length = 255 characters)
H Unsigned 2-byte integer Value unsigned short int
I Signed 2-byte integer Value short int
J Signed 4-byte integer Value long int
K Array Reference FP *
L Logical (FALSE = 0, TRUE = 1) Reference short int *
M Signed 2-byte integer Reference short int *
N Signed 4-byte integer Reference long int *
O Array Reference Three arguments are
passed:
unsigned short int *
unsigned short int *
double [ ]
P Microsoft Excel OPER data structure Reference OPER *
R Microsoft Excel XLOPER data structure Reference XLOPER *