"Contact Editor" demos the Model-View-Presenter (MVP) pattern backed by persistent storage (MVP-DB) in VBA. I developed this mock data manager as a VBA OOP course project and an MVP-DB template/prototype for my VBA experiments.
Figure 1. Data management workflows.
The typical data management constituents are shown in Fig. 1. Usually, the user sends a query to the database, receives a response table, browses record data via a user form, and, possibly, updates the database. From the user perspective, the data management process involves three key players: the user, persistent storage, and GUI acting as an intermediary between the first two. Functionally, the actual intermediary is the data manager application responsible for presenting the GUI, maintaining a local data container, and transferring the data (user ⇔ GUI ⇔ data container ⇔ persistent storage), as shown in Fig. 2. The left part of the figure marked with green arrows ("user ⇔ GUI" and "GUI ⇔ data container" interactions) can be implemented via the MVP pattern, and the remaining "data container ⇔ persistent storage" interaction can be handled via a "storage" library.
Figure 2. Data manager application.
Primarily, I use Excel 2002 for development and also run tests on Excel 2016. Contact Editor demo is a VBA app and is part of an Excel Workbook Contact Editor.xls available from the root of this repo. Additionally, all code modules and user forms are available from the Project folder (which acts as a container and corresponds to the VBA project root within the .xls file). Rubber Duck VBA add-in greatly facilitated the development process, and the project structure is exported/imported using the RDVBA Project Utils VBA module. The repo includes a sample SQLite database used by one test module. This database is accessed via the "ADODB" backend and relies on the SQLite ODBC driver. While the tests should pass when the provided driver distribution is used, I compiled the driver myself, as briefly discussed here, to use an up-to-date SQLite library with all extensions enabled.
ContactEditorRunner.RunContactEditor is the main entry for the demo.
ContactEditorPresenter.InitializeModel performs basic backend configuration.
The DataTableBackEnd variable found in the entry point sub determines the type of the main storage backend. It can take one of the following values:
- "Worksheet" for an Excel Worksheet (demo database file),
- "CSV" for a text delimited file (demo database file), and
- "ADODB" for a relational database (demo SQLite database file).
While the "ADODB" backend can connect to both "CSV" and "Worksheet" databases, the dedicated backends should be more efficient.
For documentation and technical details see project website.
A special thanks goes to Mathieu Guindon, a co-founder of the Rubber Duck VBA project and his RDVBA blog. RDVBA blog post describing a possible approach to abstracting a Worksheet-based persistent storage and a demo file helped me jump-start with storage integration. I also followed the blog post regarding the best practices for UserForm handling and the SO answer (and the last comment to that answer) regarding the modeless user forms.