Extending Excel - with new formulas

[SteveGoodman]

I have been writing a C DLL (XLL) for Excel which extends the core forumals to link into one of our back end systems. We have got this working ok, but now we want to extend it.

The problem is we want to have a formula which takes 1 fixed parameter and then at least one pair of parameters, but possibly upto 36 pairs of parameters depending how the user does it. So it works similar to the sum formula where you can have =sum(B1+B2) or you could have =sum(B1+B2+B3+B4).

Do I have to code all 73 parameters into the code, or is there a way of passing a parameter array?

Also what would be nice is when you click on the FX button and select a forumla you get a dialog for entering the cell codes into, this comes as standard, but if we have 73 parameters this dialog is going to look real ugly, so does anyone know how the sum function works, as each time you populate the last parameter it adds another parameter onto the list?

Cheers

Steve.

 

[Andrew Poulsom]

The array must be the last (or only) argument like this:

Function MyFunc(ParamArray arglist() As Variant)

It is always a variant.

 

[SteveGoodman]

Sorry, the code is written in C. This was because Excel 97 and Excel 2K don't support COM DLL's, so it is easier to write in C.

I think I need to make a change to the the way we call this

err = Excel(xlfRegister, 0, 1+ARGCOUNT, (LPXLOPER)&xDll,
(LPXLOPER)TempStr(func[0]),
(LPXLOPER)TempStr(func[1]),
(LPXLOPER)TempStr(func[2]),
(LPXLOPER)TempStr(func[3]),
(LPXLOPER)TempStr(func[4]),
(LPXLOPER)TempStr(func[5]),
(LPXLOPER)TempStr(func[6]),
(LPXLOPER)TempStr(func[7]),
(LPXLOPER)TempStr(func[8]),
(LPXLOPER)TempStr(func[9]),
(LPXLOPER)TempStr(func[10]),
(LPXLOPER)TempStr(func[11]),
(LPXLOPER)TempStr(func[12]),
(LPXLOPER)TempStr(func[13]),
(LPXLOPER)TempStr(func[14]),
(LPXLOPER)TempStr(func[15]),
(LPXLOPER)TempStr(func[16]),
(LPXLOPER)TempStr(func[17]),
(LPXLOPER)TempStr(func[18])
);

So Excel knows that we are exposing a function from this DLL that supports a parameter array.

Any Help would be top.