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

10 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.



    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

  4. Muy útil el articulo, soy desarrollador freelancer del paquete de Office y me estuve preguntando mucho como implementar este modelo, lo hacia de una manera muy complicada, pero tu post me aclaro mis dudas, sin duda seguiré este patrón que propones, muchas gracias por compartirlo!

  5. I really like your how you applied the MVC design pattern to a VBA application. I am using your approach to design a relatively simple Excel VBA app. I am working on a sequence diagram for my app, but I am not quite sure how to model the interactions between frmFrontend and csViewForm. Should I consider csViewForm as an extension of frmFrontend, and just use csViewForm to represent the user interface in my sequence diagram?

    1. Daniel Caballero

      Thanks for the post. It is very helpful and clarifies a lot of points in how to apply the MVC framework in an Excel VBA.

      I am in the same situation as Eddy Proft. If I am not mistaken, the viewer and the user form, or any kind of user interface used here, must have a connection. It implies that the viewer must know about the UI. The question, in this case, is how to implement such a connection.

      Perhaps implementing the viewer as part of the form code?

      Or is there any way of referencing the form from the viewer?

      A third one might be to encapsulate the form and giving it an interface to communicate with the form, but that seems to over-complicate things. There is a high level of coupling implied in this third option, which renders it not very useful.

      I hope we can find a valid approach.

  6. Hi Zaid,

    First of all, thanks for the excellent article about MVC Design Pattern in VBA. I did a huge search but didn’t find such a good article as yours.

    I know that the business rules are handled in the Model layer. If I need to manipulate the objects of a form (in Access) because of any business rule (eg. if the value is over 20,000, hide subform 1, if it is over 20,000, hide subform 2…), how would you do that better considering tha the View layer should manipulate the form objects and not the Model layer. If I manipulate the objects via View layer according to a result coming from the Model layer, I not have the business rules just in the Model layer. how would you do that better?

    Just to say that I’m in the beginner/intermediate level about OOP.

    Thanks in advance.

  7. I’m trying to advance my Excel VBA knowledge into using more OOP and patterns. This MVC article is great. Knowing the use of Tables (vba ListObjects) and Named Ranges, this pattern could be greatly enhanced. 🙂

Leave a Comment

Your email address will not be published. Required fields are marked *

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