Solved

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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 …

778 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