Solved

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

Posted on 2014-03-27
3
791 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 50

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

791 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