Archive for September, 2010

Snappy ListBox Population

The Challenge…

Retrieve 10,000 rows from a database table and populate a ListBox control on an Excel Userform; and make it snappy. While 10,000 rows is not a “large” dataset in our business, it is certainly large enough to drive your application users crazy as they wait for their worksheet or List controls to be populated with all this information.  Clearly, looping through a 10,000 row Recordset and populating the ListBox one row/column at a time is not going to meet the requirement.  This post will demonstrate how to create the Userform and ListBox control, retrieve the dataset from the database, and populate the ListBox in an efficient manner.

The Solution…

The ListBox control itself is quite optimized for handling large sets of data.  The key to the speed is in how we retrieve the data from the database and, equally important, how the data is inserted into the ListBox control.

  • Create a new workbook.
  • Open the VBA Editor (Alt-F11).
  • In the Project Explorer, right click on “VBAProject (Book1)” and select VBAProject Properties.
  • Change the Project Name to “SnappyListBox”.
  • Change the Project Description to “Populate ListBox with 10,000 rows from database”
  • Click OK, then click the Save button.  Create a new folder called “SnappyListBox” and name the workbook “SnappyListBox.xlsm” (being careful to save the workbook as a Excel “Macro-Enabled Workbook (*.xlsm)”).
  • Next, create a new Userform (Insert… Userform).  Change the Name property to “frmSnappy” and the Caption to “Snappy ListBox Form”.
  • Change the form’s Height to “400” and Width to “600”.
  • Add a ListBox control to frmSnappy.  Set Top=30, Left=30, Height=300, and Width=540.  Change the ListBox’s name to lstSnappy.
  • Open up the frmSnappy code window and enter the following code in the Userform_Initialize event procedure:

Private Sub UserForm_Initialize()

    'Set basic formatting for the ListView

    With lstSnappy

        .ListStyle = fmListStylePlain

        .BorderStyle = fmBorderStyleSingle

        .ColumnCount = 6

        .BoundColumn = 2

        .TextColumn = 3

        .ColumnHeads = False

        .ColumnWidths = "40pt;55pt;140pt;75pt;40pt;185pt"

        .MultiSelect = fmMultiSelectSingle

        .SpecialEffect = fmSpecialEffectEtched

    End With

End Sub
  • This just sets the basic attributes of the ListBox, such as the number of columns of data it will have, etc.
  • Save the Project.
  • Next, open up the code window for ThisWorkbook and locate the Workbook_Open event.  Add code to show the Userform when the workbook first opens:
Private Sub Workbook_Open()


End Sub
  • Add a Command Button to the form, in the lower right hand corner.  Name it “cmdLoadList” and change the caption to “Load List”.
  • Save the Project once again.  Then, with the Workbook_Open() function still open and selected, press F5 to run the project.  (You could also close the Workbook and open it again to trigger the execution of Workbook_Open(), but just running it from the development environment is much easier).
  • You should see your form appear with a blank list and Command Button, looking something like this:
Basic Userform with empty ListBox

Basic Userform with empty ListBox

  • If any errors are raised, review all the instructions above and try to determine where it went wrong.  Once you confirm that all is working correctly, close the Userform and let’s move on to the next step.

Now that the ListBox is prepared, we need to configure the environment and write the code to retrieve the data from the database.  We will be using a SQL Server database and will be connecting from VBA via ADO.  At this stage, you’ll need to have created a database called “TestDB” and you will need to know the server name on which the database resides and how to build a connection string to connect.  On my end, I have created a test table called “large_test_set” with the following columns defined:

