Passing String of arrays through a function in VBA -

i creating several string arrays , attempting use function each array on excel sheet. supposed go through each row , every row counts see if of strings match value in active cell. seem , error when try pass string array function , empty value function parameter. here code

the array

anarray = array("string1", "string2", "string3") 

the function

function checkarray(a variant) integer     range("a1")    dim count integer    count = a.length - 1     while activecell.value <> ""              while count <> -1           if activecell.value = a(count)               checkarray = checkarray + 1         end if         count = count -1     next         activecell.offset(1, 0).select         loop   end function 

and call it

 checkarray(anarray) 

there seems few necessary things missing sample function code.

1. functions cannot select cells process them can pass range of 1 or more cells function parameter processed.
2. you've described array using vba code made no mention how function supposed determine nature of array beyond being fed incoming parameter. conflicts teh nature of remainder of sample function code since looks used udf worksheet function.

here expect work worksheet udf function.

function checkarray(rng range, optional variant) long     dim v long, vstrs variant      if ismissing(a)         vstrs = array("string1", "string2", "string3")     else         vstrs =     end if      v = lbound(vstrs) ubound(vstrs)         checkarray = checkarray + application.countif(rng, vstrs(v))     next v  end function 

the optional a variant array parameter can passed in constant array or defined within function default values stored within function.

syntax: =checkarray(<range of cells check>, <optional array of strings>)
examples: =checkarray(a1:a10)
                  =checkarray(a1:a10, {"abc", "def", "jkl"})

      

this function can called within sub assign long integer value declared variable.

sub test()     dim num long     num = checkarray(activesheet.range("a1:a10"), array("string1", "string2", "string3"))     debug.print num end sub