Building a basic class in VBA based on data in a spreadsheet.


, , , , , , , , , , , , , , , ,

I would like to use one of my answers on SO as a tutorial-ish resource for people who have never worked with classes in VBA. I will show you which module to insert and how to write code to access the structurally stored data.

Say your spreadsheet looks kind of like this:

enter image description here

When you open Visual Basic Editor ALT+F11 you need to right click your VBA project and insert 2 modules: one standard Module1 and one Class Module.

A class is a like a blue-print. It’s like a “template” for the data you want to store in it. We want each instance of our class to have its own separate properties : Id, Name, Math, Science, English and History and I have added a public function which returns the average of all the scores. I will show you later on how to “use” the properties and function.

Rename the class module to Student and populate it with

Public Id As Long
Public Name As String
Public Math As Long
Public Science As Long
Public English As Long
Public History As Long

Public Function GetAverage()
    GetAverage = (Math + Science + English + History) / 4
End Function

* Here we have got 6 public variables – you call them properties when they are declared public within a class module. Each instance of this class will have its own separate 6 properties – as you have probably noticed they do somehow match the headers on the spreadsheet and that’s exactly the point. By declaring them public – anyone who can access the class instance can both read and write (ie. modify) their values. This is a really basic setup just to show you how classes in VBA work.

See Difference Between Public Variable and Class Property in a VBA class for details.

Now, Module1 should contain the below code

 Sub Main()

   Dim students As New Collection ' collection to store all students

   Dim cell As Range ' iterator
   Dim person As Student

   ' for each cell in column A
   For Each cell In Sheets(1).Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
       ' create a new student object
       Set person = New Student
       ' fill the data
       person.Id = cell
       person.Name = cell.Offset(0, 1)
       person.Math = cell.Offset(0, 2)
       person.Science = cell.Offset(0, 3)
       person.English = cell.Offset(0, 4)
       person.History = cell.Offset(0, 5)

       ' add the student to the collection of students
       students.Add person

   ' open Immediate Window CTRL + G or VIEW -> Immediate Window
   Dim s As Student
   For Each s In students
       Debug.Print "ID: " & s.Id & vbTab & "Name: " & s.Name, "Math: " & s.Math, "Science: " & s.Science, "English: " & s.English, "History: " & s.History, "Avg: " & s.getAverage()

End Sub

In the above snippet, we have create a new Collection object(students) to store our students Names, their IDs and their scores. We then iterate over all the students on the spreadsheet and for each student we create a class – a blue-print to store each student’s information in one place.

Let’s see this at work

Now if you run F5 your macro and check out your Immediate Window you will have all the data from your spreadsheet loaded programmatically in to a collection;

enter image description here



Get every new post delivered to your Inbox.