CREATE TABLE [dbo].[large_test_set](
    [price_date] [smalldatetime] NULL,
    [item_id] [varchar](10) NULL,
    [issuer] [varchar](100) NULL,
    [avg_mkt_price] [float] NULL,
    [avg_rating] [varchar](5) NULL,
    [dominant_industry] [varchar](100) NULL

My test table is currently populate with about 14,000 rows.

NOTE:  If you’re unclear about any of the database stuff, you can still follow along with the post to see how the ListBox is managed (which is really the purpose of the post), although you won’t actually be able to run it.  However, it is still probably worthwhile to continue reading.

With everything on the database side now prepared, let’s return to the VBA code.

  • Return to the VBA Editor.  On the menu bar, click Tools… References.  Locate “Microsoft ActiveX Data Objects 2.8 Library” (or 2.7, or 2.6, whatever the latest version of ADO is on your computer; any of the version from the last few years will work for this example).  Check off the ADO selection and click OK to close the dialog.

Now the entire ADO object model is available for use in the project.  Normally, at this point I would plug in one of my data access class modules and go.  But since this is not really a post about ADO or data access, we’re going to keep it very basic and just include all the data access code directly in the form module.

  • Open up the code module for frmSnappy and add the following constant and variable (remember to substitute your values in the connection string constant):
Private Const CONN_STR = "Provider=SQLNCLI10;Server=MyServer;Database=TestDB;Trusted_Connection=yes;"
Private mobjConn As ADODB.Connection
  • Next, add the following module level function to frmSnappy:
Public Function GetRecordsetToArray(SQLToExecute As String) As Variant
    'Load recordset data into an array and return the array to the calling routine.
    'If the recordset does not return any data, this function simply creates a
    '  single element array and populates it with  "No matching records...".
    Dim rst As ADODB.Recordset
    Dim arrData As Variant
    Dim lngX As Long

    On Error GoTo GRAError
    If mobjConn Is Nothing Then
        Set mobjConn = New ADODB.Connection
    End If
    mobjConn.ConnectionString = CONN_STR
    Set rst = New ADODB.Recordset
    With rst
        .CursorLocation = adUseClient
        .LockType = adLockReadOnly
        .Open SQLToExecute, mobjConn, adOpenStatic
        If Not (rst.EOF) Then
            'Disconnect the recordset
            .ActiveConnection = Nothing
            'Get the field count
            lngX = .Fields.Count
            'Load the array
            arrData = .GetRows()
            'Recordset is empty; create dummy array record
            ReDim arrData(0, 0)
            arrData(0, 0) = "No matching records found in the database"
        End If
    End With

        GetRecordsetToArray = arrData
        Set rst = Nothing
        Set mobjConn = Nothing
        Exit Function
        MsgBox "Error: " & Err.Number & vbCrLf & "Description: " & _
            Err.Description & vbCrLf & "Source: frmSnappy::GetRecordSetToArray", _
            vbCritical, "Database Error"
        Resume GRAResume
End Function

Note that this function uses the .GetRows() method of the ADO Recordset object.  GetRows() will take the Recordset data and put it directly into an array.  It’s super-fast and reliable.  Great.  Except for one last problem:  GetRows() load the array such that the first dimension of the array are the Fields/Columns and the second dimension of the array are the Rows.  Which is exactly the opposite of what our ListBox’s .List property is expecting.  So, if I were to code “lstSnappy.List = arrData”, it would not give me the results I’m looking for since the data would be transposed.

Now it’s no real difficult thing to write a routine that will transpose an array, but wouldn’t it be better if the ListBox just had a way of handling this?  Well, it does.  The .Column property.  This poorly documented property will accept an array as its value, transposing it automatically.  (I haven’t seen this usage of .Column documented anywhere, but I’m sure it is; I just haven’t looked hard enough.  But I think it’s safe to say that it isn’t an easy thing to find).

  • Finally, write code for the Command Button to execute the database query and population of the Listbox:
Private Sub cmdLoadList_Click()
Dim strSQL As String
Dim arrData As Variant
'Clear the list first
'Retrieve the data into the array
strSQL = "select price_date, item_id, issuer, "
strSQL = strSQL & "avg_mkt_price, avg_rating, "
strSQL = strSQL & "dominant_industry" & vbCrLf
strSQL = strSQL & "from large_test_set" & vbCrlf
strSQL = strSQL & "order by price_date DESC"
arrDat = GetRecordsetToArray(strSQL)

'Populate the ListBox.
'Transpose and load the array in a single statement.
lstSnappy.Column = arrData

End Sub

Now run your project.  Click the “Load List” command button and see how quickly the thousands of rows are loaded into your ListBox.  Looping through a Recordset and using AddItem on the ListBox will take considerably longer, and will require more code.  Hopefully, this post has been clear enough that you’re seeing the same results I am.  Experiment with other techniques, such as dumping the Recordset onto a worksheet and setting the RowSource property of the ListBox to point to this range.  In my experimentation, the .GetRows/Column method is still considerably faster (and not a kludge like some other methods.

Populated ListBox control

ListBox control populated with 14,000+ Rows


The combination of the .GetRows() method of the ADO Recordset, combined with the mysterious .Column property of the ListBox, provides an optimal solution for quickly loading a ListBox with large data sets.  This technique works with Excel 2003 and 2007, but I have not yet tested it with Excel 2010.  Soon.



Read Full Post »