User functions

[odisej]

Hi

As far as I can understand (I haven't checked this though) there was option in older xls to define user functions. I can see OnFunction event in new version but I can't find the place where this is executed or AddUserFunction which should be used to register user function. Is this still supprted in new version or not? If not, will you support this? We need this very much as we have a million custom functions.

Grega

[larsa]

Hello

You don't have to define user functions. Just enter them in the formula. The OnFunction event is however never fired. Will fix that in the next update.

[odisej]

Hello

You don't have to define user functions. Just enter them in the formula. The OnFunction event is however never fired. Will fix that in the next update.

Do you have any idea when next update will be ready? I can miss some things and move on but I can miss those functions.

[larsa]

Hello

It will not take long, as most a week.

[odisej]

Anything new on the subject?

Thanks

[larsa]

Hello

The update is ready now.

[odisej]

Hi Lars

On the registered users download page it says I can download 5.20.01. Is that just the wrong text? I need XE version.

[odisej]

Is this working? I have Delphi XE and I have formula like =MyFormula(1) in the cell. When I click into the cell, I get exception. I also don't get any event for this assignment:

XSS.XLS.OnFunction := MyFunction;

When I call this and I debug, I step into this:

procedure TXLSReadWriteII5.SetFunctionEvent(const Value: TFunctionEvent);
begin

end;

It's empty?

[larsa]

Hello

Use the OnUserFunction event. The OnFunction event is only there to avoid compiler errors (if it was removed). There will be a sample on how to use OnUserFunction in the next update.

[odisej]

I've used now OnUserFunction event but I get access. I have =MYFUNC(1) in the cell.

Debug:
TXLSWorksheet.GetAsFormula at step Result := FOwner.Formulas.DecodeFormula(FCells,FIndex,Ptgs,Sz,False); I get access.

Going deeper I get into DecodeFmla, there is a big case and first loop gets to xptg_EXCEL_97, second loop to xptgNum and third loop to xptgFuncVar97 where crashes at S := ExcelFunctions[...

[larsa]

Hello

There is a sample on how to use user functions in update 5.20.04

[odisej]

I changed the demo this way:

procedure TfrmMain.btnReadClick(Sender: TObject);
begin
XLS.Filename := edReadFilename.Text;
XLS.Read;
XLS[0].AsInteger[0,0] := 4;
XLS[0].AsInteger[0,1] := 5;
XLS[0].AsFormula[0,2] := 'SUM(A1:A2)';
XLS[0].AsFormula[0,3] := 'TEST_NUM_1ARG(8)';
end;

When I press Calculate button, I never drop into XLSUserFunction. perhaps I'm doing something wrong?

Grega

[larsa]

Hello

Add this line:

Code: Select all

XLS.Calculate;

[odisej]

I added but...

Looking at TXLSFormulaEvaluator.DoEvaluate, Sum function goes to xptg_EXCEL_97, FStack.Push, then to xptgFuncVar97 where DoFunction is called.
Same procedure for custom function goes to xptg_EXCEL_97, then to xptgInt97 where 8 is pushed to stack and finished.

[odisej]

Could you please look at this issue. I can't move forward.

Thanks

Grega