Daily Dose of Excel

The purpose of this article is to introduce various capabilities of a class property. It is not meant to serve as an introduction to classes and objects. See the references section for introductory pages.

The typical use of a Class Property is to assign a value or to query its value as in the example below that defines the radius of a circle. This code would go in a class module named clsCircle.

Here are three things to consider. First, there are two different procedures, one to query the value and another to assign it. Second, there’s no requirement that there be only one statement in the Get or the Let procedure. In fact, there could be just about any number of statements in either procedure. Third, there’s no rule that both the Get and the Let procedures be present. These three together mean there's a lot more one can do beyond the basic use of a property. In this document we will explore some of the possibilities. There is one critical weakness in how properties work and we will explore a workaround towards the end of this document.

Validating a property value

The first improvement is to validate the value of a property. In the case of a circle, the radius cannot be negative. So, we can enhance the code to ensure that it is not.

Creating a Write-Once-Read-Many attribute

We can modify the above code to allow the Radius property to be assigned a value only once.

Verify that a Property has been initialized

We can use the same concept as above to verify that a property has been initialized. Thus, the property value is returned only after it has been initialized.

A “Virtual” Property

A property doesn’t have to have a variable associated with it. The Radius property has a private LclRadius variable that holds its value. But that is not strictly necessary. Consider a circle's Diameter property. Since it is simply twice the radius there is no need for a separate variable to contain the diameter.

Using Properties inside the Class Module

The above example also illustrates another useful point. Even though the variable LclRadius is available to all the procedures of the class one can always use the actual property itself. In fact, unless there is a compelling reason not to, one should always use the property since this has the benefit that any additional code for the property (such as the validation of the radius) will be correctly executed.

A Public vs. a Private Property

There may a valid reason for creating a property such that it is available to other members of the class but not to a client. This is accomplished by declaring the corresponding Get or Let procedure private. Consider the Diameter property from above. Clearly, it makes good sense for the client to know the diameter of the circle. So, the Get procedure should be public (the default). Also suppose we decide that the client cannot change the diameter (all changes should be through the Radius property). At the same time, we decide that procedures inside the class itself should be allowed to change the Diameter property. We accomplish this by making the Let procedure private as in:

Now, code within the class can set the Diameter value with something like:

However, a client would be unable to do something like the below since it would result in a compile time error “Method or Data Member not found”

Referring to an instance of the class (the Me object)

The Me keyword is the way code inside the class module can refer to the instance of the object created from the class. One could call it a “self reference,” I suppose. Me is the equivalent of a client referring to a variable of the class. VBE’s Intellisense capability will show the same properties and methods that a client would be able to use.

The use of Me is relevant in the context of a private property since a procedure in the class module can refer to a private property such as Diameter above. However, assigning a value to the Diameter of the Me object would fail since Diameter is publicly read-only.

Read-only Property

Since the Get and Let property procedures are separate entities, one can always exclude one (or the other). To implement a read-only property, simply exclude the Let procedure. In the case of the circle class, once the client specifies the radius, other properties such as the area or the perimeter are easy to calculate. However, if we assume that the client cannot specify the area (or perimeter) directly, we can create read-only properties with

Similarly, one can implement a write-only property by creating the Let procedure but excluding the corresponding Get procedure.

Property with an argument

Just like a subroutine or a function can have one or more arguments passed to it, so can a property. Suppose we want to provide a property that returns the length of the arc corresponding to a specified angle. The length of the arc is calculated as the perimeter / (2*Pi) * angle of the arc, which is also the same as radius * angle of the arc. So, we would get the property

Similarly, a Let procedure can also have an argument list. In the case of a property where the Get procedure has zero arguments, the corresponding Let procedure already has 1 argument, the value of the Let assignment. Similarly, when the Get procedure has an argument list, the corresponding Let procedure has 1 more argument than the Get procedure. The value of the Let statement is the last argument. So, if we were to allow the client to specify the radius of a circle through the ArcLen property – keeping in mind that while it helps demonstrate this capability it is not really a good idea for a ‘production’ system – we might have something like:

Raising an Error

