Wrapper Function that Calls Excel for Query in Access
Channel: LearnAccessByCrystal
Date: 2018-04-17

Want to use an Excel function in an Access query? A VBA wrapper function can interface with Excel. "Wrapper Function" is a term to mean a user-defined function (UDF) that calls another program. In this case, Excel is called and one of its worksheet functions is used. This example shows how to to calculate payments for a number of offers from data that is stored in a table using Excel's PMT function. 

Even though topics may be technical, I do my best to consider that a beginner may be watching, so extra detail is added. Hopefully not so much that more advanced users turn away ... maybe you will add your thought about this in a comment ? Thanks!

We define a calculated field in the column of a query that calls our wrapper function and displayes the result. 

Change colors! for columns using the Format property. Parts of a numeric format code specify how positive, negative, zero, and Null will appear, and you can also specify color names. See how.

What is the difference between using "0" and "#" in a format code? This is explained too.

See the calculations on the query design grid, and in the SQL statement, that make this work. Note that it is okay for intermediate calculated fields to be defined after the column where they are referenced.

Get comfortable with the SQL view of the query -- this displays in words what the query design shows graphically, and is what Access stores for a saved query. 

Go to the VBA that does this magic. Understand each statement in the code as it is explained.

The module name you are in is in the Name property, and also the titlebar. Good to use prefix of "mod" or "bas" to ensure that module name is different than any procedure name.

The declaration statement defines the function name and its return data type. The values it needs to calculate are specified as parameters, or arguments.

Create an error handler that will exit without the user realizing there might have been a problem, and define a default value of zero for the function return value.

See how to open Excel so the code in Access can use it, and how to reuse the Excel application object to make susbsequent processing faster.

Call Excel's PMT function, and pass the parameters it needs to calculate. Return this result so it appears on every row of a query.

There is also a report in the database that is not discussed in the video ... explore! A few comments are in the report footer.

* let's connect *
Do you need help? You know your business. I know how to teach, write programs, and develop applications. If you have a budget and want control of your project, let's connect and build it together!

visit MsAccessGurus and contact me today!
http://msaccessgurus.com 

I look forward to hearing from you ~

have an awesome day,
crystal