VBA Tutorial Login  

Free! Excel VBA Online Tutorial

   

Excel VBA

User Rating:  / 15
PoorBest 
Details

 

Excel VBA Custom Classes & Objects, Class Modules, Custom Events

 

 

In vba you can create your own custom objects by defining classes & inserting class modules. You can also create your own custom class events, in addition to Excel's own inbuilt event procedures. In this section we explain how to create custom objects by inserting a class module and how to create your own custom events in a class.

 

 

Custom Classes and Objects

 

In vba you can create your own custom objects by defining classes. Classes act as templates for new objects. The custom object is used to store, process and make data available. A Class contains data and code - data can be accessed with properties (viz. Name property) and the code is referred to as methods (which are defined as Subs and Functions in VBA). Classes are integral to the Object-Oriented Programming (OOP) language. A class is created by inserting a Class Module (in your VBA project) to which you give a name. A Class Module allows you to create your own objects having their own properties and methods much like other objects such as range, worksheet, chart, etc. The Class Module has its own set of vba code (which includes variables, constants and procedures), which defines its properties and methods. The properties of the class object are manipulated in a Class Module with Property procedures which use the Property Let, Property Get, and Property Set statements. To access the properties and methods of the class object from a procedure in a code module, you declare an object variable of the class's type in that procedure.

 

You can do all vba programming without creating custom objects which do not really increase code functionality. However, using custom objects makes complex coding look simpler by having related code in one place, makes coding self-documenting with appropriately named classes, properties & methods, and this helps in debugging and reusing code.

 

 

Insert a Class Module:

 

In Visual Basic Editor (VBE), click Insert in the menu bar and then click Class Module. Alternatively, in the Project Explorer in VBE, right click on the VBA Project name or any object or item under it, click Insert and then click Class Module. Or, on the Standard Tool bar in VBE, click the Insert button and then click Class Module. This creates a blank class with the name Class1. To remove or delete a Class Module, right click after selecting it in the Project Explorer in VBE, and then click on Remove.

 

 

Name a Class Module:

 

The name of the selected Class Module appears to the right of (Name) in the Properties Window in VBE, and can be changed therein.

 

 

Instancing Property of a Class Module:

 

The Instancing property of a Class Module is set to Private by default which does not allow an external project from working with and using instances of that class. Set the Instancing property to PublicNotCreatable to allow external projects, with a reference set to the project containing the defined class, to access and use instances of the defined class. Note that the setting of PublicNotCreatable still does not allow the external project to instantiate (ie. create or call into existence) the class object or an instance of the class, which can be instantiated only by the project which contains the definition of the class. Note that the external project can use an instance of the defined class if the referenced project has already created that instance.

 

 

Instantiate a Class Object:

 

As already mentioned, in vba you can create your own custom objects by defining classes. A class is created by inserting a Class Module. To access the properties and methods of the class object from a procedure in a code module, you need to create a new instance of the class object ie. instantiate  (ie. create or call into existence) an instance of the class. Note that multiple number of instances of a class object can be created. There are two ways of doing this, one with a two-line code or alternatively with a single-line code.

 

Two-line code to instantiate an instance of a class:

 

Use the Dim statement to create a variable (iStudent) and define it as a reference to the class (clsStudent):

Dim iStudent As clsStudent

 

Create a new object reference by using the New keyword. Mention the name of the class (clsStudent) you want to instantiate, after the New keyword:

Set iStudent = New clsStudent

 

Alternate single-line code to instantiate an instance of a class:

 

In this case the clsStudent object gets instantiated only when the class method is first called ie. iStudent gets instantiated only when first used.

Dim iStudent As New clsStudent

 

 

Create Class Properties:

 

