Generic populate listbox function with listbox and array as arguments

[ScotWW]

I'd like to have a generic function to populate my listbox with public arrays (variants). I've done some googling and done my best but it's not good enough.

The following iss not working for me (the array doesn't seem to read in):

Private Function populateListbox(lbArg As MSFORMS.ListBox, ParamArray arrX() As Variant)
lbArg.Clear 'clear the listbox
Dim cntR As Integer
If UBound(arrX) > 0 Then 'lame attempt to ensure argument is
passed,but it always will be for me.
For cntR = 0 To UBound(arrX) - 1
lbArg.AddItem CStr(arrX(cntR)) 'populate the listbox with
array contents
Next cntR
End If


End Function

and the function call as follows:
Call populateListbox(ListBox1, fnm) ' where fnm is a variant array

arrX argunet doesn't seem to be read into the function, even though when the array is passed in before entering the function I can see it is indeed a variant array with one element. hovering over cstr(arrx(cntR)) in debug mode shows type mismatch and hoering over arrX shows nothing. Debug.print(arrx(0)) gives a runtime error 13 in debug mode (breakpoints enabled)

Any help would be most appreciated on this.

[gmaxey]

Private Sub LoadForm()
Dim oFrm As UserForm1
Dim A, B
A = Split("1,2,3", ",") 'First variant array
B = Split("A,B,C", ",") 'Second variant array
Set oFrm = New UserForm1
With oFrm
populateListbox .ListBox1, A, B 'Pass listbox and both variant arrays to load routine.
.Show
End With
lbl_Exit:
Exit Sub
End Sub

Sub populateListbox(oList As MSFORMS.ListBox, ParamArray arrX() As Variant)
'ParamArray is used when an unknown (or variable) number of arguments can be passed
'Functions typically return a value. Since this routine does not is doesn't need to be a function.
Dim lngParam As Long
Dim lngIndex As Long
oList.Clear
If UBound(arrX, 1) > 0 Then
For lngParam = 0 To UBound(arrX, 1)
For lngIndex = 0 To UBound(arrX(lngParam))
oList.AddItem CStr(arrX(lngParam)(lngIndex))
Next lngIndex
Next lngParam
End If
lbl_Exit:
Exit Sub
End Sub

[ScotWW]

Hi Greg!
I love this website because of great people like you! Your website is also an uplifting one. Thank you for your response.

Your solution indeed worked well with multiple 1D arrays. Initially I couldn't get your populateListbox() going with my single public pre-defined variant 1D array as the 2nd argument and without a 3rd argument.


It is great that you were able to teach me a few things:
* Subs can have arguments (for some reason I thought only functions could pass arguments)
* Your representation of arrX helped me to realise a solution to my problem, solution used I will post below (I learnt how to represent 1D and presumably multidimensional arrays)...confusingly, I thought I knew how to do to this already
* Introduced me to splilt()

After overcoming some troubles, I have learnt also that passing an array with brackets() as arguments to a ParamArray arr(X) sub results in one dimensional array placed to it becoming elements of the 0th second dimension... Ie. arrayPassed2Function(1) becomes arrX(0)(1), arrayPassed2Function(x) becomes arrX(0)(x).



The intention of my sub is to only ever take two arguments: a listbox and a one dimensional array. To do this I did as in the sub below, dropping array brackets and paramArray in the sub arguments.

I am so happy to have a higher level sub that I can run on any listbox that I add to my userform! Awesome. Dropdown boxes are next!

This is what I used updated after post a few times now:

Private Sub populateListboxAlternate(lbArg As MSFORMS.ListBox, arrX As Variant)
lbArg.Clear 'clear the listbox
Dim cnteR As Integer
If UBound(arrX, 1) > 0 Then
For cnteR = 0 To UBound(arrX, 1) - 1
lbArg.AddItem CStr(arrX(cnteR))
Next cnteR
End If
End Sub

How I plan to use and call the sub, example follows

private sub commandbutton7_click()
Call populateListboxAlternate(ListBox1, fnm) 'fnm is a module level public variant that is being used as a 1D array
end sub



Thank you Greg!

[gmaxey]

Scot,

My fault your first test didn't work. As I mentioned in the first post, Paramarray is only needed when the calling procedure may pass a variable/unknown number of arguments, but in implementing, I made the requirement greater than 0.

If UBound(arrX, 1) > 0 Then

Now, I you are always going to pass only one argument then skip Paramarray and just past the argument. The code is much simpler:


Private Sub LoadForm()
Dim oFrm As UserForm1
Dim A, B
A = Split("1,2,3", ",") 'First variant array
B = Array(1, 2, 3, 4, 5) 'Second variant array
Set oFrm = New UserForm1
With oFrm
populateListbox .ListBox1, A, B 'Pass ListBox1 and both variant arrays to load routine using a ParamArray
populateListboxII .ListBox2, B 'Pass .LIstBox2 and single array to other load routine that is much simpler.
.Show
End With
lbl_Exit:
Exit Sub
End Sub

Sub populateListbox(oList As MSForms.ListBox, ParamArray arrX() As Variant)
'ParamArray is used when an unknown (or variable) number of arguments can be passed
'Functions typically return a value. Since this routine does not is doesn't need to be a function.
Dim lngParam As Long
Dim lngIndex As Long
oList.Clear
For lngParam = 0 To UBound(arrX, 1)
For lngIndex = 0 To UBound(arrX(lngParam))
oList.AddItem CStr(arrX(lngParam)(lngIndex))
Next lngIndex
Next lngParam
lbl_Exit:
Exit Sub
End Sub

Sub populateListboxII(oList As MSForms.ListBox, X As Variant)
oList.List = X
lbl_Exit:
Exit Sub
End Sub