Public Function myfn(arg As Double) As Double
    myfn = arg + Rnd()
End Function
[download]

Cell A1: =myfn(RANDBETWEEN(5,17)) -- this cell-formula proves that myfn(arg) is working: Excel generates a random integer from 5 to 17, and passes it to myfn ; the myfn VBA then adds a random offset

Then I run the following perl, using Win32::OLE :

#!perl

use 5.012; # strict, //
use warnings;
use Win32::OLE;

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');

print "output = ", $Excel->Run('myfn', 17), "\n";
[download]

The output I get will be some random number from 17 to 18:

output = 17.468005657196
[download]

This is using the 'Run' Application Object from the Excel Object Model. The function (or subroutine) name is the first argument to $Excel->Run() ; arguments to that function/subroutine are the remaining arguments.