MVC Design Pattern in Excel VBA

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:

  1. 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.
  2. 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.
  3. 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:

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:

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.

Putting it altogether

Inside the worksheet code module of our frontend worksheet, we add the following code:

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.


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 :Facebooktwitterredditpinterestmail