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.
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.
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
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.
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.
