integrating Excel VBA functions in Access

[MikeDutcher]

I have an Excel workbook in which I have written several VBA functions, such as, AnnuityCertain(Term,Interest) which receives a term (e.g. 5 years) and an interest rate (e.g.7%) and returns the annuity certain (e.g. 4.39).

I want to be able to use this function from an Access database.

I could just copy the VBA code from Excel to Access, but that doesn't seem very elegant. Further, any future revisions to AnnuityCertain would have to be done in both places.

It seems like it should be easy to have Access use the VBA function out of Excel.

Is it? & if so, how do I do it?

 

[bkkauffeld]

Try to add the Excel Object Library to the your Object Browser and then Browse it for the necessary functions. That is the only way I know to find them.

 

[MichaelRed]

I don't think "AnnuityCertain" is an intrinsic procedure for Excel. If it is not, then bkkauffeld'd suggestion will not work, as only the intrinsic (e.g. nuilt in functions) functions are available from the objet library.

If, as I believe, "AnnuityCertain" is a user defined function, then the only way I know to be able to use it in both is to make an add in (User DLL llibrary) and "add it to whatever project you need it in.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over