One way to create a class property is by declaring a Public Variable in the Class Module, and this property will be read-write. The other way to create a class property is by using Property Procedures ie. create a private variable to hold values and use property statements (viz. Property Let, Property Set and Property Get). Creating properties using a Public Variable, though simple, may not usually be preferable because it is not flexible. Using property statements will enable to set a read-only or write-only property in addition to read-write, whereas using a public variable will create only read-write properties. Further, using property statements you can execute code to calculate values as properties whereas using a public variable will not allow use of code to set or return the value of a property. For example, in case of an Area property, the area of a rectangle changes per its length-width and in case the length-width is dynamic it should not be stored as a fixed value.

 

Create methods in a Class Module: In addition to properties, objects can also have one or more methods. A method is defined as Subs and Functions in VBA and is created with Sub-routine and Function procedures. A method is a sub-procedure containing a set of codes which perform an action or an operation on the data within the class, or a function containing a set of codes which returns a value after performing an operation. In a Class Module, only if the method is declared Public can it be called from an instance of this class, else if a method is declared Private it can be called only from other methods within the class. Note, that by default a procedure is Public if the Private or Public keywords are not specified.

 

 

Using Property Procedures to Create Properties:

 

Property Procedure is a set of vba codes that creates and manipulates custom properties for a class module. A Property procedure is declared by a Property Let,  Property Get or Property Set statement and ends with an End Property statement. Property Let (write-only property) is used to assign a value to a property and Property Get (read-only property - which can only be returned but not set) returns or retrieves the value of a property. Property Set (write-only property) is used to set a reference to an object. Property procedures are usually defined in pairs, Property Let and Property Get OR Property Set and Property Get. A Property Let procedure is created to allow the user to change or set the value of a property, whereas the user cannot set or change the value of a read-only property (viz. Property Get).

 

A property procedure can do whatever can be done within a vba procedure like performing an action or calculation on data. A Property Let (or Property Set) procedure is an independent procedure which can pass arguments, perform actions as per a set of codes and change the value of its arguments like a Property Get procedure or a Function but does not return a value like them. A Property Get procedure is also an independent procedure which can pass arguments, perform actions as per a set of codes and change the value of its arguments like a Property Let (or Property Set) procedure, and can be used similar to a Function to return the value of a property.

 

A Property Get declaration takes one argument less than in the associated Property Let or Property Set declaration and the Property Get declaration should be of the same data type as the data type of the last argument in the associated Property Let or Property Set declaration. The Property Get declaration will use the same property name as used in the associated Property Let or Property Set declaration.

 

A Property Let procedure can accept multiple arguments, and in this case the last argument contains the value to be assigned to the property. This last argument in the argument list is the property value set by the calling procedure. The name and data type of each argument in a Property Let procedure and its corresponding Property Get procedure should be the same, except for the last argument in the Property Let procedure which is additional. All arguments before the last argument are passed to the Property Let procedure. In the case of a Property Let procedure with a single argument (at least one argument is required to be defined), this argument contains the value to be assigned to the property and is the value set by the calling procedure. In this case the Property Get procedure will have no argument. It is not a usual practice to pass multiple arguments in property procedures and sub-procedures or Functions are used for this.

 

A Property Set procedure can accept multiple arguments, and in this case the last argument contains the actual object reference for the property. All arguments before the last argument are passed to the Property Set procedure. In the case of a Property Set procedure with a single argument (at least one argument is required to be defined), this argument contains the object reference for the property. The data type of the last argument or the single argument must be an Object type or a Variant.

 

The Property Set procedure is similar to and a variation of the Property Let procedure and both are used to set values. A Property Set procedure is used to create object properties which are actually pointers to other objects, whereas a Property Let procedure sets or assigns values to scalar properties like string, integer, date, etc. Using the Property Set statement enables Properties to be represented as objects.

 

Below is the syntax for the 3 property procedure declarations.

 

Property Get:

Property Get PropertyName(argument_1, argument_2, …, argument_n) As Type

 

Property Let:

Property Let PropertyName(argument_1, argument_2, …, argument_n+1)

 

Property Set:

