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.

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

5 thoughts on “MVC Design Pattern in Excel VBA”

  1. Hi, really good example and explanation. However, I was wondering how you could implement similar approach on a MSFORM. I tried to follow the logic of your code and I stuck trying to ge only one Form instance open at a time.

    Thanks,

    Sergio

    1. Hi Sergio, thank you for your comment. Here is a basic example of the same application using a form. The implementation of the MVC pattern is the same as what has been described in the post, with only a few minor changes to the View class.

  2. Great article. I’m building a fairly extensive Excel tool with VBA and I decided to use the MVC architecture and I found this article really useful.

    However, I was wondering how to implement a pseudo-database, that is, not only just a worksheet but nor something too complicated such as SQL database.

    I was thinking about creating a collection class that would work as the database. What do you think?

    1. Hi Andre, thanks for your comment! Using a collection class for your project sounds like an interesting idea. I suppose the only thing to consider would be what kind CRUD operations you might want to perform on your data. It might be that these operations would be better handle in your app if the data existed on a worksheet/database, as there are optimized ways to do this. Also, having everything in memory all the time may be an issue with a collection, particularly if the data were to increase in size.

  3. Hi Zaid, Great post
    I’m trying to implements a MVC project using excel but a have some issues.
    I have the next relational model
    I receive Letters from Companies, these letter have Approvals, Management and Articles. My data base have the next tables
    tblLetters, tblLettersApprovals, tblLettersManagements, tblCompanies.
    How can i manage these?
    pd. My English is no very good

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.