I have the following function to calculate the price of a Call option. It s written in a Modul. The main problem is everytime I am creating a new spreadsheet, i have to copy all the code in it.Is there a way to have this function to be called by any spreadsheet ? Thus, having a central VB code page, updated for all SpreadsheetsFunction Call_Eur(S, X, T, r, rf, vol) Dim a1 As Single Dim b1 As Single Dim c1 As Single Dim d1 As Single Dim d2 As Single a1 = Log(S / X) b1 = ((r - rf) + (0.5 * vol ^ 2)) * T c1 = vol * (T ^ 0.5) d1 = (a1 + b1) / c1 d2 = d1 - vol * (T ^ (0.5)) Call_Eur = (S * Exp(-rf * T) * Snorm(d1) - X * Exp(-r * T) * Snorm(d2))End Function

Yes.save it in personal.xlsand call it by usingpersonal.xls!Call_Eur(S, X, T, r, rf, vol)

You can make a little DLL in Visual Basic (not VBA), create all objects you like to reference later on and add that DLL to the references of your VBA.This way your objects will be recognised each time by VBA

Noygou,Brilliant, this works! I only need to have this *.xls file open all the time.Ntruwant,I ll try your method.

Why not just create a VBA XLA addin and have it automatically load every time you start Excel?

forgot to mention that usually, the personal.xls is saved in the excel autostart folder. You probably can locate the folder hereC:\Documents and Settings\user_id\Application Data\Microsoft\Excel\XLSTART\Excel will open personal.xls automatically every time it starts, and have it hidden. Thus you save the trouble of opening it every time. In fact this sheet is designed for the purpose of doing exactly what you desire -- when you record a macro, Excel asks where you wanna store it, the 'Personal Macro Workbook' refers to personal.xls. To check if this is indeed loaded, go to Window -> Unhide...

NoyghouThanks for the advice... following the first one, i just created an Empty spreadsheet, containing the VB Code in it, named "formula.xls"Then i changed all my formulas in other spreadsheet to include "=formula.xls!Delta_c_Eur(S,X,T,etc...."But I was not aware of this personal file opening everytime i open Excel. that's some very good advice (I look like the guy who discovered that Men walked on the Moon now to the others )DavidJNYour advice seems interesting, how do I create this ? is it a matter of saving some VBA code under a certain filetype ?

Wildbill,The following section of MSDN should get you started with Excel Add-ins (for Office XP):Excel Add-insI've never created an add-in -- so I'm not sure how much work is involved -- but after a quick browsing of the site it seems pretty simple.- Rob