Creating Your Own Objects

Proceed with creating the Employee object just discussed. You want to store the employee's name, hours worked per week, and rate of pay. From this information, you want to calculate the employee's weekly pay. You can create an Employee object with three properties to hold the required data and a method that calculates the weekly pay.

To do this, you create a class module named CEmployee, as shown in the top right of Figure 16-1.

¡5 Microsoft Visual Basic - Classl.xlsm

- â'3

File Edit View Insert



Run Tools Add-Ins Window Help

1 Tvjje s c|U25tiBn icr

H! - y • - i^j ^



J ^ Ln 13, Col 5


Lis A:- iW- if-

-Ö =



Piojtrt - VtiAFVoject

• :.. - •• i •:! 1 1 : sri1;.:: !• i

— v"


m JI _f


1 (General) v WeeklyPay v |

i- ^ VBAProject [ClassLxlsmJ

G S Microsoft Excel Objects

Option Explicit-


a] Sheetl {Sheet 1) B] Sheet2{Sheet2) ffl] sheets {Sheet}) Q ThisWorkbook

F'jblic Name î-i3 5trinç Fufciic HoursFerWeek A3 Dpùble Public Raue î-l3 Dcufcle

p Modules modExamples 6 ^ Class Modules ¿*il CEmployee

Public Function Weekly Pay (} As Dcufcle

— 1

WeeîclyEay = KouiaPexweelc * Rate End. Function



Classl.xlsm modExamples {Code)


- mrnJf Kcunrilei


J (General) |.y j i EmployeePay

modExamples Module

V 1

Option Explicit


Alphabetic Categorized

Sub EmclayeePay()

f'temej modExamples

Set clsEmployee — Mew CEieplcyee

clsEmployee.Name = "Mary"

clsEmployee.Race = 15

ClsEmployee.HoursPerWeek = 35

MsgBox clsEmployee.Name & " earns * els

IicçLGyee .WeEfclypay i rr/wlc,r

End |5uh





Figure 16-1

The class module declares three public variables — Name, HoursPerWeek, and Rate — which are the properties of the Employee object. There is also one public function, WeeklyPay. Recall that any public function or sub procedure in the class module behaves as a method of the object. A function is a method that can generate a return value. A sub is a method that does not return a value.

The code in the standard module modExamples (at the bottom right of Figure 16-1) generates an Employee object from the CEmployee blueprint. The module declares clsEmployee as a CEmployee type. The EmployeePay sub procedure uses the Set statement to assign a new instance of CEmployee to clsEmployee; that is, Set creates the new object.

The sub then assigns values to the three properties of the object, before generating the message that appears in the message box shown in Figure 16-2. To form the message, it accesses the Name property of the Employee object and executes the WeeklyPay method of the Employee object.

Microsoft Excel

M-ary earns 5525/wk

Figure 16-2

An alternative way of setting up the standard code module, when you only need to create a single instance of the object variable, is as follows:

Dim Employee As New CEmployee

Sub EmployeePay()

Employee.Name = "Mary" Employee.Rate = 15 Employee.HoursPerWeek = 3 5

MsgBox Employee.Name & " earns $" & Employee.WeeklyPay & "/wk" End Sub

Here, the keyword New is used on the declaration line. In this case, the Employee object is automatically created when it is first referenced in the code.

0 0

Post a comment