Readability with Custom Classes

Posted on by Dick Kusleika

I was reviewing some old code recently that uses a two-dimensional array to store some data. Figuring out what was in the array, then remembering what was in the array as I went through the code, was the biggest challenge. It seemed to me that a custom class would have made the code easier to understand. Here’s a snippet of the old code:

    ReDim aSolutions(1 To 3, 1 To lSolCnt)
   
    aSolutions = GetSolData
   
    For i = LBound(aSolutions, 2) To UBound(aSolutions, 2)
        rCell.Offset(lRow, 0).Value = aSolutions(1, i)
        rCell.Offset(lRow, 1).Value = aSolutions(2, i)
        rCell.Offset(lRow, 2).Value = 100 / aSolutions(3, i) * lFACTOR
    Next i

Here’s what that would look like if I had created a class:

    Set colSolutions = GetSolData
   
    For i = 1 To colSolutions.Count
        Set clsSol = colSolutions(i)
       
        rCell.Offset(lRow, 0).Value = clsSol.SolutionID
        rCell.Offset(lRow, 1).Value = clsSol.LabID
        rCell.Offset(lRow, 2).Value = 100 / clsSol.Reading * lFACTOR
    Next i

This example may be a bit oversimplified, but it makes the point. There’s no way I’ll remember what aSolutions(2,i) is, but there’s at least a chance I’ll know what clsSol.LabID is. I’m reusing some of this code on a new project, and I’m getting rid of the multi-dimensional arrays and replacing them with collections of custom classes. So much for the efficiency of code reuse.

Posted in Uncategorized

8 thoughts on “Readability with Custom Classes”

  1. Makes sense to me. I tend to find once I’ve got to having classes, that I can then move the outputting part into the class. So your example above might become

    For i = 1 To colSolutions.Count
    Set clsSol = colSolutions(i)
    clsSol.OutputTo rCell.Offset(lRow,0)
    Next

    … with the class getting

    Public Sub OutputTo(target As Range)
    target.Offset(lRow, 0).Value = SolutionID
    target.Offset(lRow, 1).Value = LabID
    target.Offset(lRow, 2).Value = 100 / Reading * lFACTOR
    End Sub

    I don’t know what scope lFACTOR has – that might need to be passed in. Or the class might get another, suitably informatively named function that returns the computation value.

    Be careful with large (thousands) collections of Class instances, by the way – destroying them takes significant time. If you don’t need keyed lookup, use an array. Actually, use an array anyway, and store the array indices in a collection – collections of simple types don’t have the costly destruction problem.

  2. Switching to a class definitely makes for nicer, cleaner code. In cases where I’ve felt the need to leave things in arrays, however, one thing I’ve found that can help with the readability issue is to set up constants for the columns (or rows, depending on how you’re using the array) in the array. With a constant definition like:

    Const cLabIdIndex = 2

    The array reference can change from, for example:

    rCell.Offset(lRow, 1).Value = aSolutions(2, i)

    to:

    rCell.Offset(lRow, 1).Value = aSolutions(cLabIdIndex, i)

    Not as elegant as a class, certainly, but readable enough to make sense of when you look at it a year later.

    SMS

  3. A quick and dirty improvement over your original code that doesn’t require custom classes is to just use constants for the array positions. For example:

        Const SolData_SolutionID = 1
        Const SolData_LabID = 2
        Const SolData_Reading = 3

        ReDim aSolutions(1 To 3, 1 To lSolCnt)
       
        aSolutions = GetSolData
       
        For i = 1 To UBound(aSolutions, 2)
            rCell.Offset(lRow, 0).Value = aSolutions(SolData_SolutionID, i)
            rCell.Offset(lRow, 1).Value = aSolutions(SolData_LabID, i)
            rCell.Offset(lRow, 2).Value = 100 / aSolutions(SolData_Reading, i) * lFACTOR
        Next i

  5. I would normally use a Type in this case, which is just a codeless class

    However, a comment on the end of the line could be the clearest
    rCell.Offset(lRow, 0).Value = aSolutions(1, i) ‘solution ID
    rCell.Offset(lRow, 1).Value = aSolutions(2, i) ‘Lab ID
    rCell.Offset(lRow, 2).Value = 100 / aSolutions(3, i) * lFACTOR ‘Value

    (Also, I try not to write back to cells one at a time, although I realise that’s not the point you’re trying to make)

  6. Hello Dick

    I’m sorry my query will not be related to your post.

    A few weeks ago I had written my problem to one of the gentleman in your group. The problem was to convert some date data into more easily understood date format (11206 to be converted to 1 December 2006 or 2102007 to 21 Feb 2007) and I had the solution almost straight away. But I lost it. It is embarrassing for me but unfortunately true and further I forgot the name of the gentleman, shame on me.

    How can I find my comment in your system?

    Thanks for your help.

    PS Am I going to be banished and sentenced to forced exile from your group? :-)

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.