<

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

Posted on
6,018 Points
18 Views
Last Modified:
Experience Level: Beginner
8:17
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
Comment
0 Comments

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

In the blog post, we are going to discuss step by step procedures to set up an archive and deletion policy for Office 365 mailboxes.
You can use IMAP or POP3 protocol to configure your Gmail account in MS Outlook. However, after using IMAP account in Outlook for some time, many users want to change their mail settings from IMAP to POP. Today we will discuss how quickly you can c…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month