Property Set PropertyName(argument_1, argument_2, …, argument_n+1)

 

 

Examples

 

 

Example 1 - Create Class Properties by using Property Procedures. Refer Images 1a & 1b. For live code, click to download excel file.

 

Insert Code in Class Module named clsStudent:

 

'Example - Create Class Properties by using Property Procedures:
Private strStuName As String
Private dblStuMarks As Double

Public Property Let Name(strN As String)
'declare the property procedure Public so that it can be called from an instance of this class in another module.
'In the case of a Property Let procedure with a single argument (at least one argument is required to be defined), this argument contains the value to be assigned to the property and is the value set by the calling procedure. In this case the Property Get procedure will have no argument. A Property Let procedure is created to allow the user to change or set the value of a property, whereas the user cannot set or change the value of a read-only property (viz. Property Get).


strStuName = strN


End Property

Public Property Get Name() As String
'returns the Name property


Name = strStuName


End Property

Public Property Let Marks(iMarks As Double)
'assigns the Marks property


dblStuMarks = (iMarks / 80) * 100


End Property

Public Property Get Marks() As Double
'returns the Marks property


Marks = dblStuMarks


End Property

Public Function Grade() As String
'Create a Method within a Class - declare the method Public so that it can be called from an instance of this class in another module.


Dim strGrade As String

If dblStuMarks >= 80 Then

strGrade = "A"

ElseIf dblStuMarks >= 60 Then

strGrade = "B"

ElseIf dblStuMarks >= 40 Then

strGrade = "C"

Else

strGrade = "Fail"

End If

 

Grade = strGrade

 

End Function

 

 

Insert Code in a Standard Code Module:

 

Sub clsStudentRun()
'this procedure instantiates an instance of a class, sets and calls class properties:


'use the Dim statement to create a variable and define it as a reference to the class.
Dim iStudent As clsStudent

'a new object reference is created by using the New keyword. Mention the name of the class you want to instantiate, after the New keyword.
'following code line (together with the above Dim statement, it is a two-line code to instantiate an instance of a class), instantiates the clsStudent object:
Set iStudent = New clsStudent

'alternate single-line code to instantiate an instance of a class, however in this case the clsStudent object gets instantiated only when the class method is first called, ie. iStudent gets instantiated only when first used:
'Dim iStudent As New clsStudent

'sets the Name property in the clsStudent object to be the string "Peter", and passes this data to the strN variable in the Name property:
iStudent.Name = "Peter"

 

'call the Name property in the clsStudent object
MsgBox iStudent.Name

'sets the Marks property in the clsStudent object to the value 45, and passes this data to the iMarks variable in the Marks property:
iStudent.Marks = 45

'call the Marks property in the clsStudent object
MsgBox iStudent.Marks

'call the Grade function from the clsStudent object:
MsgBox iStudent.Grade

MsgBox iStudent.Name & " has got " & iStudent.Marks & " percent marks with a Grade " & iStudent.Grade


End Sub

 

 

 

------------------------------------------------------------------------------------------------------------------------

 

Example 2 - Create Class Properties by using Property Procedures, Property Let procedure accepting multiple arguments. For live code, click to download excel file.

 

Insert Code in Class Module named clsRectangle:

 

'Example - Create Class Properties by using Property Procedures, Property Let procedure accepting multiple arguments.
Private dblA As Double

Public Property Let Area(lngth As Double, wdth As Double, ar As Double)
'A Property Let procedure can accept multiple arguments, and in this case the last argument contains the value to be assigned to the property. This last argument in the argument list is the property value set by the calling procedure. The name and data type of each argument in a Property Let procedure and its corresponding Property Get procedure should be the same, except for the last argument in the Property Let procedure which is additional. All arguments before the last argument are passed to the Property Let (& Property Get) procedure.

'In the case of a Property Let procedure with a single argument (at least one argument is required to be defined), this argument contains the value to be assigned to the property and is the value set by the calling procedure. In this case the Property Get procedure will have no argument.

