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 have a cell with data validation. It's set as an in-cell dropdown list and contains two items: Yes and No. I want to make it so that I can double click on that cell to toggle between yes and no. But wait, that's not good enough. What about longer lists? Yes, I want something that will iterate through all the items on a data validation list by double clicking. Dare I dream.

Here's my first go at it. I figure it's going to need some work, like what happens when the user double clicks on something that's not a range, but it's a start. I've basically handled two types of lists: the kind where you hard code values separated by commas (international issue here?), and the the range reference. Oh, and I need to test named ranges, but I think they'll work.

This code is in the sheet's class module (Sheet1 in my case). Test it out if you like. Let me know if you see any errors or better ways.

P.S. Why is Target.Validation always something (that is, Not Nothing) even if the cell doesn't have validation?

In a previous post, I demonstrated how to use constants to improve your code. Then everyone started beating up my loop. The code wasn't solving a real life problem, so I just threw any old loop together. It wasn't relevant because that's not what the post was about. To fight back, I created a highly improbably backstory in the comments to make my loop look at least as efficient as everyone else's.

But no matter how crazy my story was, I couldn't subvert Peltier's comment about reading the range into an array. So I tried to see what kind of time differences we're talking about. I wrote this code:

And got these results:

The array is quite a bit faster. I don't think Timer is hyper-accurate, but relatively the differences are pretty clear.

Hi all

New page on my site with a very useful add-in from Jim Rech.
http://www.rondebruin.nl/vbebookmarks.htm

VBEBookmarks.dll is a simple COM Add-in created by Jim Rech for the Microsoft Office 2000-2007 Visual
Basic Editor which lets you bookmark up to five locations in VBA projects for easy navigation among them.

Have fun

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Do you know how windows appends that "on Ne01" to your ActivePrinter. In the old days we had to loop through all the possible digits to find which one didn't error out. Well no more!

Holger uses the registry to find the printer port. Very clever. However, if you have back slashes in your regestry key name, the scripting shell object won't work for retrieving them. To the shell, back slashes are path separators, so it's trying to navigate down some path that doesn't exist.

To overcome that problem, you can use Registration Manipulation Classes.

If your you're a late binding kind of a guy, use CreateObject("RegObj.Registry") in your code. With this dll, we can loop through all of the keys in a folder, like so:

I haven't tested that extensively, so use caution.

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.