excel - VBA Type Mismatch of the same functions in different workbooks -

i'm having issue resolving type mismatch when running same code in different workbook. lets workbook 1 original workbook, , workbook 2 new workbook.

both workbook 1 & 2 have same code (below) listbox_refresh sub calls getaccountref() function. code runs in workbook 1 has type mismatch in workbook 2 , cannot figure out why.

i've checked vartypes of getaccountref() in both workbooks , different.

for workbook 1

• this results in 8204 (vbarray + variant) expected:

  debug.print vartype(getaccountref()) 

• this results in 8 (string) expected:

  debug.print vartype(getaccountref(0)) 

for workbook 2

• this results in 0 (empty):

  debug.print vartype(getaccountref()) 

• this results in error type mismatch:

  debug.print vartype(getaccountref(0)) 

the function trying run is:

function getaccountref() variant on error resume next  dim cell range dim row_i range set row_i = sheet5.range("9:9")    '<- error: range not contain "date"  dim counter integer counter = 0 dim date_ref() variant   each cell in row_i     if cell = "date"          redim preserve date_ref(counter)         date_ref(counter) = cell.address         getaccountref = date_ref          counter = counter + 1     end if next cell   on error goto 0 end function 

and i'm trying utilize function in for loop so:

    dim listedbnk variant     each listedbnk in getaccountref()         listedbnk = replace(listedbnk, "9", "7")         .combobox1.additem range(listedbnk)         .combobox2.additem range(listedbnk)      next listedbnk 

thanks!

your function has mistake.

function getaccountref() variant on error resume next  dim cell range dim row_i range set row_i = sheet5.range("9:9")    'tfsa tracker  dim counter integer counter = 0 dim date_ref() variant   each cell in row_i     if cell = "date"          redim preserve date_ref(counter)         date_ref(counter) = cell.address         counter = counter + 1     end if next cell          getaccountref = date_ref '<~~ @ moved. on error goto 0 end function 

and sheet module

sub test()     dim listedbnk variant     dim myarray variant      myarray = getaccountref      activesheet         .combobox1.clear         .combobox2.clear     'for each listedbnk in getaccountref()     each listedbnk in myarray         listedbnk = replace(listedbnk, "9", "7")         .combobox1.additem sheet5.range(listedbnk)         .combobox2.additem sheet5.range(listedbnk)      next listedbnk     end end sub