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:
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 Next ' 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() Next 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;