'It is not a usual practice to pass multiple arguments in property procedures and sub-procedures or Functions are used for this.


dblA = ar

MsgBox "Arguments received - lngth: " & lngth & ", wdth: " & wdth & ", ar: " & ar


End Property

Public Property Get Area(lngth As Double, wdth As Double) As Double


Area = dblA


End Property

 

 

Insert Code in a Standard Code Module:

 

Sub clsRectangleRun()
'This procedure instantiates an instance of a class, sets and calls class properties, passing multiple arguments to Property Let procedure:


Dim l As Double
Dim w As Double

Dim rect As New clsRectangle

l = InputBox("Enter Length of rectangle")
w = InputBox("Enter Width of rectangle")

'setting the property value - passes this data to the ar variable in the Area property:
rect.Area(l, w) = l * w

'accessing the Area property:
a = rect.Area(l, w)

MsgBox "Area of Rectangle with length " & l & ", width " & w & ", is " & a


End Sub

 

 

------------------------------------------------------------------------------------------------------------------------

 

Example 3 - Create Read-Only Class Property with only the PropertyGet_EndProperty block. For live code, click to download excel file.

 

Insert Code in Class Module named clsRectArea:

 

'Example - Create Read-Only Class Property with only the PropertyGet_EndProperty block.
Private dRectL As Double
Private dRectW As Double


Public Property Let Length(l As Double)


dRectL = l


End Property

Public Property Get Length() As Double


Length = dRectL


End Property

Public Property Let Width(w As Double)


dRectW = w


End Property

Public Property Get Width() As Double


Width = dRectW


End Property

Public Property Get rArea() As Double
'Read-Only property with only the PropertyGet_EndProperty block and no PropertyLet_EndProperty (or PropertySet_EndProperty) block.


rArea = Length * Width


End Property

 

 

Insert Code in a Standard Code Module:

 

Sub clsRectAreaRun()
'This procedure instantiates an instance of a class, sets and calls class properties.


Dim a As Double
Dim b As Double

Dim areaRect As New clsRectArea

a = InputBox("Enter Length of rectangle")
b = InputBox("Enter Width of rectangle")

areaRect.Length = a
areaRect.Width = b

MsgBox areaRect.rArea


End Sub

 

------------------------------------------------------------------------------------------------------------------------

 

Example 4 - Using Property Set statement to set a reference to an object.

A Property Set procedure is used to create object properties which are actually pointers to other objects. Refer Images 2a, 2b & 2c. For live code, click to download excel file.

 

Insert Code in Class Module named clsCar:

 

'Example - Using Property Set statement to set a reference to an object. A Property Set procedure is used to create object properties which are actually pointers to other objects.

'declare a private variable (ie. varCar) to store a reference to the clsMotorCars object:
Private varCar As clsMotorCars

Public Property Set Car(objCar As clsMotorCars)
'The Property Set statement sets a reference to an object, and assigns Car to an object.


'create an object variable (ie. varCar) and point it to the clsMotorCars object passed to the procedure:
Set varCar = objCar


End Property

Public Property Get Car() As clsMotorCars


'return the object variable (ie. varCar) created by the Property Set procedure:
Set Car = varCar


End Property

 

 

Insert Code in Class Module named clsMotorCars:

 

'Create Class Properties by using Property Procedures:
Private strColor As String
Private strName As String
Private dMG As Double

Property Let Color(clr As String)

strColor = clr

End Property

Property Get Color() As String

Color = strColor

End Property

Property Let Name(nm As String)

strName = nm

End Property

Property Get Name() As String

Name = strName

End Property

Property Let Mileage(milesGallon As Double)

dMG = milesGallon

End Property

Property Get Mileage() As Double

Mileage = dMG

End Property

Function FuelBudget(FuelCost As Double, Distance As Double) As Double

FuelBudget = (Distance / Mileage) * FuelCost

End Function

 

 

