current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am currently working on a module that is meant to populate three ActiveX ListBoxes. Each will be populated with the same values: choices for axes titles on a graph (X-axis, Primary Y-Axis and Secondary Y-Axis). I have a function called "FillBox" that takes a ListBox as an argument and populates it.

Originally I used the following to accomplish this:

Sub FillAllBoxes()
    FillBox X_BOX
    FillBox Y1_BOX
    FillBox Y2_BOX
End Sub

I was informed that I cannot use a Union to compact this code because of the MSForms.ListBox object type. There are many other subs that perform a repeated operation on each box, so it appears that some sort of array would be best to use.

So far, the only way I have found to define this array of ListBoxes is the following, though I am unsatisfied with the fact that the return value is a Variant but I can't seem to get it to work if I define it as Function BOX_ARRAY() As MSForms.ListBox:

Function BOX_ARRAY() As Variant
    Dim Temp(1 To 3) As 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 then use this sub, though I needed to explicitly define the argument to FillBox as ByVal, because BOX_ARRAY is a variant.

Sub FillAllBoxes()
    For j = LBound(BOX_ARRAY) To UBound(BOX_ARRAY)
        FillBox BOX_ARRAY(j)
    Next j
End Sub

A) Is there any way to have a function return an array that is not a Variant?

B) Would it be wise to stick with my original method of just repeating the function explicitly for each list box or is it worth using arrays for this type of operation?

C) In the function BOX_ARRAY is there a way around defining a Temp array and just populating the returned array right off the bat?

share|improve this question
1  
If you can't like Variant you can make it a Collection. But, you are never going to get a strongly typed array/collection in VBA. I like your first solution, personally. It is the least amount of code and works, right? –  Brad Apr 7 at 22:59
    
It does work, but I hate to see anything redundant in the code so I thought I'd branch out into other areas hoping for a more elegant solution. Thanks for the tip. –  teepee Apr 7 at 23:09
1  
It is just barely redundant. They way you are getting around this 'redundancy' is to write more code, which should not be the goal. It was clear and readable as it was, passing arrays around is going to complicate things. You only ave 3 listboxes. If you had 50 it'd be a different story. –  Brad Apr 7 at 23:11
    
You're right about that, it isn't all that bad. Just looking for ways to enhance the code somewhat. I will probably just stick with the current method, though the other answers will be good for future problems. –  teepee Apr 8 at 4:22

1 Answer 1

up vote 0 down vote

Here goes:

There are many other subs that perform a repeated operation on each box

Simply pass the ListBox to a function that operates on listboxes, e.g.:

Sub MyExample()

EnableListBoxes Array(Me.ListBox1, Me.ListBox2, Me.ListBox3)

End Sub

Sub EnableListBoxes(boxes as Variant)
Dim lbox as variant

For each lbox in boxes
    lbox.Enabled = True
Next

End Sub

Alternatively you can pass the entire form/worksheet/whatever contains the listboxes, and iterate over the collection containing them (e.g., UserForm.Controls, etc.) You'd need to do some additional logic to make sure you're operating on the right type of form control, but that is relatively easy to do.

I can't seem to get it to work if I define it as Function BOX_ARRAY() As MSForms.ListBox

Of course not, because an MSForms.ListBox is a single object. You can't expect to return an array of anything in to a function that is designed to return an object.

Is there any way to have a function return an array that is not a Variant?

Perhaps use a subroutine and pass the arguments ByRef. You don't need to return a value if you are operating on the reference directly, rather than a local copy of it. See Chip Pearson for some detailed explanations/examples:

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

Sub TestByRef()
Dim i as Integer
    i = 1
    ChangeValue i

    Debug.Print i

End Sub

Sub ChangeValue(ByRef x as Integer)

    x = 3

End Sub

In the function BOX_ARRAY is there a way around defining a Temp array and just populating the returned array right off the bat?

Not that I can think of. Perhaps with a class module and the class _Initialize method, you could automatically assign one of the class properties. But I think that ultimately is the same method: creating a temporary array that defines the return value. It is hard to tell for sure since you don't provide enough code (e.g., where are the variables? are they even declared?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.