Microsoft Access Office VB VBA Help and Examples

Using Excel Functions in Queries

Using the standard setup you cannot use an excel function within a query. However, you can mask a function and borrow excel functionality to achieve the same result.

Create a new module or open an existing one. Paste the following code in.

Public Function ExcelCosH(Angle As Double) As Double
     ExcelCosH = Excel.WorksheetFunction.Cosh(Angle)
End Function

Add a reference to Microsoft Excel, and then modify the function line to the particular function you need. The one I've shown is the CosH function, although you can use almost any of the excel functions availiable. Also change the name of the function to something to remember (not forgetting to replace the name as the start of the second line). It may well be worth adding some checking code to make sure the value you pass is not null or out of the bounds.

Then in the query you can just use your newly created mask function.

The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.