Class in VBA

VBA also allows to define class and create objects as you can do with C++, C#, or Java. To define a class in VBA, you have to create a new Class Module ( click a drop down list near save icon in Visual Basic Editor). A class can have variables, properties, and methods.
-Variables of the class are used to store data when its object is created. Usually the variables are private and can only be accessed through properties.
-Properties are used to write and read data to the private variables. The keyword GET or SET are used to define a readable or writable property. A property can be read-only property or write-only property. It can be both.
-Methods specify the actions or processes on the data.
Example:
' class Student
Dim stid As String ' variable member
Dim stname As String ' variable member

'Read and Write Name property
Property Get Name() As String
   Name = stname
End Property
Property Let Name(value As String)
   stname = value
End Property

'Read and Write ID property
Property Get ID() As String
   ID = stid
End Property
Property Let ID(value As String)
   stid = value
End Property
'Method to print information
Sub printinf()
   Dim info
   info = "ID: " & stid & vbCrLf & "Name:" & stname
   MsgBox info
End Sub
'End of class

After defining the class, you can create objects from the class and access its members.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim obj As Student
   Set obj = New Student ' Create object
   'Write data to private variables by using properties
   obj.ID = "St2013"
   obj.Name = "Dara Yuk"
   'call printf method member to show the information
   obj.printinf
End Sub

A class can have objects as its data member. To access the object data members, you will need a read-only property that accepts the class type ( the class that is used to create the objects). You do not need to define the property as a writable property.

' class Student

Dim stid As String ' variable member
Dim stname As String ' variable member
Dim Cr As New Course ' object member

Property Get Name() As String ' read property member
   Name = stname
End Property
Property Let Name(value As String) 'write property member
   stname = value
End Property
Property Get ID() As String ' read property member
   ID = stid
End Property
Property Let ID(value As String) 'write property member
   stid = value
End Property

Property Get Course() As Course 'this property to access the object member
   Set Course = Cr
End Property

Sub printinf()
   Dim info
   info = "ID: " & stid & vbCrLf & "Name:" & stname
   MsgBox info
End Sub

'End of class

' Class Course
Dim courseid As String
Dim coursedesc As String
Dim coursestartdate As Date
Dim lecturer As String
Property Let ID(value As String)
   courseid = value
End Property
Property Let Desc(value As String)
   coursedesc = value
End Property
Property Let StartDate(value As Date)
   coursestartdate = value
End Property
Property Let Instructor(value As String)
   lecturer = value
End Property
Property Get ID() As String
   ID = courseid
End Property
Property Get Desc() As String
   Desc = coursedesc
End Property
Property Get StartDate() As Date
   StartDate = coursestartdate
End Property
Property Get Instructor() As String
   Instructor = lecturer
End Property
Sub printinf()
   Dim st
   st = "Course ID:" & courseid & vbCrLf & "Description:" & coursedesc & _
   vbCrLf & "Start date:" & coursestartdate & vbCrLf & " Instructor:" & lecturer
   MsgBox st
End Sub

' End of class

Next, you have another version of accessing class and its object as shown below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim obj As Student
   Set obj = New Student
   obj.ID = "St2013"
   obj.Name = "Dara Yuk"
   obj.Course.ID = "C001"
   obj.Course.Desc = "MIS"
   obj.Course.Instructor = "Kamo Shado"
   obj.Course.StartDate = #2/9/2012#
   obj.printinf
   obj.Course.printinf
End Sub

Posted by: Dara | post date: 02-22-2013 | Subject: VBA for MS Excel




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.