use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
$xl->{SheetsInNewWorkbook} = 1;
my $wb = $xl->Workbooks->Add;
$xl->VBE->ActiveVBProject->VBComponents->Add(1);

my $cm = $xl->VBE->ActiveVBProject->VBComponents(3)->CodeModule;
my $line = int($cm->CountOfLines);
$cm->InsertLines(++$line,"Function Example(n as double) as double");
$cm->InsertLines(++$line,"Example = n^2");
$cm->InsertLines(++$line,"End Function");

$wb->Sheets(1)->Cells(1,1)->{Formula} = '=example(2)';

$xl->{DisplayAlerts} = 0;
$wb->Close;
$xl->Quit;