Just as we can raise an error in any procedure in our code modules, one can also raise an error in a class module. Suppose we decide to replace our Radius property’s Get procedure so that it raises an error if Radius is uninitialized.

Now, if we were to query the value of the Radius property before assigning a value to it, we would get a runtime error.

Sample Use of the circle’s properties

In a standard module, enter the code below and then execute it. It creates a circle of radius 1 and then displays its diameter, area, perimeter, and the length of the arc corresponding to 1/4th the circle.

Difference between Set and Let property procedures

Suppose we have another class, clsPoint, that contains 2 properties, the X and Y coordinates of the point.

Now, in our clsCircle class, we could specify the center of our circle as:

Note that the Get procedure can Set the property. However, if we used a Let procedure and tried to Set the module variable, it would not work. Try it. Instead, one must use a Set procedure as in the above example.

We can now extend the testCircle subroutine (it’s in the standard module).

Creating private “property variables”

One of the biggest weaknesses in the current implementation of a class is that any variable associated with a property must be declared at the module level. This makes the variable visible to and, worse modifiable by, any code anywhere in the module. Essentially, the variable is global to the entire module.

One generic way to make a variable persistent but not global is to declare it as static inside a procedure. That, of course, does not work with a Property since typically there are two procedures associated with a property (a Get and a Let or a Get and a Set). But, what if our property procedures called a common private procedure? Then, we could declare our local variables in this common procedure.

Create a function that declares the variable(s) associated with a property as static within its own scope. Now, the only way to access the variable is through the function – and the function can contain all the code required to assign or query a property value.

The variable LclCenter above is private to myCenter. No procedure in the module can directly access LclCenter. All access has to be through myCenter; we have cut off unrestricted access to the variable.

One can verify the above works by simply running the testCircle code (without making any changes to it). You will get the same result.

I had hoped that with the .Net declaration of a property one would be able to declare variables local to it but unfortunately it remains impossible. The result of the below is a syntax error on the dim X... statement indicating the declaration is not allowed in the Property.

Summary

There’s a lot one can do with a class property beyond just associating it with a variable. The list includes, but is not limited to, introducing data validation as well as implement write-once or read-only (or write-only) properties. One can also restrict the scope of variables associated with a property.

This document shared some ideas on the subject. For those wondering, yes, I can think of some possibilities that were not discussed here. Of course, I am sure there are even more possibilities that I haven’t thought of.

References

There is much information on the subject of classes and objects. Just search Google. Two introductory topics I found -- and I don't know how the compare with other information on the subject -- are Dick Kusleika's blog post at http://www.dailydoseofexcel.com/archives/2004/09/28/classes-creating-custom-objects/ and Chip Pearson's introduction to the subject at http://www.cpearson.com/excel/Classes.aspx Chip addresses a couple of the issues addressed above as well as topics I opted to exclude from this article.

I answer about four questions a month on the newsgroups. I'm off to a roaring start in April, but I don't like my answer much. I never know just how "correct" an answer to give. So I gave an answer that I felt was appropriate for the original poster and I'll give a slightly different answer here.

The OP has an external query pulling stock quotes from Yahoo! Finance. He's using their csv download option, but just querying the csv rather than downloading it and loading it into Excel. One problem with that is that it puts all of the data in one column. If there's a built-in way to fix that, I don't know it. The other problem is that the list of companies he wants to see is variable and lives in a range of cells. I actually don't know if these are his problems, but I like to pretend.

My objectives are to create a web query to Yahoo, add a parameter that points to a range, and parse the data into multiple columns. Does that sound like fun? Well, I'm doing it anyway.

I can't create the query the old fashioned way. The web query user interface is like a browser; if you try to point to a CSV file, the browser wants to download it. So I create a web query to whatever table is on whatever page that happens to open up. Basically, I'm creating a query that I will later edit to point to where I want.

I pick any old arrow on the Dell website and create a query. I get a message that my query doesn't return any data, but I just OK past that message because I know I'll be making changes. Next I go to the VBE, and specifically the Immediate Window, to change my query.

The part you can't see says this:

wshquotes.QueryTables(1).Connection = "URL;http://quote.yahoo.com/d/quotes.csv?s=^GSPC[""EnterTicker"",""Ticker""]&f=nl1c"

