VBA – The perfect userform in VBA

Somewhere in the summer of 2012 I have started coding in VBA. A little less than 5 years have passed since that very moment when I have pressed Alt+F11 for a first time and I have entered another Excel world. Since then a lot has happened and I have become a much better developer. But still, there are things that I don’t know. If they are worthy,  I try to master them and put them into my routine.

Long story short – I was browsing the Documentation section for VBA in stackoverflow and suddenly I have noticed that the idea of building a userform of the author of the section was completely different from mine. He has written as a best practise that one should work with a new instance every time. Definitely the opposite of the last 5 VBA books I have read, where you are thought to use UserForm.Show and to live easily and happily ever after. After reading the example, it really made sense to me. E.g. if the form is a blueprint, one should really not abuse it with UserForm.Show. Then one can follow the famous MVC pattern and anything can be like a standard for a developer outside the VBA world. Anyhow, I somehow realized that the userforms should be threatened somehow better than in all VBA books and thus, I have decided to write the code of the perfect userform in VBA.

Pretty much we have a form, a module and a class. The form is the blueprint, the module is the business logic and the class is what implements the business logic into the form, thus they do not know about each other.

Pretty much, here is how the form looks like:


It has only the following controls:

  • btnRun
  • btnExit
  • lblInfo
  • frmMain (the class)

The code of the form itself is the following:

The form is with two events, getting caught by the clsSummaryPresenter. The clsSummaryPresenter looks like this:

Finally, we have the modMain, which is the so-called business logic of the form:

And the whole party works flawlessly!

Enjoy it! 🙂

Special thanks to Mat’s Mug for writing the form documentation in  StackOverflow!

Tagged with: , , ,