Insert Code in a Standard Code Module:

 

Sub propSetCars()
'Using Property Set statement to set a reference to an object. A Property Set procedure is used to create object properties which are actually pointers to other objects.


Dim dDist As Double
Dim dCost As Double

'instantiate as a clsCar object ie. create a new instance of the clsCar object. A new object reference is created by using the New keyword.
Dim ownCar As clsCar
Set ownCar = New clsCar

'Car has been instantiated as a clsMotorCars object (note that Car is a property of the ownCar object). See below how to access the properties & methods of clsMotorCars object:
Set ownCar.Car = New clsMotorCars

'enter property values:
ownCar.Car.Color = "Yellow"
ownCar.Car.Name = "Ford"
ownCar.Car.Mileage = 50
dDist = InputBox("Enter Distance in miles, covered by car in a month")
dCost = InputBox("Enter Cost of Fuel per gallon")

'return values from properties & methods of clsMotorCars object:
MsgBox "Car Color is " & ownCar.Car.Color
MsgBox "Car Model is " & ownCar.Car.Name
MsgBox "Gives a Mileage of " & ownCar.Car.Mileage & " miles per gallon"
'call the FuelBudget function:
MsgBox "$" & ownCar.Car.FuelBudget(dDist, dCost) & " is the monthly cost of fuel"


End Sub

 

 

 

 

------------------------------------------------------------------------------------------------------------------------

 

Example 5 - Using Property Set statement to set a reference to a Range object. For live code, click to download excel file.

 

Insert Code in Class Module named clsSetRange:

 

'Example - Using Property Set statement to set a reference to a Range object:
Private intColor As Integer
Private strName As String
'declare a private variable (ie. rngV) to store a reference to the Range object:
Private rngV As Range

Public Property Set activeRange(oRng As Range)

'create an object variable (ie. rngV) and point it to the Range object passed to the procedure:

Set rngV = oRng

End Property

Public Property Get activeRange() As Range

Set activeRange = rngV

End Property

Property Let Name(nam As String)

strName = nam

End Property

Property Get Name() As String

Name = strName

End Property

Property Let Color(clr As Integer)

intColor = clr

End Property

Property Get Color() As Integer

Color = intColor

End Property

Sub methodColor()

activeRange.Interior.ColorIndex = Color

End Sub

 

 

Insert Code in a Standard Code Module:

 

Sub clsSetRangeRun()
'Example - Using Property Set statement to set a reference to a Range object.:


'Instantiate as a clsRange object ie. create a new instance of the clsSetRange object. A new object reference is created by using the New keyword.
Dim rngActive As clsSetRange
Set rngActive = New clsSetRange

'Set activeRange property (which is an object property) of clsSetRange object to ActiveCell, and pass this data to the oRng variable in the activeRange property:
Set rngActive.activeRange = ActiveCell

'enter ColorIndex, value 1 to 56:
'3 for red, 4 for green & 5 for blue, ....
rngActive.Color = 5

If rngActive.Color < 1 Or rngActive.Color > 56 Then

MsgBox "Error! Enter a value for ColorIndex between 1 and 56"

Exit Sub

End If

'call sub-procedure named methodColor:
rngActive.methodColor

MsgBox "Interior color, ColorIndex " & rngActive.Color & ", entered in cell " & rngActive.activeRange.Address


End Sub

 

 


 

 

Custom Class Events

 

You can create your own class events also. Events are actions performed, or occurences, which trigger a VBA macro. A VBA code is triggered when an event occurs such as, clicking on a button, opening the workbook, selecting a cell or changing cell selection in a worksheet, and so on. Excel also has its own Event Procedures which are triggered by a predefined event and are installed within Excel having a standard & predetermined name viz. like the Worksheet change procedure is installed with the worksheet as "Private Sub Worksheet_Change(ByVal Target As Range)". When content of a worksheet cell changes, VBA calls the Worksheet_Change event procedure and runs the code it contains. Here we explain how to create your own custom events in a class.

 

