In this lesson you will learn how to fill a Listbox from an array. Listboxes are a very useful control type available in the VBA toolbox. You can use the listboxes for search and view functions of the VBA programs. First let’s fill the listbox using a multidimensional array. After that we can try it with a one dimensional array. This is the sample Excel sheet I’m going to use for this lesson.
And here is the listbox we are going to fill with the array.
So we have data in three columns and fourteen rows. First, we are going to add this data to an array. After that we can add the array to our listbox in the VBA form. You might wonder why we need to add the data to an array. Because data can be directly added to the listbox from the worksheet using VBA. Yes it is possible. But here our objective is to learn how to add data to a listbox from an array. Because there are situations where we need to add the data which is not available in worksheets to listboxes using VBA. For example you might add data from a worksheet to one array. Then search through the elements of that array and create another array from the search results. Now you may want to show this second array in a listbox. So then you have to fill a listbox directly from an array using VBA. There can be various examples like this. But to make this lesson easy to understand, I’m going to use this simple example.
Here what we are going to do is we are going to show the data in the listbox when the VBA form is loaded. So we can add the VBA code to the UserForm_Initialize event.
End Sub
First we need to declare a few variables. Let’s assume the name of the worksheet is “Data”.
Dim AllData(1 To 14, 1 To 3) As String
Dim i As Integer
Dim j As Integer
Here I have declared an array of the type string. Because I have only string type data in the Excel range. So you need to declare your array according to the type of data you have. If the data contains different data types then you should declare the array as type variant.
Next, assign the Excel sheet to the WS_Data variable.
Now we can add the data from the worksheet to the array. There are a few different ways to do this. Here is one method.
For j = 1 To 3
AllData(i, j) = WS_Data.Cells(i, j).Value
Next j
Next i
This post explains a quicker way to create an array from an Excel range.
Quick Multidimensional Array from Excel Range
Next step is to set the column count. If you don't set the column count then the listbox will have only one column. Assume the name of the listbox is “lstNameEmailList”. In the conventional naming method we add “lst” in the beginning of the listbox name.
Finally we can add the array to the listbox using the “List” method as follows.
Below is the full VBA code of the UserForm_Initialize event.
Dim WS_Data As Worksheet
Dim AllData(1 To 14, 1 To 3) As String
Dim i As Integer
Dim j As Integer
Set WS_Data = Worksheets("Data")
For i = 1 To 14
For j = 1 To 3
AllData(i, j) = WS_Data.Cells(i, j).Value
Next j
Next i
lstNameEmailList.ColumnCount = 3
lstNameEmailList.ColumnWidths = "100;100;250"
lstNameEmailList.List() = AllData
End Sub
Now data will be shown in the listbox when the userform is loaded.
So we learnt how to fill a listbox from a multidimensional array in VBA. Next let’s try to fill this listbox with a one dimensional array.
Assume we have a one dimensional array like this.
OneEmployeeInfo(1) = "Cathrine"
OneEmployeeInfo(2) = "Wintour"
OneEmployeeInfo(2) = "cathrinewintour@example.com"
We can add this array to our listbox easily inside the UserForm_Initialize event as follows.
Dim OneEmployeeInfo(1 To 3) As String
OneEmployeeInfo(1) = "Cathrine"
OneEmployeeInfo(2) = "Wintour"
OneEmployeeInfo(3) = "cathrinewintour@example.com"
lstNameEmailList.ColumnCount = 3
lstNameEmailList.ColumnWidths = "100;100;250"
lstNameEmailList.List() = OneEmployeeInfo
End Sub
Below is the result you will get when the VBA form is shown.
Here the data is listed in one column even though I have set the column count as 3. I faced a similar problem when I developed a VBA application recently. That program had a class module function which returns an array. Sometimes it returns a multidimensional array and sometimes a one dimensional array. Then the data of this returned array was shown to the user through a listbox. But as in above, listbox showed the data in a single column when the class module returned a one dimensional array. But I wanted to show the data horizontally when there is one row.
So how can we solve this problem? How to show the data horizontally in multiple columns instead of in a one column? For that you have to convert the one dimensional array to a multidimensional array. You can follow below steps to convert a one dimensional array to a multidimensional array in VBA.
Dim OneEmployeeInfo(1 To 3) As String
OneEmployeeInfo(1) = "Cathrine"
OneEmployeeInfo(2) = "Wintour"
OneEmployeeInfo(3) = "cathrinewintour@example.com"
Dim OneEmp_Multidimensional_Arr(1 To 1, 1 To 3) As String
For i = 1 To 3
OneEmp_Multidimensional_Arr(1, i) = OneEmployeeInfo(i)
Next i
lstNameEmailList.ColumnCount = 3
lstNameEmailList.ColumnWidths = "100;100;250"
lstNameEmailList.List() = OneEmp_Multidimensional_Arr
End Sub
Now when we show the VBA form using the form.show method, the listbox will be filled with the data like this.
Want to learn more about arrays? Then check these posts.
Fixed Size Arrays in VBA
Multidimensional Arrays in VBA
Dynamic arrays in VBA
Calculate With Arrays
