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)
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:
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
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"
' Gets the count of the Array Items
Private Function fCountArray(arrayVar)
fCountArray = Ubound(arrayVar)
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.
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.