How do Class definitions work in VBA
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 the minor question)
Option Explicit Dim Employee as clsEmployee 'why is this outside of the code block? Sub EmpPay() Set Employee = New clsEmployee With Employee .EmpName = "Tracy Syrstad" .EmpID = "1651" .EmpRage = 25 .EmpWeeklyHrs = 40 MsgBox .EmpName & " earns $" & .EmpWeeklyPay & " per week." End With End Sub
The book graciously says:
The procedure declares an object Employee as a new instance of clsEmployee. It then assigns values to the four properties of the object and generates a message box displaying the employee name and weekly pay. The object’s method, EmpWeeklyPay, is used to generate the displayed pay.
I'm stuck here people
As far as i know when we put (an original VBA-made and not a custom one from the class module) a property after an object for example Application.Name ...VBA returns a String value that represents the name of the object. OK, so VBA somehow knows how to do this. But I just cannot understand how is it possible in the books paradigm to create a custom property just by typing with nothing hard-coded
Public EmpName As String Public EmpID As String Public EmpRate As Double Public EmpWeeklyHrs As Double
...just as Name property differs from Value property, what creates up there 4 different properties? I mean how are their mechanics are different? What makes property EmpName different to EmpID??? For all I know they are just variables declared in the class module. How can we create custom object properties just by declaring variables in the class module??? Shouldn't we somehow input the mechanics, a demarcation, the chassis if you will, so when I use/set EmpID it will go to look the employees ID and not his name. 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. There's surely something hardcoded that makes Value and Name property different how can we only declare variables in the custom made object? it makes no sense to me
thank you for watching my long long long question
Based on my understanding of what you are asking, here is my answer:
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.
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.