1

I have a simple Excel VBA query that returns two fields. If I run it as [Public Sub] it works fine. If it run it as [Public Function] it fails. Why would this be...what is the difference between the two?

The code below opens the connection and queries the data. Messageboxes:

1 - Shows 75 records

2 - Shows "getting ready to write out records"

3a - Third Messagebox never fires if running as a Function.

3b - If running as a SUB, then the RecordSet contents are written into cells A5:B81 (from the previous line of code), and the messagebox shows 75 records

Any thoughts? I'm fairly new to VBA/Excel Automation, but have a lot of experience elsewhere.

Thanks!

Public Function GetAccountList(cMainStart As String, cMainEnd As String)

    ' Check to see if data connection is open...if not, connect
    ' declares CON and RS.  Opens Connection
    OpenGLJSJ

    Dim cSQL As String
    Set rs = New ADODB.Recordset
    Set rs.ActiveConnection = con
    rs.CursorLocation = adUseClient

    cSQL = "SELECT distinct Main, Sub From tblGLAccountsPeriodBalance where Main>='" & cMainStart & "' and Main<='" & cMainEnd & "'"

    rs.Open cSQL, con, adOpenForwardOnly, adLockReadOnly, adCmdTxt

    MsgBox rs.RecordCount
    MsgBox "getting ready to write out records"
    Worksheets("test").Range("a5").CopyFromRecordset rs
    MsgBox rs.RecordCount

End Function
Community
  • 1
  • 1
Jeff Barefoot
  • 43
  • 4
  • A `Function` in VBA returns a value - i.e. `GetAccountList = myreturnvalue` should be somewhere in this code. Just because it takes arguments doesn't mean it needs to be a `Function` - in your case, this should be a `Subroutine`. – dwirony Apr 25 '18 at 15:59
  • 1
    @dwirony it does not *need* to be a function, but it should not fail if it is declared as a function (it will simply return `Variant/Empty` by default). – GSerg Apr 25 '18 at 16:01
  • 2
    When you are saying "running as a Function", do you mean a [function that is called from a sheet cell](https://stackoverflow.com/a/3622544/11683)? – GSerg Apr 25 '18 at 16:02
  • @GSerg Yes you're right, I just wanted to clear up the difference in case he's new to VBA syntax. I don't know why the results are different here. – dwirony Apr 25 '18 at 16:03
  • @GSerg : YES, I'm trying to write a function that can be called from the worksheet. So a user could enter =GetAccountList(10000,19999) and get a list of all of the accounts that start with a "1". This will be implemented as an Excel Add-In. We have other functions that do this, like "GetEndingBalance()", but they all return a single value, not a recordset. My intent is for any user in our finance dept that has this Add-In to be able to quickly/easily extract a list of valid account numbers from our ERP. – Jeff Barefoot Apr 25 '18 at 16:08
  • 3
    You can't do that. A worksheet UDF **returns a value**, it doesn't populate other cells. – Mathieu Guindon Apr 25 '18 at 16:09
  • This is not how UDFs are supposed to work. You should return an array of data from your function and let Excel put it in cells. But you will be limited to the cells into which you have entered the function, you will not be able to spill data into more rows, and any additional data will be silently lost. Use either a sub or a querytable. And, from experience, the more fancy things like network access you do from worksheet functions, the more likely Excel is to crash. – GSerg Apr 25 '18 at 16:12
  • Thanks everyone...I appreciate that fast response. That definitely explains the problem. Can I implement a QueryTable from a UDF, and populate a range of cells? – Jeff Barefoot Apr 25 '18 at 16:17
  • You cannot do anything from a UDF. You can set up a querytable in advance and then refresh it with different parameters from a sub, or you can set up a new querytable each time from a sub, or you can use `CopyFromRecordset` from a sub, but you cannot do any of that from a worksheet function. – GSerg Apr 25 '18 at 16:26
  • My goal is to allow my users - who know how to use Excel functions, but not Macros, etc - to get a list of account numbers on-demand from any Excel document that they are working in. So, they'd be able to open a new workbook, go to a cell and type [something] that allows them to see a list of account numbers. Any thoughts as to a direction, or is this a no-go? – Jeff Barefoot Apr 25 '18 at 16:34
  • Set up a ribbon tab for them with buttons to push. Attach the buttons to subs. – GSerg Apr 25 '18 at 16:40
  • @GSerg, that's a reasonable way to implement. Thanks. – Jeff Barefoot Apr 25 '18 at 17:22

0 Answers0