<

Wrapper-2, VBA.  Show result of Excel Worksheet Function in Access Query

Posted on
6,062 Points
62 Views
Last Modified:
Published
Experience Level: Beginner
8:19
crystal (strive4peace) - Microsoft MVP, Access
Love empowering people by teaching and helping them develop applications.
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access.

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 and refer to it so code in Access can use it. Reuse the Excel application object to make susbsequent processing faster ... so, how do we clean that up?

Use the function in a query! Part 1 shows a query with the calculated column that calls this code:
Wrapper-1-Query-Show-result-of-Excel-Worksheet-Function-in-Access-Query.html

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 comment and let me know your thoughts about this? Thanks!

have an awesome day,
crystal

Video Steps

1. Dimension oExcel as object at top of module

2. Name of function follows the "Function" keyword

3. Enclose function parameters in parentheses

4. Declare data type of return value

5. Set up error handler


For more information on the error handling, here is a short video: Basic-Error-Handling-code-for-VBA-and-Microsoft-Office.html

6. Define default return value

7. Validate parameters -- not done in this example

8. See if Excel is loaded and ready to use


if not, then open Excel in the background and use oExcel to represent it

9. Call the Excel function you want and pass parameters

10. Assign the result to the function return value

0
0 Comments
On the official Microsoft website we can find out curious data about Excel. In this post, I will tell you about the ones that have caught my attention the most.
Often times what you may consider better indicators of various items, others do not.  It is always a good idea to have a way to turn off conditional formatting in whatever program is displaying it.  The most obvious I will use here - MS Excel.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month