Solved

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

Posted on 2014-03-27
3
773 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
  • 2
3 Comments
 
LVL 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 167 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 45

Assisted Solution

by:aikimark
aikimark earned 333 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 45

Accepted Solution

by:
aikimark earned 333 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article will show, step by step, how to integrate R code into a R Sweave document
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

861 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now