Which function for finding if a string is in an array (VBA) is better? -

i have 2 functions check see if string exists in array. don't know better , if there reasons use 1 on other. appreciated. thank :)

function 1

function isinarray(stringtobefound string, arr variant) boolean   isinarray = (ubound(filter(arr, stringtobefound)) > -1) end function 

function 2

function isinarray(myarray variant, val string) boolean     dim integer, found boolean     found = false      if not len(join(myarray)) > 0         found = false     else         = 0 ubound(myarray)             if myarray(i) = val                found = true             end if         next     end if     isinarray = found end function 

this do.

for each thing in arr     if instr(thing, stringtobefound) > 0 msgbox thing next 

your second function uses lot of memory big array.

this happens when join strings. don't know if join function uses stringbuilding or not. doubt nothing else in basic does. ordinary concatination shuffles lot of bytes around memory.

string concatination

and don't join strings 1 character @ time. see vbscript programmer. requires 50,000 bytes , many allocation , deallocation make 100 character string.

http://blogs.msdn.com/b/ericlippert/archive/2003/10/20/53248.aspx

ps: have mid statement in vba (not confused mid function) allows string building generic solution shuffling bytes. matters on large arrays/strings.