If this URL isn't exactly what you want, don't worry. Alex has done all the legwork that will allow you to get whatever data you want. For me, I'm getting the name, last, and change for the S&P500 Index plus whatever else I enter. The "whatever else I enter" part is the parameter and is the name/prompt pair of strings inside the square brackets. When I Refresh the QueryTable, it asks me for a Ticker. I enter MSFT and the web query returns some data.

I have a parameter, but it doesn't point to a range, so that's next. The range B1:B10 will hold whatever ticker symbols I want and D1 will put them in the format I need for the URL.

The formula in D1 is ="+"&B1&IF(NOT(ISBLANK(B2)),"+"&B2,"")&IF(NOT(ISBLANK(B3)),"+"&B3,"")&IF(NOT(ISBLANK(B4)),"+"&B4,"")
&IF(NOT(ISBLANK(B5)),"+"&B5,"")&IF(NOT(ISBLANK(B6)),"+"&B6,"")&IF(NOT(ISBLANK(B7)),"+"&B7,"")&IF(NOT(ISBLANK(B8)),"+"&B8,"")
&IF(NOT(ISBLANK(B9)),"+"&B9,"")&IF(NOT(ISBLANK(B10)),"+"&B10,"")

Inspiring, huh? If a ticker exists, it puts a "+" in front of it and adds it to the list. Now I can click on the Query Parameters button on the External Data Toolbar and point to D1.

I also click on the Data Range Properties button and check the box to make the table automatically refresh.

OK, I have a parameterized web query that refreshes automatically. Unfortunately, it gives me this

Everything in one column. Yuck. Time to get fancy. I know that Text to Columns will parse the results, but I'm not going to manually do that every time the QueryTable refreshes. Fortunately, QueryTables have events. Two events, to be precise. I'll be using the AfterRefresh event. I'll bet you can guess what the other one is.

Other than out brief jaunt into the Immediate Window, we've been pretty much in Excel's UI up until now. It's time to step through the looking glass into VBA and (gasp) class modules. Open the VBE (Alt+F11) and add a class module and a module to your project. Name the class module (F4) CQTEvents and name the module MEntryPoints. In your class module, put this code:

Of course I never use public variables in a class because I'm a class module snob. So I have a private variable declared WithEvents that exposes the events of the QueryTable variable. Because it's a private variable, I need public get and set statements so code outside of the class can access it. Finally, I create the AfterRefresh event (using the code pane drop downs) and simply call the TextToColumns method to parse out the QueryTable results. If you try to TextToColumns into a range that already contains data, you'll get a message. I use DisplayAlerts to avoid that.

The only thing left is to tell my class which QueryTable I care about. I do that in the standard module with this code:

That's so easy I don't even have to explain it. Run Auto_Open and wait for your QueryTable to refresh. Then go fix your typos and you're all set. And you thought class module were difficult. If you're following along at home, be sure to do this during trading hours. Having those one minute updates where nothing changes isn't too exciting.

I have four objects that are all dependent on each other. They are CInvoice has many CInvoiceLines, CInvoiceLine has many CRoyaltyLines, CRoyaltyLine has many CRoyaltyAdjustments. In order to properly terminate all of these class modules, I need to remove any and all dependencies. If I don't, the classes will stay alive and consume memory. With four levels like this eating memory, I will eventually have to answer the question: Do I want to send an error report?

I think I have my termination sequences right, but I'll let you be the judge in the comments. I start in the CInvoice class, which I'll call a Parent class. It has many children, including the CInvoiceLines class (a child collection) and each individual CInvoiceLine class (a child class). The CInvoiceLine class then becomes a parent class for my next round of terminations. His children are the CRoyaltyLines class and each instance of the CRoyaltyLine class.

All of my Terminate events are structured like

I don't use the built-in class terminate event because it doesn't fire at the right time. When I'm in a parent class, I do these three things:

1. Call the Terminate method of the Child Collection Class
2. Set the local Child Collection Class variable to Nothing
3. Set the local Parent variable to Nothing

Items 1 and 2 are done in a parent class that is not also a child class. Item 3 is done in a child class that is not also a parent class. All three items are done in a class that is both a parent and a child. For instance, CInoviceLine is a parent with respect to CRoyaltyLines, but a child with respect to CInvoice, so all three steps must occur.

