How do Class definitions work in VBA

Question

As i am trying to learn VBA, Classes are blasting me some heavy duty pain!!! Here is something from one of my VBA books (VBA and Macros Microsoft Excel 2010 - Bill Jelen at page 497) which is absolutely bizzare and peculiar: We open a class module in VBE And we are writing the 4 properties and 1 method of the custom object in the class module `clsEmployee`: ``` 'Properites Public EmpName As String Public EmpID As String Public EmpRate As Double Public EmpWeeklyHrs As Double 'Methods Public Function EmpWeeklyPay() As Double EmpWeeklyPay = EmpRate * EmpWeeklyHrs End Function ``` The book writes that the custom object is now complete Now the book goes to open a simple module (not a class module) to reference the custom object from another module, so it goes: ``` Dim Employee as clsEmployee Set Employee = New clsEmployee ``` Not it presents all the code block. Really awkward thing here is that the Dim Employee as clsEmployee is outside of the sub procudure! Why? (So this could be seen as…

Accepted Answer

Based on my understanding of what you are asking, here is my answer:

First question:

Dim Employee as clsEmployee 'why is this outside of the code block?

When you do this, you can reference Employee from different parts (subs) of your code. It becomes accessible to the following code. This is optional. You could use the above line only when required by specifying in inside the sub you want. This concept is usualy discussed under the concept of variable reference scope. It is not special to class objects but any variable you use follow the same rule. Take a look at: Scope of variables in Visual Basic for Applications.

Second question:

What is actually assuring me that when i call the EmpRate it won't go for the EmpWeeklyHrs it's the same data type and there is no other type of code in their "blueprint" the class module to stop things from getting unreliable.

Compilers and interpreters are very specific about variable names. Naming is fundametal aspect of coding ever since registers were used in Assembler. When you specify Employee.EmpRate, then data in the address assigned to Employee.EmpRate will be used, I see no possibility it could confuse Employee.EmpRate with Employee.EmpWeeklyHrs. The two variables/properties have different names and hence will have 2 distinct storage locations.

Edit to explain with a VERY VERY simplified illustration (this is now what happens of course, but it is meant to illustrate the idea): Now, when you code

```
Doc = Employee.EmpName 
```

VBA goes to the storage location of the instance called "Employee" and finds that the property called 'Name" is stored with a displacement of 8 chars from the base address of value 1000 of the object. It then goes and assigns the string "Scott" found at location 1008 to the variable Doc.

How do Class definitions work in VBA

Solution

Problem