Thursday, November 22, 2012

Create a basic class in Excel

Create a basic class in Visual Basic Excel


You have heard of class in programming including in VBA or VB. But never had you tried to use them because you are afraid to mess your program. In fact Class is very simple to use and understand. Many sites explain class correctly and surely better than me.

If you started to program in MS-Excel, you never really use them and are perfectly normal. You program function directly to operate instructions over instructions. When you program in VBA work with a lot of data, you cycle over a range of cells or loop over an array of cells or data. That data might be a number, a date or a word.

Here, I will not try to change you habits. I will just simple point out how a simple class might help you a lot.
First lets try to visualize this excel sheet. Sorry if the image is in French.

Excel

In the VBA module, here is the code. There will be 2 procedures. The first one doesn’t use any class. The second do the same thing than the first one except it use classes to store the data. The procedures are very stupid and do nothing incredible. Yes you could surely to thing better that is not the point.

If you take a look carefully at the first test procedure, you will notice I will do 2 loops. The first loop, I will read all the value for each row and each column. While I am looping the cells, I store (save) the data in a string variable. At the end, I display the information. OK.

But what happen if you want to read a value again? You will have to go over all again the 2 loops. Of course, you will find your value, but manipulating cells are heavy. Your sample is very small. The moment you Excel sheet is bigger, your program is not be powerful. Yes, small code offends means cheap and simple.





Also, if you need to get an information, of course you have to find it but the information is in a 2D cell and again, is not always easy to look for the information when is not stored in a single group. The moment you have to different table, you are dead.


Public Sub test()
    Dim index1 As Integer
    Dim oRange As Range
    Dim str As String
   
    
    Set oRange = Range("A1", "C6")
    str = ""
   
    For index1 = 1 To oRange.Rows.Count
        For index2 = 1 To oRange.Columns.Count
            str = str & oRange.Cells(index1, index2) & vbCrLf
        Next
        str = str & "======================" & vbCrLf
    Next
    MsgBox str
End Sub



So before we take a look at the second function, we have to take a look at the class.
See how simple is your class. We simple put the names of the information you wish to gather together


Basic Class in VBA Excel

The class has only 3 lines. Of course you could all the lines you need if is part of your class.

When you need to use it, it might look to something like this:




Public Sub test2()
    Dim index1 As Integer
    Dim oRange As Range
    Dim str As String
    Dim oClass1 As Classe1
    Dim oEmployee As Collection
    Set oEmployee = New Collection
   
    Set oRange = Range("A1", "C6")
    str = ""
   
    For index1 = 1 To oRange.Rows.Count
        Set oClass1 = New Classe1
        oClass1.Name = oRange.Cells(index1, 1)
        oClass1.Salary = oRange.Cells(index1, 2)
        oClass1.hireDate = oRange.Cells(index1, 3)
        oEmployee.Add oClass1
    Next

    For index1 = 1 To oEmployee.Count
        Set oClass1 = oEmployee.Item(index1)
        str = str & oClass1.Name & vbCrLf
        str = str & oClass1.Salary & vbCrLf
        str = str & oClass1.hireDate & vbCrLf
        str = str & "======================" & vbCrLf
    Next
    MsgBox str
   
End Sub



Please notice this, there is 2 loops. Honestly, I tried not to change to much from the first procedure. But think about this. You worked a little bit more and created a little bit more code but instead of manipulating the cells directly, you save the information in a collection of class. Each class represents an employee.

MsgBox So if you need to locate an employee, as soon you find you class base on the Name, the HireDate or the Salary in the collection, you will automatically get the rest of the data. You don’t have to care of the table range or the column number or the line number. Is not important if some data if placed elsewhere in your sheet or worksheet?

Also, believe me. If you take this good habit, you will notice that the loop will run faster. Yes, manipulating small object is lighter. Playing with 3 words is lighter than 3 cells (because of the text style, graphic…)



Download the sample used in this post: Classeur1.xlsm (Microsoft Excel 2010)
Visit my web site on: http://checktechno.ca/en
Please comment or share if you like this post .


If you like this post, leave a comment or share it.

No comments:

Post a Comment