Should I have to have duplicate values in VBA clas

Question

I've been using VBA class modules in excel for a while now, but I'm not sure that I am creating them correctly. I normally create module level variables for the class and then property let and get functions. For example:

Private msRegion As String

Property Get Region() As String
    Region = msRegion 'Return the Region
End Property

Property Let Region(ByVal sRegionName As String)
    msRegion = sRegionName 'Set the Region
End Property

When I look at the objects in the local window I notice that for each property I end up with the module scoped variable and the variable required by the Let function. This seems like duplication of a variable to me. I'm concerned that if I instatiated a large collection of objects with several properties in each it could be costly in terms of resources.

I've tried to modify the code so that only one variable is in the object class, but so far I've only got error messages for my pains.

Does anyone know of a way to create properties in class modules that does not lead to duplicate variables?

Edit:

I have just had another look at my locals window and realised that argument to the property let isn't there. The 2 expressions appear to be the privately stored variable and the proprty get value, in this case msRegion and Region. That fits in with Cor_Blimey's answer.

Answer 1

Declan_K is correct in saying that a) you are doing right, and b) the Locals window ignores scope and shows them anyway.

To add a little to why it is good to seperate them out and have a public property and and a private member field:

1) You can have useful limited 'logic' in the Get/Set/Let. Perhaps you wish to store the value in a different format but always present it in another (e.g. store it as a Long number of milliseconds but always return in Double seconds), or perhaps you wish to validate the data before storing it.

2) You define a public 'interface' that represents a contract between your class and consuming classes. You therefore make a clear distinction between the internals of your class, that are free to change as you see fit, and the external interface. This may not be an issue in practice in VBA, as you are likely completely in control of all consuming classes, and VBA compiles on demand so it isn't an issue changing fields to properties and vice versa, but it is good practice and should help to enforce better encapsulation. It will also help you remember when you need to be damn sure that you aren't making a breaking change elsewhere in your Addin / program!

3) Properties basically don't take up additional storage space for the variable. The storage requirements in the case of having a public field and a property with private backing field are basically the same. If it helps you, think of it as a 'function' that returns the private value or a 'sub' that sets the private value.

4) You can actually save resources in some cases: consider a class that has properties that return objects (like a "Results" collection or something). You can keep the backing fields as Nothing until the object is actually needed, either through a method in your class or through a consumer requesting it via a property. When it is requested, you can then instantiate the object as necessary.

5) Seperate scope for the Setter and Accessor: Fields are either private or are public and so if it is public you cannot prevent, for example, a field being made to reference a new object that may or may not be inconsistent with the state your class expects. On the other hand, you can restrict a property to be Read-only etc (Note that this will not stop a read-only object from having it's properties changed - it only prevents the reference from changing). Again, as per point 2, this may not be an issue at all for you as it is within your project and only VBA, after all, but it should help to ensure that properties (in the looser sense) of your class that are integral features of your class are controlled by your class.

6) There are plenty of other reasons in other languages, but VBA does, it is true, have fewer.

On the other hand, it is a pain to type out so much text for something that you may or may not find useful!

Answer 2

Have you tried to get or set the msRegion property in your code that is calling the class? If you do you will notice that it is not callable.

You are structuring the class correctly.

The locals window shows all declared variables, including private varibale within a class.