Consulting

Results 1 to 4 of 4

Thread: Generic populate listbox function with listbox and array as arguments

  1. #1
    VBAX Regular
    Joined
    May 2017
    Posts
    12
    Location

    Generic populate listbox function with listbox and array as arguments

    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.

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

  3. #3
    VBAX Regular
    Joined
    May 2017
    Posts
    12
    Location
    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!
    Last edited by ScotWW; 09-30-2017 at 09:02 PM.

  4. #4
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •