populating a string array dynamically from a column of phrases in an excel sheet -

i have 2 sheets. using user-defined function in sheet 1, in want use array compare strings. array comprised of contents of column of cells in second sheet (which named "phrases.").

so (looking @ way) in "phrases" have 100 strings typed column p, cells 3 102. , want put of them array can use later.

now, let me complicate little - intent users of spreadsheet able add new content column p, may 500 cells or more. want populate array dynamically.

here's - , doesn't seem working:

dim newarray() string dim long dim counter long counter = 0 = 0 5000     if worksheets("phrases").cells(i + 3, 16).value <> 0     newarray(counter) = worksheets("phrases").range(i + 3, 16).value     counter = counter + 1     end if next 

where going wrong?

please note - i've tried without .value - didn't seem work. i've tried .text instead of .value - didn't seem work. i've tried cstr(worksheets("phrases").range(i + 3, 16).value) , several variations - , didn't seem work.

i expect there simple missing here - have no idea what.

dim newarray() string dim long dim lr long dim counter long lr = activesheet.range("p" & rows.count).end(xlup).row counter = 0 = 1 lr     if worksheets("phrases").range("p" & i).value <> 0         redim preserve newarray(counter)         newarray(counter) = worksheets("phrases").range("p" & i).value         counter = counter + 1     end if next