Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to call a UDF in Excel from Access and return an array of values

Posted on 2014-03-27
3
Medium Priority
?
848 Views
Last Modified: 2014-04-08
How can I call a a VBA function defined in Excel to return data to a function call from Access?

This is not calling a built in Excel function but rather a function defined in a module in Excel.  I want to call the function, have it populate an variable size array and return the array to the caller in Access


Thanks
0
Comment
Question by:MJDercks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 501 total points
ID: 39960909
Hi,

pls try
EDITED return array
Sub Connect()
    Dim xlApp As Variant
    Set xlApp = GetObject(, "Excel.Application")
    Param1 = 1
    Let answer = xlApp.Application.Run("'c:\\Documents\cat 14.xlsm'!'myFunction'", Param1)
End Sub

Open in new window


in XL to test the function

Public Function MyFunction(Nr As Integer) As Variant
    Let MyFunction = Array(Nr * 2, Nr * 2)
End Function

Open in new window

Regards
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 999 total points
ID: 39961792
That's very close.  You can return an array in Variant data type.
Let's put the following into an Excel workbook.  I put it into a module.
Public Function testremote()
    testremote = Array(1, 3, 5, 7, 9)
End Function

Open in new window

Then in Access, I created the following routine.  Note the addition of the workbook close. If you have trouble opening your workbook from the Windows explorer after running the prior code, you will need to use Task Manager to stop the phantom Excel process.
Public Sub remotetest()
    Dim xlApp As Object, x As Variant, vItem As Variant
    Set xlApp = CreateObject("Excel.Application")
    x = xlApp.Application.Run("'c:\users\Aikimark\downloads\Attendees-2013-11-02.xls'!'testremote'")
    Debug.print Ubound(x)
    For Each vItem In X
        Debug.Print vItem
    Next
    xlApp.Workbooks(1).Close
    Set xlApp = Nothing
End Sub

Open in new window

Since the Office security prompts me to open a workbook with macros, you instantiate a workbooks object, like this:
Public Sub remotetest()
    Dim xlApp As Object, x As Variant, vItem As Variant
    Dim wkb As Object
    Set xlApp = CreateObject("Excel.Application")
    Set wkb = xlApp.Workbooks.Open("c:\users\mark\downloads\Attendees-2013-11-02.xls")
    x = xlApp.Application.Run("testremote")
    Debug.Print "UBound(x):", UBound(x)
    For Each vItem In x
        Debug.Print vItem
    Next
    wkb.Close
    Set xlApp = Nothing
End Sub

Open in new window

So, you can return an array from a UDF.  There is no need to parse/split a string result.

Note: You can also return a collection object from a function.  Sometimes this proves to be a better data structure than an array.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 999 total points
ID: 39961803
If you didn't already know, defining a variable or function without a specific data type, defaults the data type to Variant.  Therefore
Public Function testremote()

Open in new window

has no specific data type.  It is the same as
Public Function testremote() As Variant

Open in new window

If you do explore the collection data type, you would use
Public Function testremote() As Collection

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question