Define a Custom Event:

The first step is to declare the event in the declaration section of the class. Use the Event keyword to define a custom event in a class module. It can have any number of arguments, and the event declaration should be Public to make it visible outside the object module. Note that you can declare and raise Events only within object modules (viz. ThisWorkbook module, Sheet modules - worksheets and chart sheets, UserForm modules and Class modules), and not from a standard code module.

 

Raise an Event:

After declaring an event, use a RaiseEvent Statement to trigger the declared event. The event procedure runs when an event is raised or triggered. The event is raised in a public procedure within the class module where it is declared, using the Event keyword. The RaiseEvent statement passes values for the event's arguments, which also get passed to the event procedure that runs on raising the event.

 

External Code to Raise the Event:

However we need an external code to call the public procedure in the class module, which raises the event. This external code determines when the event will be raised by which the event procedure runs.

 

Create an Event Procedure:

Use the WithEvents keyword to declare an object variable of the custom class (in which the custom event is defined) type. By declaring this object variable, the instance of the custom class which this object variable points to will respond to the event by adding the object to the events list in the Code window. Only variables declared at module level can be used with the WithEvents keyword. Also variables can be declared using the WithEvents keyword only in object modules and not a standard code module. After the object variable declaration, the event procedure stub can be created similar to standard vba procedures - the object variable will be displayed in the Object drop-down list and all its events are listed in the Procedure drop-down list.

 

 

Examples

 

 

Example 6 - Create a Custom Event:- use a Worksheet_Change procedure to trigger the custom event. Refer Images 3a & 3b. For live code, click to download excel file.

 

Insert Code in Class Module named clsRange:

 

'Example of Creating a Custom Event - use a Worksheet_Change procedure to trigger the custom event:
Private rngVar As Range
Private intColor As Integer
Private strName As String
'Event declaration: Use the Event keyword to define a custom event (ie. CellSelect event) in a class module (ie. clsRange class). It can have any number of arguments, and the event declaration should be Public to make it visible outside the object module.
Public Event CellSelect(cell As Range)

Public Property Set selectedRange(objRng As Range)

Set rngVar = objRng
'Trigger the event: the RaiseEvent statement executes & raises the CellSelect event and passes values for its arguments. Note that using the RaiseEvent keyword is similar to using the Call keyword used to call & execute a procedure in vba.
RaiseEvent CellSelect(rngVar)

End Property

Public Property Get selectedRange() As Range

Set selectedRange = rngVar

End Property

Property Let Name(nm As String)

strName = nm

End Property

Property Get Name() As String

Name = strName

End Property

Property Let Color(clr As Integer)

intColor = clr

End Property

Property Get Color() As Integer

Color = intColor

End Property

Sub methodColor()

selectedRange.Interior.ColorIndex = Color

End Sub

 

 

Insert Code in a Worksheet (sheet named 'Sheet1') Module:

 

'Use the WithEvents keyword to declare an object variable (rng) of type clsRange. By declaring this object variable, the instance of the clsRange class which this object variable points to will respond to the event by adding the object to the events list in the Code window. Only variables declared at module level can be used with the WithEvents keyword.
'After the variable declaration, the event procedure stub can be created similar to standard vba procedures - rng will be displayed in the Object drop-down list and all its events are listed in the Procedure drop-down list.
Private WithEvents rng As clsRange

Private Sub rng_CellSelect(cell As Range)
'this is an event procedure, consisting of a set of codes, which runs on raising the CellSelect event.


'enter ColorIndex, value 1 to 56:
'3 for red, 4 for green & 5 for blue, ....
rng.Color = 4

If rng.Color < 1 Or rng.Color > 56 Then

MsgBox "Error! Enter a value for ColorIndex between 1 and 56"

Exit Sub

End If

rng.Name = "FirstCell"

'call sub-procedure called methodColor:
rng.methodColor

