Solved

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

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

760 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

18 Experts available now in Live!

Get 1:1 Help Now