VBA Has No Class




Recently, I was working on one of my apps, one that is database centric. Whilst making some changes, I came across this piece of code that inserts a new record into the database


    With RS

        .AddNew
        
        .Fields(FIELD_AUDIT_DATE) = sh.Cells(Rownum, COL_FU_AUDIT_DATE).Value
        .Fields(FIELD_CONSULTANT_ID) = GenerateID(FIELD_CONSULTANT_ID, Designer)
        .Fields(FIELD_SALES_TYPE_ID) = GenerateID(FIELD_SALES_TYPE_ID, sh.Cells(Rownum, COL_FU_SALES_TYPE).Value)
        .Fields(FIELD_CUSTOMER) = sh.Cells(Rownum, COL_FU_CUSTOMER).Value
        .Fields(FIELD_QUOTE_NUMBER) = QuoteNumber
        .Fields(FIELD_PRODUCT_ID) = GenerateID(FIELD_PRODUCT_ID, sh.Cells(Rownum, COL_FU_PRODUCT).Value)
        .Fields(FIELD_QUOTE_DATE) = sh.Cells(Rownum, COL_FU_QUOTE_DATE).Value
        .Fields(FIELD_QUOTE_AMOUNT) = sh.Cells(Rownum, COL_FU_QUOTE_AMOUNT).Value
        .Fields(FIELD_ESTIMATED_PROFIT) = sh.Cells(Rownum, COL_FU_PROFIT).Value
        .Fields(FIELD_REVISED_QUOTE) = sh.Cells(Rownum, COL_FU_REVISED_QUOTE).Value
        .Fields(FIELD_ACTUAL_PROFIT) = sh.Cells(Rownum, COL_FU_ACTUAL_PROFIT).Value
        .Fields(FIELD_STATUS_ID) = GenerateID(FIELD_STATUS_ID, sh.Cells(Rownum, COL_FU_QUOTE_STATUS).Value)
        .Fields(FIELD_DECLINED_ID) = GenerateID(FIELD_DECLINED_ID, sh.Cells(Rownum, COL_FU_DECLINED).Value)
        .Fields(FIELD_QUOTE_NOTES) = sh.Cells(Rownum, COL_FU_NOTES).Value
        
        .Fields(FIELD_UPDATED_BY) = ThisApp.LogonUser
        .Fields(FIELD_UPDATED_ON) = Format(Now, FORMAT_TIMESTAMPS_DB)
    
        .Update
    End With

Whilst looking at this code, for some reason my mind wandered to thinking about disconnected recordsets. Whilst most of my application involve database access, it is no longer on enterprise databases, so I don’t have the connection issues of high-end systems. As such, my use of disconnected recordsets is infrequent, my apps are safe in creating a user connection at logon, maintaining the connection throughout their session, and dropping at the end.


As an aside, I always use ADO in my applications. I found it easy to use and it performs fine for me. I have frequently been told that DAO performs better, usually by old Access’ers, but I have no issues with ADO, and will continue with it.


As often happens, my mind started wandering over this topic, forgetting what I was doing and thinking more about disconnected recordsets. I roamed on to thinking about collection classes. Collection classes are a very useful way of creating an in-memory dataset that can be manipulated by creating class methods, but they do require rather a lot of setup. In my musings, it occurred to me that I could use disconnected recordsets to achieve the same functionality, and use the builtin recordset functionality rather than creating my own methods.


Usually, a recordset would connect to a data source at some point, even a disconnected recordset, if only to get the data and/or write it back. It occurrs to me that this is not an absolute necessity, a recordset can be created and used without ever connecting to a data source, for instance where the data is maintained on a spreadsheet.


In this discussion, I will be working with a simple dataset as shown in Figure 1. 



Figure 1


In these examples, I am using late-binding, so I first declare some constants to emulate the ADO constants.


Enum ADOConstants
    adOpenStatic = 3
    adUseClient = 3
    adVarChar = 200
End Enum

Next, the data is extracted from the worksheet, and stored in an array.


Dim RSUsers As Object
Dim vecUsers As Variant
Dim Lastrow As Long
Dim i As Long, j As Long

    With ActiveSheet

        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        vecUsers = .Range("A1").Resize(Lastrow, 5)
    End With