'note that rng.selectedRange & rng.Color values have been assigned, because they will lose values while executing below code:
Dim i As Integer
i = rng.Color

rng.selectedRange.Select
Selection.Offset(0, 1).Value = "Cell Name: " & rng.Name
Selection.Offset(0, 2).Value = "Cell Address: " & Selection.Address
Selection.Offset(0, 3).Value = "Cell Interior ColorIndex: " & i
Selection.Offset(0, 4).Value = "Cell Content: " & Selection.Value


End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'this worksheet change procedure calls the selectedRange public procedure of the clsRange object.


On Error GoTo ErrorHandler  'Enable error-handling routine for any run-time error

'Instantiate as a clsRange object ie. create a new instance of the clsRange object. A new object reference is created by using the New keyword.
Set rng = New clsRange

'CHANGE CONTENTS OF CELL A1 IN SHEET1 TO RUN THIS PROCEDURE:
'Set selectedRange property (which is an object property) of clsRange object to ActiveCell, and pass this data to the objRng variable in the selectedRange property:
If Target.Address = Range("A1").Address Then

Set rng.selectedRange = Target

Else

Exit Sub

End If

ErrorHandler:
  Application.EnableEvents = True  'EnableEvents is changed back to True on any error


End Sub

 

 

 

------------------------------------------------------------------------------------------------------------------------

 

Example 7 - Create a Custom Event:- initialization of the UserForm triggers the custom event. Refer Images 4a, 4b & 4c. For live code, click to download excel file.

 

Insert Code in Class Module named clsTextBox:

 

'Example of Creating a Custom Event - initialization of the UserForm triggers the custom event:
Private tb As MSForms.TextBox
Private strSeq As String
'Event declaration: Use the Event keyword to define a custom event (ie. eTxtBx event) in a class module (ie. clsTextBox class). It can have any number of arguments, and the event declaration should be Public to make it visible outside the object module.
Public Event eTxtBx(objTxtBx As MSForms.TextBox)

Public Property Set setTxtBx(objTxtBx As MSForms.TextBox)

Set tb = objTxtBx
'Trigger the event: the RaiseEvent statement executes & raises the eTxtBx event and passes values (TextBox object) for its arguments (objTxtBx). Note that using the RaiseEvent keyword is similar to using the Call keyword used to call & execute a procedure in vba.
RaiseEvent eTxtBx(tb)

End Property

Public Property Get setTxtBx() As MSForms.TextBox

Set setTxtBx = tb

End Property

Property Let Sequence(tbSeq As String)

strSeq = tbSeq

End Property

Property Get Sequence() As String

Sequence = strSeq

End Property

 

 

Insert a UserForm, insert 2 textBox (TextBox1 & TextBox2) and a CommandButton (CommandButton1) within the Form. Insert Code in the UserForm Module:

 

'Use the WithEvents keyword to declare an object variable (tx) of type clsTextBox. By declaring this object variable, the instance of the clsTextBox class which this object variable points to will respond to the event by adding the object to the events list in the Code window. Only variables declared at module level can be used with the WithEvents keyword.
'After the variable declaration, the event procedure stub can be created similar to standard vba procedures - tx will be displayed in the Object drop-down list and all its events are listed in the Procedure drop-down list.
Private WithEvents tx As clsTextBox
Private sq1 As String, sq2 As String

Private Sub CommandButton1_Click()
'using the excel built-in event Click: procedure runs on clicking the command button:


'copying the contents of TextBox1 to all other TextBoxes and changing the BackColor of these TextBoxes to red:
Dim objControl As Control
For Each objControl In Me.Controls

If TypeName(objControl) = "TextBox" Then

If Not objControl.Name = "TextBox1" Then

objControl.Value = "copied: " & tx.setTxtBx.Value

objControl.BackColor = vbRed

End If

End If

Next

MsgBox "text copied from the " & sq1 & " to the " & sq2

   
End Sub

