Axolot Data

To see what I mean, place a new XLSReadWriteII4 component on your form and add a button with the following code:

procedure TForm1.Button1Click(Sender: TObject);
const FILE_NAME: string = 'C:\Test.xls';
var vba: TVBAModuleSource;
begin
if FileExists(FILE_NAME) then
DeleteFile(FILE_NAME);

xls.Filename := FILE_NAME;

// Add a custom vb function "UpCase" which takes a string
// and returns it as uppercase.
xls.VBA.EditVBA := True;
vba := xls.VBA.AddModule('Module1', vmtMacro);
with vba.Source do
begin
Add('Public Function UpCase(Text As String) As String');
Add(' UpCase = UCase(Text)');
Add('End Function');
end;

xls.Sheet[0].AsString[0, 0] := 'This is a test';
xls.Sheet[0].AsFormula[0, 1] := 'UpCase(A1)';
xls.Write;
end;

When opening the spreadsheet, A2 should display as "THIS IS A TEST" but it instead shows as #NA. I'm using Office 2003 and exporting to Office 97 format.

Just to be clear, the problem is writing a new spreadsheet in XLSReadWriteII, creating a public VBA function in a VBA module and writing a formula which uses that function. I don't care about being able to call that function and get a result from within XLSReadWriteII, but rather I just want the formula to work in Excel - which it doesn't! Instead of the formula appearing the in the cell, the formula just reads "=#N/A"