In this post we will be demonstrating how using class modules can make your arrays more functional and easier to read!
In the first post we explained how class modules change the way you think about programming. You are now focusing on objects rather than actions. When dealing with arrays that require multiple data types, this idea will become even more apparent and will increase your understanding of class modules further.
Note: This post assumes you have a good understanding of arrays. If you would like to learn more about arrays, please read these posts:
Working With Arrays 1
Working With Arrays 2
Arrays in MS Access allow us to create lists of data in memory which we are then able to manipulate through code. They are similar in design to tables but don’t take a physical form. One of the main issues with arrays is that we often need to store data for multiple data types but primitive arrays only allow you to store one data type. So, what do we do?
We use strings!
Let’s take a look…
An Array Of Strings
In the provided database we want to create an array based off of data in the Films table. The films table holds data such as ID (long), FilmName (text) and RottonTomatoRating (double) but we are only going to use one array.
Create a standard module called modArrayOfStrings and add this code to it:
Public Sub subArrayOfStrings1()
On Error GoTo ErrorHandler
Dim myArray() As String
Dim i As Integer
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iRND As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Films")
With rs
'ensure recordset is populated
If Not .BOF And Not .EOF Then
'populate recordcount
.MoveLast
.MoveFirst
'set size of array to the number of records in Films
ReDim myArray(0 To .RecordCount - 1, 0 To 4)
For i = LBound(myArray) To UBound(myArray)
myArray(i, 0) = .Fields("ID")
myArray(i, 1) = .Fields("FilmName")
myArray(i, 2) = .Fields("YearOfRelease")
myArray(i, 3) = .Fields("RottenTomatoes")
myArray(i, 4) = .Fields("DirectorId")
.MoveNext
Next i
End If
End With
'code to generate a random number to test the array
iRND = Int((UBound(myArray) - LBound(myArray) + 1) * Rnd)
Debug.Print "ID: " & myArray(iRND, 0)
Debug.Print "FilmName: " & myArray(iRND, 1)
Debug.Print "YearOfRelease: " & myArray(iRND, 2)
Debug.Print "RottonTomatoes: " & myArray(iRND, 3)
Debug.Print "DirectorID: " & myArray(iRND, 4)
Debug.Print ""
ExitSub:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Resume ExitSub
End Sub
If you call it multiple times from the immediate window by using this code,…
call subArrayOfStrings1
…you will get output similar to this…
ID: 7 FilmName: Jaws YearOfRelease: 1975 RottonTomatoes: 0.98 DirectorID: 4 ID: 6 FilmName: 12 Years A Slave YearOfRelease: 2014 RottonTomatoes: 0.96 DirectorID: 5 ID: 8 FilmName: The Dark Knight YearOfRelease: 2008 RottonTomatoes: 0.94 DirectorID: 3
We are opening up the Films table, creating an array of strings and adding the relevant data to the relevant element.
The Explanation
Public Sub subArrayOfStrings1
The name of the sub-procedure is subArrayOfStrings1.
On Error GoTo ErrorHandler
Choosing to handler errors.
Dim myArray() As String Dim i As Integer Dim rs As DAO.Recordset Dim db As DAO.Database Dim iRND As Integer
We are declaring variables that we will need in order for the sub-procedure to function.
Set db = CurrentDb
Set rs = db.OpenRecordset("Films")
We instantiate the db and rs variables. Open up table Films as a recordset.
With rs 'ensure recordset is populated If Not .BOF And Not .EOF Then 'populate recordcount .MoveLast .MoveFirst ... End With
Standard code to ensure the recordset and recordcount properties are populated.
'set size of array to the number of records in Films ReDim myArray(0 To .RecordCount - 1, 0 To 4)
Earlier in the module, we declared an array and called it myArray. We didn’t, however, say how big we wanted it. Arrays have to be a fixed size so in the code snippet above we are letting Access know that we want an array of elements that is equal to the number of records in the Films table.
For i = LBound(myArray) To UBound(myArray)
myArray(i, 0) = .Fields("ID")
myArray(i, 1) = .Fields("FilmName")
myArray(i, 2) = .Fields("YearOfRelease")
myArray(i, 3) = .Fields("RottenTomatoes")
myArray(i, 4) = .Fields("DirectorId")
.MoveNext Next i
In this code snippet, we are looping through the Films recordset, adding the data from the records to myArray as we go. LBound and UBound refer to the lower and upper bounds of the array respectively.
'code to generate a random number to test the array iRND = Int((UBound(myArray) - LBound(myArray) + 1) * Rnd) Debug.Print "ID: " & myArray(iRND, 0) Debug.Print "FilmName: " & myArray(iRND, 1) Debug.Print "YearOfRelease: " & myArray(iRND, 2) Debug.Print "RottonTomatoes: " & myArray(iRND, 3) Debug.Print "DirectorID: " & myArray(iRND, 4) Debug.Print ""
In the above code snippet, we use a function to generate a random number for testing purposes only. iRND will be equal to a number between the lower and upper bounds of the array. We then proceed to print information about the array to the immediate window. This works fine but it isn’t obvious which field of data myArray(iRND, 1) (for example) refers to. It is the FilmName field, as it happens.
So, we have seen that you can use an array of strings to load and print out information about records in a table to the immediate window. But, is there a better way?
Of course!
An Array Of Objects
In the previous example we used an array of primitive data types to work with our data. It worked fine but we can in fact use an array of objects that we create ourselves. Let’s take a look.
Create a Class Module called clsFilmArray and add this code to it:
Private m_ID As Long Private m_FilmName As String Private m_YearOfRelease As String Private m_RottenTomatoes As Double Private m_DirectorID As Long ''''''''''''''''ID'''''''''''''''''''''' Public Property Get ID() As Long ID = m_ID End Property Public Property Let ID(value As Long) If Not IsNull(value) Then m_ID = value End If End Property ''''''''''''''''FilmName'''''''''''''''''''' Public Property Get FilmName() As String FilmName = m_FilmName End Property Public Property Let FilmName(value As String) If Not IsNull(value) Then m_FilmName = value End If End Property ''''''''''''''''YearOfRelease''''''''''''''' Public Property Get YearOfRelease() As String YearOfRelease = m_YearOfRelease End Property Public Property Let YearOfRelease(value As String) If Not IsNull(value) Then m_YearOfRelease = value End If End Property ''''''''''''''''RottenTomatoRating'''''''''' Public Property Get RottenTomatoes() As Double RottenTomatoes = m_RottenTomatoes End Property Public Property Let RottenTomatoes(value As Double) If Not IsNull(value) Then m_RottenTomatoes = value End If End Property ''''''''''''''''DirectorID'''''''''''''''''' Public Property Get DirectorID() As Long DirectorID = m_DirectorID End Property Public Property Let DirectorID(value As Long) If Not IsNull(value) Then m_DirectorID = value End If End Property
This code is very simply a list of private variables (instance variables) and get and let methods to access them. There are no procedures or functions doing any calculating; this is (almost) as simple as a class module can get. But how do we test it? Create a standard module called modArrayClass and add this code:
Public Sub subArrayOfStrings1()
On Error GoTo ErrorHandler
Dim myArray() As clsFilmArray
Dim i As Integer
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim iRND As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Films")
With rs
'ensure recordset is populated
If Not .BOF And Not .EOF Then
'populate recordcount
.MoveLast
.MoveFirst
'set size of array to the number of records in Films
ReDim myArray(0 To .RecordCount - 1)
For i = LBound(myArray) To UBound(myArray)
'instantiate the array
Set myArray(i) = New clsFilmArray
myArray(i).ID = .Fields("ID")
myArray(i).FilmName = .Fields("FilmName")
myArray(i).YearOfRelease = .Fields("YearOfRelease")
myArray(i).RottenTomatoes = .Fields("RottenTomatoes")
myArray(i).DirectorID = .Fields("DirectorId")
.MoveNext
Next i
End If
End With
'code to generate a random number to test the array
iRND = Int((UBound(myArray) - LBound(myArray) + 1) * Rnd)
Debug.Print "ID: " & myArray(iRND).ID
Debug.Print "FilmName: " & myArray(iRND).FilmName
Debug.Print "YearOfRelease: " & myArray(iRND).YearOfRelease
Debug.Print "RottonTomatoes: " & myArray(iRND).RottenTomatoes
Debug.Print "DirectorID: " & myArray(iRND).DirectorID
Debug.Print ""
ExitSub:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Resume ExitSub
End Sub
This code doesn’t differ wildly from the code in the first module we created. But trust us, it does differ!
Calling subArrayOfStrings1 from the immediate window gives us identical functionality:
ID: 8 FilmName: The Dark Knight YearOfRelease: 2008 RottonTomatoes: 0.94 DirectorID: 3 ID: 4 FilmName: The Shining YearOfRelease: 1980 RottonTomatoes: 0.92 DirectorID: 2 ID: 4 FilmName: The Shining YearOfRelease: 1980 RottonTomatoes: 0.92 DirectorID: 2 ID: 9 FilmName: 2001 A Space Odyssey YearOfRelease: 1968 RottonTomatoes: 0.97 DirectorID: 2
So, how does it work?
The Explanation
First let’s take a look at the class module.
Private m_FilmName As String
''''''''''''''''FilmName''''''''''''''''''''
Public Property Get FilmName() As String
FilmName = m_FilmName
End Property
Public Property Let FilmName(value As String)
If Not IsNull(value) Then
m_FilmName = value
End If
End Property
As stated, we are only using the class module to provide some instance variables to work with. We actually do not need to go as far as we did. The code below would server the same purpose on its own:
Public m_ID As Long Public m_FilmName As String Public m_YearOfRelease As String Public m_RottenTomatoes As Double Public m_DirectorID As Long
But we like to keep our instance variables private and have access to them through properties (much more secure that way).
Public Property Get FilmName() As String FilmName = m_FilmName End Property
These 3 lines of code let us retrieve the value of m_FilmName. Simple, right?
Public Property Let FilmName(value As String)
If Not IsNull(value) Then
m_FilmName = value
End If
End Property
These 4 lines of code let us modify the value of m_FilmName. Simple again, right?
That is all there is to this class module. A simple module that let’s us update and retrieve values of instance variables. What is really interesting is how the standard module interacts with it.
Public Sub subArrayOfStrings1()
...
Dim myArray() As clsFilmArray
...
With rs
...
'set size of array to the number of records in Films
ReDim myArray(0 To .RecordCount - 1)
For i = LBound(myArray) To UBound(myArray)
'instantiate the array
Set myArray(i) = New clsFilmArray
myArray(i).ID = .Fields("ID")
myArray(i).FilmName = .Fields("FilmName")
myArray(i).YearOfRelease = .Fields("YearOfRelease")
myArray(i).RottenTomatoes = .Fields("RottenTomatoes")
myArray(i).DirectorID = .Fields("DirectorId")
.MoveNext
Next i
...
End With
...
Debug.Print "ID: " & myArray(iRND).ID
Debug.Print "FilmName: " & myArray(iRND).FilmName
Debug.Print "YearOfRelease: " & myArray(iRND).YearOfRelease
Debug.Print "RottonTomatoes: " & myArray(iRND).RottenTomatoes
Debug.Print "DirectorID: " & myArray(iRND).DirectorID
Debug.Print ""
...
End Sub
A lot of the code above is very similar to the example for the array of strings. We will highlight and explain the differences caused by using a class module.
Dim myArray() As clsFilmArray
Rather than declare the array as a primitive data type (String), we instead prefer to declare it as an array of objects (clsFilmArray) that we created.
ReDim myArray(0 To .RecordCount - 1)
An array of objects can be redimensioned (redeclared) in exactly the same way as an array of strings.
For i = LBound(myArray) To UBound(myArray) ... Set myArray(i) = New clsFilmArray ... Next i
Although we have declared myArray to be an array of objects, we still need to instantiate (bring to life) these objects. Each element of the array requires a separate instantiation and we are achieving this with a for loop.
myArray(i).ID = .Fields("ID")
Now we can really see the advantage of using an array of objects. This code writes the value of the field ID to the ID property of the class and it is very clear as to what field we are writing to.
Debug.Print "ID: " & myArray(iRND).ID
Along the same lines, when reading values from the array, we refer to the exact field we need, rather than a number, to get the value we want.
Conclusion
Using an array of objects in place of an array of strings means your code will be more readable and easier to debug. Arrays of objects are also an excellent way to get familiar with the concept of object-based programming.