Private Sub TextBox1_Change()
'using the excel built-in event Change: procedure runs on change in TextBox contents:


If tx.setTxtBx.Value = "" Then

tx.setTxtBx.BackColor = vbYellow

Else

tx.setTxtBx.BackColor = vbGreen

End If


End Sub

Private Sub tx_eTxtBx(objTxtBx As MSForms.TextBox)
'this is an event procedure, consisting of a set of codes, which runs on raising the eTxtBx event.


tx.setTxtBx.BackColor = vbYellow

With Me.TextBox1

tx.Sequence = "First TextBox"

sq1 = tx.Sequence

End With
    
With Me.TextBox2

tx.Sequence = "Second TextBox"

sq2 = tx.Sequence

End With


End Sub

Private Sub UserForm_Initialize()
'initialization of the userform calls the setTxtBx public procedure of the clsTextBox object.


'Instantiate as a clsTextBox object ie. create a new instance of the clsTextBox object. A new object reference is created by using the New keyword.
Set tx = New clsTextBox

'Set setTxtBx object property of clsTextBox object, and pass the TextBox object (Me.TextBox1) to the objTxtBx variable in the setTxtBx property:
Set tx.setTxtBx = Me.TextBox1

   
End Sub

 

 

 

 

------------------------------------------------------------------------------------------------------------------------

 

Example 8 - Create a Custom Event:- use the WithEvents keyword, within the class module, to declare an object variable (Initialize UserForm to start).

For live code, click to download excel file.

 

Insert Code in Class Module named clsComboBox:

 

'Example of Creating a Custom Event - use the WithEvents keyword, within the class module, to declare an object variable:
'Use the WithEvents keyword to declare an object variable (cBox) of type MSForms.ComboBox. By declaring this object variable here, the excel built-in events associated with this object variable will respond, by adding the object to the events list in the Code window.
Public WithEvents cBox As MSForms.ComboBox

Public Sub setComboBox(objCbx As MSForms.ComboBox)
'On userform initialization, the ComboBox object (objCbx) is passed to this procedure. This procedure is declared public to make it accessible from outside the class module.


'ComboBox object (objCbx) is assigned to the object variable (cBox):
Set cBox = objCbx


End Sub

Public Sub cBox_AddItem(strItem As String, Cancel As Boolean)
'This procedure is declared public to make it accessible from outside the class module. Clicking the command button passes the value for strItem argument, and runs this procedure.


'adds item to ComboBox list, unless AddItem event is cancelled:
If Cancel = False Then

cBox.AddItem strItem

End If

'changes BackColor of ComboBox after adding an item:
If strItem <> "" Then
cBox.BackColor = vbGreen
End If


End Sub

Private Sub cBox_Change()
'using the excel built-in event of Change, this procedure runs when the ComboBox text changes.


'if ComboBox is blank, its BackColor will change to white:
If cBox.Value = "" Then

cBox.BackColor = vbWhite

End If


End Sub

 

 

Insert a UserForm, insert a ComboBox (ComboBox1) and a CommandButton (CommandButton1) within the Form. Insert Code in the UserForm Module:

 

'instantiate an instance (cB) of the class object (clsComboBox):
Private cB As New clsComboBox

Private Sub CommandButton1_Click()


'enter item in the text area of the ComboBox, which you want to add in ComboBox:
Dim strTxt As String
strTxt = cB.cBox.Text

Dim Cancel As Boolean

c = MsgBox("Do you confirm adding the item in ComboBox?", vbYesNo)
If c = vbNo Then

Cancel = True

End If

'call the class method cB.cBox_AddItem and pass value and Boolean variable:
Call cB.cBox_AddItem(strTxt, Cancel)


End Sub


Private Sub UserForm_Initialize()


'call the class method (setComboBox) and pass ComboBox object for its argument's (objCbx) value:
cB.setComboBox Me.ComboBox1


End Sub

 

 

 

   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.