Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: How to pass an array to a Function

    I have a function that I need to pass a string array to. How is this done?
    The calling statement does not work:
    Do_the_work(myArray()) <or "myArray() As String">

    Function Do_the_work(itemList() As String)
    ...
    End Function

    I need to know the correct Excel VBA syntax to call a function and pass an array to it. I've tried this several ways and get different error messages including "type mismatch" and "Array argument must be ByRef". Adding "ByRef" to the calling statement did not work:
    Do_the_work(ByRef myArray())

    Thanks.
    Jerry

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    The most basic function you need to provide one variable in the argument list which is going to accept the value of the Array you will pass to it.

    Most of the time you will pass the reference byRef. ByRef is the default in Visual Basic, so you do not need to explicitly state this. You can also state a type for your Array variable, but the Type definition is optional. For arrays I always type them as a Variant in the Sub procedure and do not state a type in the Function. If you do not Type the variable in the function it will adopt the type of whatever variable is passed to it.

    Here's an example
    Code:
    Sub CountArray()
      Dim arrayItem as Variant
      Dim nCount as Integer
    
    ' create a quick array
      arrayItem = Array(1,2,33,56,76,89,10)
    
    ' call the function to get the array count
    nCount = fCountArray(arrayItem)
    MsgBox "My Array has " & nCount & " Items in it"
    End Sub
    
    ' Gets the count of the Array Items
    Private Function fCountArray(arrayVar)
     fCountArray  = Ubound(arrayVar)
    End Function
    Notice if you are passing the entire array you use only the array name without the parenthesis. myArray If you are passing a single array value you must use the array name with the parenthesis and the number of the item myArray(3).

    If you are calling a function within the same module you may use a Private Function. If you will need to call the function from outside the module you must use a Public Function or using "Function" without the "Private" directive preceding it will be public. You may want to use a Public Function while developing your code so you can test it by calling it from the Debug window.
    ~

    Bill

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Bill, thank you for that information on how to pass an array reference to a function in Excel VBA, which will add to my skills.

    Jerry

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by JerryDal
    Bill, thank you for that information.
    Happy to help out Jerry. Knowing where to get the answers is an important skill for programmers. Excel has very good online help in the program which is great for beginner or advance vba programmers. You can place the cursor over any statement in the VB Editor and press the F1 key to open help for the topic if it is in the vb help.

    The Debug window also called the "Immediate Window" is another excellent tool to use. You can test the operation of any function in the immediate window by typing a ? (question mark) then the function.

    Example type ?fCountArray(array("hello","Goodbye","See Ya")) in the Debug window. Hit the enter key - will return a '2'. If you are testing the example posted previously and it is set as a Public Function.
    Last edited by savbill; 01-17-07 at 20:01.
    ~

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •