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.
| 