How do Class definitions work in VBA

ExoticBirdsMerchant Source

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:

  1. We open a class module in VBE

  2. 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
    
  3. The book writes that the custom object is now complete

  4. 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
    
  5. 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
    
  6. 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

vbaclass

Answers

answered 5 years ago NoChance #1

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): enter image description here 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.

comments powered by Disqus