The recordset is created, and because we are not retrieving the data from a data source (using ADO), we need to initialise the recordset by creating the columns.


    Set RSUsers = CreateObject("ADODB.Recordset")
    With RSUsers

        .Fields.append "FirstName", adVarChar, 25
        .Fields.append "LastName", adVarChar, 25
        .Fields.append "Gender", adVarChar, 1
        .Fields.append "Role", adVarChar, 25
        .Fields.append "Location", adVarChar, 25

        .CursorLocation = adUseClient
        .CursorType = adOpenStatic

        .Open

Note that you have to use a client side cursor.


Now that we have a defined recordset, we can load it with data. Each row of the array is iterated and a new record is added to the recordset, the fields are populated, and the recordset is updated.


    For i = 2 To Lastrow

        .AddNew
        .Fields("FirstName") = vecUsers(i, 1)
        .Fields("LastName") = vecUsers(i, 2)
        .Fields("Gender") = vecUsers(i, 3)
        .Fields("Role") = vecUsers(i, 4)
        .Fields("Location") = vecUsers(i, 5)
    Next i

    .Update

The recorsdet is now fully populated, contains all of the data, and so it is ready to use. To demonstrate this, I created a simply display function that outputs the recordset contents.


Private Function DisplayDetails( _
    ByVal RS As Object, _
    ByVal Title As String)
Dim msg As String
Dim i As Long
    
    With RS

        .MoveFirst
        Do Until RS.EOF
    
            msg = msg & "Name: " & RS.Fields("FirstName").Value & " "
            msg = msg & RS.Fields("LastName").Value & vbNewLine
            msg = msg & vbTab & "Role: " & RS.Fields("Role").Value
            msg = msg & "(" & IIf(RS.Fields("Gender").Value = "M", "Male", "Female") & ")" & vbNewLine
            msg = msg & vbTab & "Location: "
            msg = msg & RS.Fields("Location").Value & vbNewLine
            .MoveNext
        Loop
    End With

    MsgBox msg, vbOKOnly + vbInformation, Title
End Function

The first demo shows all of the rows, all of the columns.


    Call DisplayDetails(RSUsers, "List All Users")

We can also use the recordset Find function to find the rows that match the specified criteria. The criteria is based upon a column name.


    RSUsers.Find = "Gender = 'M'"
    Call DisplayDetails(RSUsers, "List All Users")

Find also can be used to skip rows, specify a start point, or set the search direction.


The recordset can be sorted,


    RSUsers.Sort = "Location"
    Call DisplayDetails(RSUsers, "List All Users Sorted By Location")

… or filter it.


    RSUsers.Filter = "Gender = 'M'"
    Call DisplayDetails(RSUsers, "List All Male Users")

You might wonder what is the difference between Find and Filter. Filter allows for multiple criteria, unlike Find.


    RSUsers.Filter = "Gender = 'M' AND Location Like 'P*'"
    Call DisplayDetails(RSUsers, "List All Male Users in P*")

As you can see, the disconnected recordset can do everything a collection class can do, but without having to hand-roll any of the methods, recordset has them builtin.


I think there is a killer usage for disconnected recordsets in Excel VBA apps, I just haven’t thought of it yet.