When I'm in a Child Collection Class (ex: CInvoiceLines), I do these three things:

1. Call the Terminate method for each member of the collection
2. Set the local Parent variable to Nothing
3. Set the local Collection variable to Nothing

When item 1 is executed, it may be terminating a class that's a parent class and the whole things starts over again. Here's an example: In the CInvoice class

Since mobjLines is a CInvoiceLines object, that Terminate method gets called first. In the CInvoiceLines class

When mcolLines.Item(i).Terminate is called, it's calling the Terminate method of a class that's both a child and a parent. In CInvoiceLine

The only difference between this and CInvoice is that I killed the parent variable because it's also a child class. I won't go through the rest, except to show you the last class, CRoyaltyAdjustment

Since this is a child class, but not a parent class, only killing the local parent variable is necessary.

Boy, there's nothing more thrilling than terminating classes, is there? That's why I added the image - to spice it up a little. For a little background, I'm abstracting my relational database into objects in VBA. That way I can reference clsInvoice.Lines(1).RoyaltyLines(2).Adjustments(3).Amount, which I contend is easier to code and read. But the setup is a real pain.

You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.

What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn't very easy, since there are a zilion ways to paste.

I have put together a sample workbook and an explanatory article on how one could go about protecting a workbook by intercepting paste operations.

Let me know what you think!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

I have two custom add-ins loaded that use application-level events. I only want those events to run when a workbook associated with my application is active. Otherwise, the events in my purchase order application will try and do stuff to my invoices and vice versa. Not good.

For every application, the first thing I do is test to make sure I'm dealing with an appropriate object; be it a sheet, workbook, or whatever. I use two utilities to do the testing. The first utility verifies an open workbook and the second verifies a closed one.

As described in Custom Document Properties, I use custom document properties in my templates to identify the workbook as being part of the application. The utilities check the property and return True if it's there.

In IsOpenInvoice, the property value from the supplied workbook is set to a Boolean variable. If the property value is False or if the property doesn't exist, the variable is False. Of course the property value will never be false. I created the properties in the template and set the value to True. It will either exist or not.

For closed workbooks, IsInvoice uses FileSearch to find a file with the proper name and with the correct property. The argument sName is the full path and name of the file. If the file exists in the directory and has the property, True is returned. This is typically used before a file is opened, such as after GetOpenFilename is used but before the file is actually opened.

Both functions have an optional second argument, sProperty, that defaults to the property name that identifies the application in general. I also use these utilities to identify particular templates within my app. I may need to know, for instance, if the open workbook is an invoice, a sales order, or a report in particular, rather than just part of my app in general. I have constants set up for each template type that I want to test.

Here's an example of an application level event in a custom class module that uses IsOpenInvoice:

I have a custom class, CGroup, which has children of the type CContact (another custom class). I need to loop through all the CContacts for each CGroup and print out those that meet a certain condition - in this example, those with State property of "NE". Not all CGroups will have a CContact that meets the condition. The CContact class has Name, Company, and State properties. The CGroup class has a Name property and Contacts collection. In addition, it has a read-only ContactsState property that returns a subset of the Contacts collection that have the provided state.

The data looks like this:

The final report looks like this:

In my main sub, I could loop through all of the CContacts for each CGroup and test the State property. If I do that, I need to know beforehand how many CContacts I have in that CGroup so I can tell it when (or if) to print the dotted line that separates them. I could create a property in CGroup that returns that number, but as long I'm looping through the whole collection, I figured I might as well grab the ones I need and return a collection with only those CContacts. I'm not sure if this really saves any cycles because I'm creating a new collection and adding to it rather than just increment a Long counter. But it does have the added advantage of cleaning up the non-class code by removing all that testing for the State.

By working with the limited collection, colConState, it's easy to determine when I've reached the last CContact with NE for the State. Since colConState is an empty collection for Group2 (because it has no CContacts with NE), the inner loop never gets run. Even though I don't show it here, it's probably more important that I properly destroy all of my class references using this method.

You can download LimitChildren.zip to see the whole thing if you like.