The Model View Controller (MVC) design pattern is used within software development to organize application code into separate layers in order to make it easier to maintain and support. The key idea is to have an application whose parts are not tightly coupled together, so that changes in one particular area do not affect other non-related parts of the application. For Excel applications that have the potential to scale, adopting an MVC approach might be worth considering.
Design Pattern Layers
Implementing the pattern involves organizing the code into the following three separate logical layers:
- A Model layer is used to represent the data in an application and handle its retrieval from external sources such as a database. It does not directly talk to the view layer.
- A View layer handles the interaction with the user and the frontend controls. It does not directly talk to the model layer, but knows it needs to update itself when changes occur in the data model.
- A Controller Layer handles the interaction between the Model and View layers.
Building an MVC Excel Application
To demonstrate how the MVC approach can be applied to building applications in Excel, we are going to walkthrough building a simple HR application. The app will allow a user to find and display employee records, as well as edit an employee’s details. To keep things simple the data will be stored on a separate worksheet tab rather than a database to keep the focus on the pattern. The finished application will consist of three classes for each MVC layer. The architecture of the application will look like so:
The screenshot below shows what the worksheet frontend will look like to the user. The buttons labelled “Find Record” and “Save Record” will be assigned the event actions btnFind() and btnUpdate() respectively. You can see these in our Worksheet class in our architecture diagram.
And this is what our underlying dataset looks like:
Model Layer Class
In our application, the model layer will consist of a class representing an employee. The class will have attributes that match the fields in our dataset for employees. These will be employee number, firstname, lastname, gender and jobtitle. In addition to this, it will have methods to retrieve and update the data for an employee. The code for Employee class (csModelEmployee) is presented below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
Option Explicit Private mstrFirstName As String Private mstrLastName As String Private mstrGender As String Private mstrJobTitle As String Private mwsData As Worksheet Private mrngIds As Range Private mintEmployeeNo As Integer Public Property Get employeeNo() As Integer employeeNo = mintEmployeeNo End Property Public Property Let employeeNo(ByVal value As Integer) mintEmployeeNo = value End Property Public Property Get firstName() As String firstName = mstrFirstName End Property Public Property Let firstName(ByVal value As String) mstrFirstName = value End Property Public Property Get lastName() As String lastName = mstrLastName End Property Public Property Let lastName(ByVal value As String) mstrLastName = value End Property Public Property Get gender() As String gender = mstrGender End Property Public Property Let gender(ByVal value As String) mstrGender = value End Property Public Property Get jobTitle() As String jobTitle = mstrJobTitle End Property Public Property Let jobTitle(ByVal value As String) mstrJobTitle = value End Property Public Function UpdateEmployeeRecord(ByVal employee As csModelEmployee) As Boolean Dim rngFind As Range UpdateEmployeeRecord = False Set rngFind = mrngIds.Find(What:=employee.employeeNo, LookIn:=xlValues, LookAt:=xlWhole) If Not rngFind Is Nothing Then With mwsData .Cells(rngFind.Row, 1).value = employee.employeeNo .Cells(rngFind.Row, 2).value = employee.firstName .Cells(rngFind.Row, 3).value = employee.lastName .Cells(rngFind.Row, 4).value = employee.gender .Cells(rngFind.Row, 5).value = employee.jobTitle End With UpdateEmployeeRecord = True End If Set rngFind = Nothing End Function Public Function GetEmployeeRecord(ByVal intId As Integer) As Boolean Dim rngFind As Range GetEmployeeRecord = False Set rngFind = mrngIds.Find(What:=intId, LookIn:=xlValues, LookAt:=xlWhole) If Not rngFind Is Nothing Then With mwsData Me.employeeNo = .Cells(rngFind.Row, 1).value Me.firstName = .Cells(rngFind.Row, 2).value Me.lastName = .Cells(rngFind.Row, 3).value Me.gender = .Cells(rngFind.Row, 4).value Me.jobTitle = .Cells(rngFind.Row, 5).value End With GetEmployeeRecord = True End If Set rngFind = Nothing End Function Private Sub Class_Initialize() Dim llastRow As Long Set mwsData = ThisWorkbook.Worksheets("Data") llastRow = mwsData.Cells(mwsData.Rows.Count, "A").End(xlUp).Row Set mrngIds = mwsData.Range("A1:A" & llastRow) End Sub Private Sub Class_Terminate() Set mwsData = Nothing Set mrngIds = Nothing End Sub |
View Layer Class
The view layer will handle all interaction with the frontend of the application. It will retrieve input from the user and display data that it gets sent to it by the Controller. The following code represents the view layer class (csViewUI) and outlines its behaviour in this regard:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
Option Explicit Private mwsUserInterface As Worksheet Public Sub ClearEmployeeDisplay() Application.ScreenUpdating = False With mwsUserInterface .Range("B2").value = Empty .Range("B4").value = Empty .Range("B5").value = Empty .Range("B6").value = Empty .Range("B7").value = Empty End With Application.ScreenUpdating = True End Sub Public Sub DisplayEmployeeRecord(employee As csModelEmployee) Application.ScreenUpdating = False With mwsUserInterface .Range("B2").value = employee.employeeNo .Range("B4").value = employee.firstName .Range("B5").value = employee.lastName .Range("B6").value = employee.gender .Range("B7").value = employee.jobTitle End With Application.ScreenUpdating = True End Sub Public Function GetDisplayedEmployeeRecord() As csModelEmployee Set GetDisplayedEmployeeRecord = New csModelEmployee With mwsUserInterface GetDisplayedEmployeeRecord.employeeNo = .Range("B2").value GetDisplayedEmployeeRecord.firstName = .Range("B4").value GetDisplayedEmployeeRecord.lastName = .Range("B5").value GetDisplayedEmployeeRecord.gender = .Range("B6").value GetDisplayedEmployeeRecord.jobTitle = .Range("B7").value End With End Function Private Sub Class_Initialize() Set mwsUserInterface = ThisWorkbook.Sheets("FrontEnd") End Sub Private Sub Class_Terminate() Set mwsUserInterface = Nothing End Sub |
Controller Class
Finally, we have the controller class (csController) as detailed below. This class handles the interaction between the model and view layers so that data can be passed back and forth between our data store and the frontend.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Option Explicit Private mobjEmployee As csModelEmployee Private mobjUserInterface As csViewUI Private Sub Class_Initialize() Set mobjEmployee = New csModelEmployee Set mobjUserInterface = New csViewUI End Sub Private Sub Class_Terminate() Set mobjEmployee = Nothing Set mobjUserInterface = Nothing End Sub Public Sub UpdateEmployee() mobjEmployee.UpdateEmployeeRecord mobjUserInterface.GetDisplayedEmployeeRecord End Sub Public Sub FindEmployee(ByVal employeeNo As Integer) If mobjEmployee.GetEmployeeRecord(employeeNo) Then mobjUserInterface.ClearEmployeeDisplay mobjUserInterface.DisplayEmployeeRecord mobjEmployee Else mobjUserInterface.ClearEmployeeDisplay End If End Sub |
Putting it altogether
Inside the worksheet code module of our frontend worksheet, we add the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Option Explicit Private controller As csController Public Sub btnFind() If controller Is Nothing Then Set controller = New csController End If controller.FindEmployee Me.Range("B2").value End Sub Public Sub btnUpdate() If controller Is Nothing Then Set controller = New csController End If controller.UpdateEmployee End Sub Private Sub Worksheet_Activate() Set controller = New csController End Sub Private Sub Worksheet_Deactivate() Set controller = Nothing End Sub |
When the worksheet is selected, an instance of the Controller class is created. Whatever action that the user then invokes on the frontend, gets passed to the Controller to handle. The Controller then liases with the View and Model classes to return an appropriate response. This completes the setup for our MVC designed application.
Conclusions
Organizing your code in this manner may seem long-winded, in fact you may be thinking that this is alot of code to implement for what has been a relatively simple app in our example. But, the real benefit comes when your application grows in size and complexity. Keeping this approach, allows it to stay manageable so that changes can easily be accommodated in a clean way. Even if you feel that the MVC pattern is overkill for your needs, the important thing to take away from it, is that keeping data related code separate from user interface code will ultimately lead to a cleaner more sustainable solution in the long run.
You can download the code from here (however please take time to read the disclaimer about content found on this site).
>>Post Update (21-Nov-2017):
I have also created a version of this MVC application that uses a Form rather than a worksheet for the frontend. Essentially, the implementation of the layers is exactly as described in this post. The only difference is the code in the csViewForm class has now changed to update and retrieve values from the form rather than the worksheet. Also, the code that was in the worksheet code module, has now moved to the code module behind the Form. You can download the original example in this post from the above link, or you can download the version with a Form from here (however again, please take time to read the disclaimer about content found on this site).
Share :
