none
VBA Function using a table as parameter RRS feed

  • Question

  • Hi

    Im trying to write a VBA function to which I would pass a table argument (I understand these are of the ListObject type). 

    the function definition is as below:

    Function myFunction(resultTable As ListObject, ascending As Boolean, rsltNumber As Integer) As Variant()

    unfortunately, it is just giving me a #Value in excel with no error, and impossible to debug. Here is my call:

    =myFunction(Table4,TRUE,10)

    it is driving me crazy! I have no idea what is causing it to fail, but it doesnt seem to like the table at all as an argument, since if I pass a range instead it works...

    Thanks for your help!

    Charles

    edit: I am using excel 2010 btw 



    • Edited by cvl07 Friday, August 28, 2015 5:11 PM
    Friday, August 28, 2015 5:10 PM

Answers

  • Option 1: Declare the argument as a Range:

    Function myFunction(resultRange As Range, ascending As Boolean, rsltNumber As Integer) As Variant()

    and use resultRange.ListObject within the body of the function to refer to the table:

        Dim resultTable As ListObject
        Set resultTable = resultRange.ListObject

    Option 2: Declare the argument as a String:

    Function myFunction(resultName As String, ascending As Boolean, rsltNumber As Integer) As Variant()

    and use Application.Caller.Parent.ListObjects(resultName) within the body of the code to refer to the table:

        Dim resultTable As ListObject
        Set resultTable = Application.Caller.Parent.ListObjects(resultName)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, August 28, 2015 5:42 PM
  • Function myFunction(resultTable As ListObject, ascending As Boolean, rsltNumber As Integer) As Variant()

    =myFunction(Table4,TRUE,10)


    The 1st argument is not a ListObject when you use the function as UDF, it's a Range. And the return type must be Variant not Variant().

    Function myFunctionTest(ByVal resultTable As Range, ByVal ascending As Boolean, _
        ByVal rsltNumber As Integer) As Variant
      myFunctionTest = resultTable.Address
    End Function

    Andreas.

    Friday, August 28, 2015 5:36 PM

All replies

  • Function myFunction(resultTable As ListObject, ascending As Boolean, rsltNumber As Integer) As Variant()

    =myFunction(Table4,TRUE,10)


    The 1st argument is not a ListObject when you use the function as UDF, it's a Range. And the return type must be Variant not Variant().

    Function myFunctionTest(ByVal resultTable As Range, ByVal ascending As Boolean, _
        ByVal rsltNumber As Integer) As Variant
      myFunctionTest = resultTable.Address
    End Function

    Andreas.

    Friday, August 28, 2015 5:36 PM
  • Option 1: Declare the argument as a Range:

    Function myFunction(resultRange As Range, ascending As Boolean, rsltNumber As Integer) As Variant()

    and use resultRange.ListObject within the body of the function to refer to the table:

        Dim resultTable As ListObject
        Set resultTable = resultRange.ListObject

    Option 2: Declare the argument as a String:

    Function myFunction(resultName As String, ascending As Boolean, rsltNumber As Integer) As Variant()

    and use Application.Caller.Parent.ListObjects(resultName) within the body of the code to refer to the table:

        Dim resultTable As ListObject
        Set resultTable = Application.Caller.Parent.ListObjects(resultName)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, August 28, 2015 5:42 PM