VBA Excel - Returning an array of ListBoxes from a function -

i working on module meant populate 3 activex listboxes. each populated same values: choices axes titles on graph (x-axis, primary y-axis , secondary y-axis). have function called "fillbox" takes listbox argument , populates it.

originally used following accomplish this:

sub fillallboxes()     fillbox x_box     fillbox y1_box     fillbox y2_box end sub 

i informed cannot use union compact code because of msforms.listbox object type. there many other subs perform repeated operation on each box, appears sort of array best use.

so far, way have found define array of listboxes following, though unsatisfied fact return value variant can't seem work if define function box_array() msforms.listbox:

function box_array() variant     dim temp(1 3) msforms.listbox     set temp(1) = x_box     set temp(2) = y1_box     set temp(3) = y2_box     box_array = this_array end function 

i can use sub, though needed explicitly define argument fillbox byval, because box_array variant.

sub fillallboxes()     j = lbound(box_array) ubound(box_array)         fillbox box_array(j)     next j end sub 

a) there way have function return array not variant?

b) wise stick original method of repeating function explicitly each list box or worth using arrays type of operation?

c) in function box_array there way around defining temp array , populating returned array right off bat?

here goes:

there many other subs perform repeated operation on each box

simply pass listbox function operates on listboxes, e.g.:

sub myexample()  enablelistboxes array(me.listbox1, me.listbox2, me.listbox3)  end sub  sub enablelistboxes(boxes variant) dim lbox variant  each lbox in boxes     lbox.enabled = true next  end sub 

alternatively can pass entire form/worksheet/whatever contains listboxes, , iterate on collection containing them (e.g., userform.controls, etc.) you'd need additional logic make sure you're operating on right type of form control, relatively easy do.

i can't seem work if define function box_array() msforms.listbox

of course not, because msforms.listbox single object. can't expect return array of in function designed return object.

is there way have function return array not variant?

perhaps use subroutine , pass arguments byref. don't need return value if operating on reference directly, rather local copy of it. see chip pearson detailed explanations/examples:

http://www.cpearson.com/excel/passingandreturningarrays.htm

sub testbyref() dim integer     = 1     changevalue      debug.print  end sub  sub changevalue(byref x integer)      x = 3  end sub 

in function box_array there way around defining temp array , populating returned array right off bat?

not can think of. perhaps class module , class _initialize method, automatically assign 1 of class properties. think same method: creating temporary array defines return value. hard tell sure since don't provide enough code (e.g., variables? declared?