15 thoughts on “VBA Has No Class”

  1. Use .Provider = “MsDataShap” then fabricate using the slightly nicer syntax e.g. “SHAPE APPEND New adInteger AS stuff_id, New adVarChar(30) AS stuff_name, …” You can of course fabricate hierarchical recordsets, so you can model your whole object model in one single recordset :)

  2. I just read Dick Kusleika’s post at DailyDoseOfExcel and had to read the source for his post. I was impressed by Dick’s extension of the work he referred to your post.

    Of all the books I have read on Excel, I don’t recall even a mention of Recordsets. I have been working on some VBA code manage a company directory that is also accessed by several other spreadsheets.

    I can definitely appreciate the simplicity introduced by the record set to filter and sort the data. If this data can then be used to populate a userform’s list box or combobox, then I think an application has been determined.

    Thank you for sharing this! I’ve learned a lot during this visit.

  3. So i’ve been doing some testing. I have a spreadsheet with 29 columns and 31000 rows. If I use an array to populate the recordset, it takes >5 sec. Connecting to the workbook using ADO takes 140 ticks (using GetTickCount). This is huge!

    Problem is the internet is full of “ADO memory leak when connecting to open workbook” information. Using the recordset.open method I see no memory leak. Using the same worksheet referenced above I created/destroyed the recordset 1000 times in 141 seconds and my excel.exe memory went from 68,732 to 68,772 (it went up and down in between, never up more than a couple hundred K). Below is the sample code:

    Private Declare Function GetTickCount Lib “kernel32″ () As Long

    Public Sub simpletest()
    Dim dblCount As Double
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cnString As String
    Dim SQLString As String
    Dim lngTime As Long
    Dim lngTimeOverall As Long

    lngTimeOverall = GetTickCount

    Do Until dblCount = 1000

    lngTime = GetTickCount

    ‘Ccreate the connection object
    Set cn = New ADODB.Connection
    cn.Mode = adModeRead
    cnString = “Provider=Microsoft.ACE.OLEDB.12.0;” & “Data Source=” & _
    ActiveWorkbook.FullName & “;” & _
    “Extended Properties=””Excel 12.0;HDR=Yes;IMEX=1;”””
    cn.Open cnString

    ‘Create the recordset object
    Set rs = New ADODB.Recordset
    SQLString = “select * ” & “from ” & “[WIRELIST$]”
    rs.Open Source:=SQLString, ActiveConnection:=cn, _
    CursorType:=adOpenDynamic, LockType:=adLockOptimistic

    ‘Cleanup
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    Debug.Print dblCount & ” : ” & GetTickCount – lngTime
    dblCount = dblCount + 1
    DoEvents
    Loop

    Debug.Print “Overall: ” & (GetTickCount – lngTimeOverall) / 1000
    End Sub

    Does anyone see any problems with this? I would much rather use a connection than an array.

  4. Sorry, slight error. If you use the code above you will get no records in the recordset. I changed:

    Cursortype to adOpenStatic
    LockType to adLockUnspecified

    Times went down slightly, interestingly enough and the recordset gets fully populated.

  5. One problem with an active connection, I think, is that ADO is going to read from the disk. If the file is unsaved, any changes since the last save wouldn’t be reflected.

    I don’t use active connections on open workbooks because of the memory leak issue. I haven’t experienced it, but then I don’t use it enough to have. I don’t know what causes the memory leak, but I wouldn’t think it has anything to do with creating and destroying recordsets. It seems more likely it would have to do with the state of the file on the disk – lot’s of read/writes, changes/saves might cause it.

    That time difference is something. Thanks for doing the test.

  6. @Bjacobowski, I’ll see if I can knock up an item on data shaping, it would be interesting

    @Mike, most people miss my posts. IU only seem to pick up any interest when Dick steals my ideas. Maybe I should commission him to pick up on every item. We know what floats Dick’s boats, so he should be easy to buy.

    @Dick, Thanks, where would my blog be without you?

    @Redge Semplonius, Recordsets are not part of Excel, that is why you you won’t see it in the Excel books. They are a feature of data access layers such as DAO and ADO, and it is just another strength of VBA that you have access to other libraries/technologies.

  7. Bob, I was going crazy trying to figure out what I was doing wrong w/ the Find method b/c it kept returning the entire recordset.

    Find doesn’t alter the recordset, it moves the record pointer to the first matching record from the current record (can search forward or backward).

    Filter alters the recordset by hiding records that don’t match the criteria. Records can be subsequently unhidden by removing the filter:
    RS.Filter = 0

  8. Finally got a chance to try out MSDataShape this week – works extremely well for aggregating a flat file into a hierarchy. 2 things I found when connecting to an open wkb w/ ADO:

    -Memory leak is real in Excel 2003
    -Does not read from saved file – ie if you change something then run w/o saving, the change is picked up

  9. So which is faster dictionary or recordset? (Explanation below if you want to read that much). While I have your attention is there a way to do webqueries with ADO? I haven’t been able to find it online, of course, I picked up “Professional ADO 2.5″ and that’s been a huge help, I now know what the posters are talking about when they say shaped recordsets!

    I’m grabbing data from a open workbook and putting it in a class using dictionaries, so I wouldn’t be able to grab the data directly from the workbook (memory leak). I have to manipulate the data since some of it comes in the form of comma delimiters (from a webquery). After I split up the data I then reference the data with my class. It takes about a 1/2 second to get the data then to reference the data it takes up to a 1/2 second.

    I would be curious to know if recordsets would work faster than the dictionaries. Not sure exactly how I would do it since some of the entries are in an array and the number of entries vary depending on which one I’m referencing.

  10. Can this method be used to hold a disconnected recordset in memory to be accessed by other functions and subs whilst the workbook remains open?

    ie, follow the above, and then go about using the worksheet to do ‘work’, then click a button and get it retrieve records?

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>