Passing String of arrays through a function in VBA

Question

I am creating several string arrays and attempting to use a function for each array on an excel sheet. It is supposed to go through each row and every row it counts to see if any of the strings match with the value in the currently active cell. I seem to get and error when i try to pass the string array to the function and get an empty value for the function parameter. Here is my code

the array

anArray = Array("string1", "string2", "string3")

the function

Function checkArray(a as Variant) as integer

   Range("A1")
   Dim count As Integer
   count = a.Length - 1

   Do While ActiveCell.Value <> ""        

    Do While count <> -1 

        If ActiveCell.Value = a(count) Then  
            checkArray = checkArray + 1
        End If
        count = count -1
    Next i

       ActiveCell.Offset(1, 0).Select    
    Loop
  End Function

and i call it

 checkArray(anArray)

This code is not valid and will not work anyway. You want to fix the lone `Range("A1")`, the `a.Length` (why do you thing `a` has `Length`?), the `Next` without `For`, and the [parentheses](http://stackoverflow.com/q/19538094/11683) when you call the function provided you don't appear to be storing the value anywhere. — GSerg, Apr 24 '15 at 21:04

score 1 · Answer 1 · answered Apr 25 '15 at 02:48

There seems to be a few necessary things missing from your sample function code.

Functions cannot select cells to process them but you can pass a range of one or more cells into the function as a parameter to be processed.
You've described your array using VBA code but made no mention as to how the function is supposed to determine the nature of the array beyond being fed it as an incoming parameter. This conflicts with teh nature of the remainder of the sample function code since it looks like it is to be used as a UDf worksheet function.

Here is what I would expect to work as a worksheet UDF function.

Function checkArray(rng As Range, Optional a As Variant) As Long
    Dim v As Long, vSTRs As Variant

    If IsMissing(a) Then
        vSTRs = Array("string1", "string2", "string3")
    Else
        vSTRs = a
    End If

    For v = LBound(vSTRs) To UBound(vSTRs)
        checkArray = checkArray + Application.CountIf(rng, vSTRs(v))
    Next v

End Function

The optional a variant array parameter can be passed in as a constant array or it is defined within the function by the default values stored within the function.

Syntax: =checkArray(<range of cells to check>, <optional array of strings>)
Examples: =checkArray(A1:A10)
=checkArray(A1:A10, {"abc", "def", "JKL"})

UDF function with arrays

This function can also be called from within a Sub to assign a long integer value to a declared variable.

Sub test()
    Dim num As Long
    num = checkArray(ActiveSheet.Range("A1:A10"), Array("string1", "string2", "string3"))
    Debug.Print num
End Sub

This helped me a little thanks. My code might be off a little, but what i am trying to do is put a loop within a loop. One loop goes through the selected cell range and the other loop inside of it compares each string within the array and then moves on when its finished. If it happens to match up with any of the strings, then it adds to the total amount of times each string was found and i use that number to pass to a variable for storage. — Ken Feier, Apr 27 '15 at 14:55
I essentially just want to know if its possible to create a function that takes an entire array as a parameter. I want to be able to plug in an array to the parameter, loop through the indexes of the array passed to the function and then have the function spit out the number i want — Ken Feier, Apr 27 '15 at 15:02
Yes, you can pass an array to a function for a single result. [Passing Arrays as a parameter to a VBA function](http://stackoverflow.com/questions/24606982/passing-arrays-as-a-parameter-to-a-vba-function) — , Apr 27 '15 at 20:43

Passing String of arrays through a function in VBA

1 Answers1