Object-oriented VBA programming in Excel (OOP and VB6) | Free code

In this workbook, we have used the concept of object-oriented programming (OOP) when we have written VBA code to perform tasks in the workbook.

In this workbook, we have created a custom menu that can be found under the menu tab "Extensions" or the Tools menu depending on which version of Excel you use, and a form with code that handles the event when a button is clicked. In this workbook there is code in the form, in "this workbook", in a module and in three class modules. In order to view the VBA code, you must enable the "Analysis Tool Pack - VBA" add-in and you may also need to activate the menu tab "Developer".

The main idea of object-oriented programming (OOP) is to create independent classes for objects that should be used in a program and make the class easy to use for someone that not has created the class. A class act as a black box where the user of the class does not need to see the code in the class but only needs to know the public properties and public methods available in the class.

A class consists of variables that normally are declared as private, public properties that can be used to set and get values of private variables, methods that can be used to set and return values, constructors and destructors. In a programming language that is fully object-oriented can one class inherit from another class, implement a interface from several other classes and have multiple constructors that can accept parameters.

In VBA (VB 6) can not a class module inherit from another class, can not implement interfaces and only have one constructor (Class_Initialize) that can not receive parameters. You do not need to have a constructor in a class module but it can for example be used to set default values for private variables. A destructor (Class_Terminate) do not need to be included in a class module but can be used to release resources from memory when there is a risk of memory leakage if the class module contains unmanaged resources that are outside of Excel and VBA.

Memory management in VBA (VB 6) is handled through reference counting so that an object is destroyed (Class_Terminate) when the number of references for the object has reached 0 and this occurs when the method that has a reference to an object have been completed or when the value of the object has been set to "Nothing". There is normally no need to add a destructor (Class_Terminate) in a class module because the memory management of resources in VB6 and Excel normally is handled automatically. Bad code can cause "Out of Memory Exeptions" but then the solution is to rewrite the bad code. If your computer has shortage of memory, there may be a need for early destruction of objects, and if your class module contains unmanaged resources outside of VBA and Excel, there is a need to release these resources so that no memory leaks occur.

When we create a class (class module), we declare all variables that normally are declared as private so that only properties and methods within the class can set and get values for these variables. In a class (class module), we use public properties that can be called outside ot the class to set and get values for private variables when we do not have to perform extensive tasks. In a class (class module), we use public methods (Sub and Function) that can be called outside of the class to set and get values of private variables when more comprehensive data tasks must be performed. A method can be a subroutine (Sub) or a function (Function) where subroutines not are designed to return values while functions are designed to return values.

When we want to use properties and methods that are declared in a class (class module), we must create a new instance of the class and we do this by using the "New" keyword to run "Class_Initialize" and allocate memory for the object. Updated: 01/01/2015 | Created by All-templates.biz

Download Object-oriented VBA programming in Excel (OOP and VB6) | Free code »

Tags: excel programming concepts