Things You Can’t Do

If you are familiar with an object-oriented programming language such as Java or Visual Basic .NET you might be waiting for me to discuss constructors, inheritance, and overloading. You’ll have a long wait, because Excel’s Visual Basic can’t do any of those. There are also some limits on things you might assume you can do from looking at the Excel objects. For instance, you can’t create default properties. Table 5-1 lists these language limitations and provides some detail.

Table 5-1. Object-oriented features not available in Excel

Feature

Limitation and workaround

Constructors

Only a default constructor is available. If you want to initialize an object, you must implement an Initialize method or something similar.

Destructors

Only a default destructor is available. If you want to free nonmemory resources used by an object, you must implement a separate Dispose method or something similar.

Collection types

There is only one collection type: Collection. To implement a collection, create a class that “wraps” that type as shown earlier in "Collect Objects.”

Default properties

Not available in custom classes. Properties must be called by name.

Inheritance

Not available. You can’t base one class on another.

Interfaces

Not available. You can’t create a prototype for a class.

Overloaded methods

Not available. Use the Optional keyword to create methods that accept different sets of arguments.

I include Table 5-1 because it’s hard to know what’s missing ...

Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.