Category Archives: 11782

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.

Class Conscious

































Normal















0















































false















false















false































































































































MicrosoftInternetExplorer4


































































































 

This is going to be the first in a series of items about Code Generation, automatically generating your code. I have posted a couple of times previously on this topic, here and here, but I thought a series of posts would be useful, so here is the first.

The Last One

I remember back in the 80s a UK company built a program called ‘The Last One’, its premise being that it was the last computer program because it would write every program from thereon after. Now, after you have picked yourself up off of the floor and stopped laughing, think about it somewhat objectively. Remember, this was in the days of largely batch programs, they started at the beginning, went through a file of instructions or updates, and reacted to each item encountered. As such, with some very clear rules, it shouldn’t be too hard to write a program to read those rules and then process the data files according to those rules. Of course, it would all soon get messy, the types of rules are infinite, and the types of data is also infinite, so some severe constraints would have to be imposed, but wouldn’t a program that can handle multiple applications, even if those applications have to adapt somewhat to that program, be beneficial (of course, you could say this is exactly the same as the SAP paradigm, but please, let’s not talk about SAP when I am feeling good). Event driven applications add a whole new, more difficult dimension of course, but still…

Automate It

I am a great fan of code generation, using it frequently, and always look for further opportunities to use it. Why do I think it is so good? There are many reasons:

  • maintenance is reduced, make a small change and rerun and all code affected by that change will be re-generated
  • the quality is improved, all changes are re-generated, no need to remember what needs changing
  • the work is done in the more critical and interesting areas, in analysis and design.

To me that third point is the most important. Code generation makes you think about the application requirements and how they should be implemented, something you should do by right, but something that cannot be avoided or done half-heartedly when using  code generation. As the design is so critical, and as I find it more interesting than cutting code, this suits me perfectly.

The greatest exponent of code generation that I am aware of is Kathleen Dollard. Her book, Code Generation in Microsft .NET, is the definitive work on the subject, a remarkably comprehensive work that provide end-to-end solutions using XSLT-based code generation based upon a series of templates. As she says … Code generation will turbo-charge your development cycles by offering speed, reusability, agility, and consistency.

Whilst this is a remarkable piece of work, I think that many might find it daunting, even off-putting. The solution is comprehensive, but it is based upon .NET and XML, and dare I say, can even been too purist for the tyros amongst us. For this reason, I will address code generation from a much simpler starting point, show how you can use code generation today using Excel, and build upon it over the next few weeks,

Excel & Code Generation

What I have described so far are full-blown applications, with their attendant complexity, which might discourage you from going any further.  But let’s step back a bit, and think about ways in which we can generate code in small doses. Later items will go further, but we can show immediate benefits. In these examples, we will use Excel as the code generating engine. Excel is a superb tool for this, as it is with so many other things, with the grid for our data input, and VBA to handle any complex code generating algorithms.

You may not be aware of it, but maybe you are already using Excel to do code generation. If you ever have a list in Excel, and use formulas and functions to create some more descriptive text that you cut and paste into a code module, that is a simple form of code generation.

Let’s take a simple example. I use class modules a lot, and I find that creating properties is a pain, you need to define a private variable where the actual value is stored, and then Get and Let properties so that the property can be read from and written to in the class (that is assuming two things, your property is read/write, they may not all be so; that you don’t use Public variables for read/write properties – I don’t, I always declare Get and Let).

Using code generation, all we need are two pieces of information, the property name and its data type. If we want to cater for some properties being read only, we need a third denoting the access mode.

So, we can create a spreadsheet with 3 columns

-          column A – property name – free format text

-          column B – data type – Long, Double, String,
Boolean, Range (should suffice for now) – an ideal candidate for a data
validation list

-          column  C – access mode – R, W, or RW – again another dv list

It would be wise to add a fourth, column D, derived column, that takes the property name and replaces any spaces with _, and removes any special characters, as this will be used in the class.

With a few simple formulae, we can easily generate all 3 elements of the property that the class needs,

-          Private
Variable

=”Private m_”&D2&” As “&D2

-          Read
Property

=IF(ISNUMBER(SEARCH(“R”,$C2)),
“Public Property Get “&$D2&”() As ” &$B2&CHAR(10)&
    “&$D2&” = m_”&$D2&CHAR(10)&”End Property”,
“”)

-          Write Property


=IF(ISNUMBER(SEARCH(“W”,$C2)),
“Public Property Let “&$D2&”() As ” &$B2&CHAR(10)&
”   m_ “&$D2&” = “&$D2&CHAR(10)&”End Property”,
“”)


Correction 


=IF(ISNUMBER(SEARCH(“W”,$C2)),
“Public Property Let “&$D2&”(ByVal val As ” &$B2&”)”&CHAR(10)&
”    m_”&$D2&” = val”&CHAR(10)&”End Property”,
“”)


 


That is all there is to it. Create a list of properties with their attributes, and Excel generates the class code for you, all you need to do is cut and paste it into the class module.


Next time, we will show how to use VBA to write back this data into the class code, after all, one of the primary aims of code generation is to save us from the boring bits. We will also look at  a slightly more complex generation.


 


For RSS subscribers, this code is attached.