Storing Objects as Class Properties

In most situations, Excel objects, such as shapes and buttons, perform simple functions and stand on their own. However, when stored within a class, the opportunity exists to supplement the functionality of custom objects.

Overview

This is the tenth post in the Tips and Tricks with VBA Classes series.

Last week’s post discussed storing arrays as class properties, where we reviewed expanding class functionality to store more complex data structures. This post expands that functionality further, by evaluating how to store other Excel objects.

Note on post terminology — there a few words which may seem interchangeable, but mean different things in context:

  • Class: the framework used by custom objects to dictate what it stores and does. Classes are built within a class module, which include member variables, properties, and member subroutines/functions. This post will refer to “class” when describing structure of a custom object, or elements within the class module.
  • Custom object: an instance of a class. Multiple custom objects are derived from a single class. This post will refer to “custom object” to describe to a specific instance of a class (object).
  • Excel object: refers to objects on the application-side (non-VBA-side) of Excel. These include elements of a workbook or spreadsheet, such as ActiveX controls and shapes. The focus of this post is storing Excel objects within custom objects. This post will refer to “Excel object” when describing objects outside of a custom object or about to be assigned to a custom object.
  • Sub-object: refers to Excel objects stored within a custom object (or class). Generally, This post will refer to “sub-object” when describing Excel objects that actively exist within a custom object.

Example File

This post’s example file continues on the theme of a region (collection class) storing data for multiple countries (standard class). A basic input form permits users to enter population and land area details for countries. Below the form, a scrollbar lets users adjust the population of any listed country. This also includes two rectangle shapes that present a graphical representation of a country’s population. Unique to this setup: both the scrollbar and shape are stored within a class (custom object).

A quick demonstration video:

 

Macros must be enabled upon opening the file.

Concept

When storing values or data structures in a class, class properties facilitate the reading and writing of class member variables. Traditionally, these member variables will store values of simple data structures (like arrays). However, member variables can also store native Excel objects, such as Workbooks, Shapes, and ActiveX Controls.

This approach may come in handy in situations where an Excel object is completely reliant on a custom object, or if a custom object requires an Excel object to accomplish a task.

Storing objects introduces the Set property, which replaces the Let property.

The Set Property

If you’ve ever attempted to store an Excel object within a class using the Let property, you’ve likely received run-time error 91: “Object variable or With block variable not set”. This error will not appear when using the Set property.

Run time error 91 appears when attempting to assign objects with a Let property.

The Let property is useful for storing data values in a class. However, objects require a Set property, which tells VBA to handle the identifier as an object. The general syntax for a Set property is straightforward — the “Let” simply needs to be replaced with “Set”. Detailed examples are in the following sections.

Storing Shapes as Class Properties

Two rectangle shapes exist within the example to present a graphical representation of a country’s population. The background shape is a larger, light blue rectangle that represents a maximum population of 100 million (arbitrary maximum for example purposes). The foreground shape is a smaller, dark blue rectangle that represents a country’s population. Together, the two shapes present an area chart that shows a country’s population in proportion to a maximum population of 100 million. With that, a country population of 90 million will fill in 90% of the light blue rectangle.

While the background (light blue) shape is in not tied to the class, the foreground (dark blue) shape is. This permits each object of the class to access the dark blue shape directly through its own properties and methods. In this example, the the class will resize the rectangle shape based on population. Note that multiple country objects within the region collection can use this same shape. In practice, the shape is shared by all objects within the collection, but it is only actively used by one object at a time.

Assignment of the shape to the class must be handled by a Set property within the class module, as follows.

You’ll notice that the property includes a Set statement in line 3. This tells the member variable mPopulationShape that it must be set as an object.

Within the class module’s declaration area, the member variable must be declared as the proper object type (in this case, Shape).

Given that the CountryGeneral class exists as a child class within the RegionCollection collection class, there are two ways of facilitating the object transfer to the property: through the collection class’ add/insert procedure (as a country object is being created), or directly through a standard module (after a the country object is created). For this specific example, we’ll pass the shape through the collection class’ Insert subroutine (we’ll discuss the alternative approach in the next example).

Within the collection class’ (RegionCollection) Insert subroutine, the Shape object must be set up as a subroutine parameter (line 1).

The argument passed through the populationShape parameter is assigned to the CountryGeneral class’ populationShape property in line 8. This is done as a Set statement, as an object must initially be set to any new identifier. Other than that, this is a standard add/insert procedure for a collection class.

Shape names can be updated in the name field, to the left of the formula bar.Before the next step, we need to provide the shape with a name that can be accessed in VBA. The default name will be either Rectangle1 or Rectangle2, but it’s worthwhile to provide a more descriptive name. This can be updated on the application-side of Excel, in the name field to the left of the formula bar. For this example, the shape will be named “Current_Pop_Shape”

Stepping back to the standard module, we include the shape as an argument in the call to the collection class’ Insert procedure. I’ve extracted the important lines and included them as a snippet here (the full subroutine can be viewed in the example file):

Again, the identifier passed as an argument (line 4) must be initially set as a shape object (line 3).

This approach is effective for adding a sub-object that exists at the time of a custom object’s creation, but requires coding in three different modules (standard, collection class, and class).

Storing ActiveX Controls as Class Properties

The example file utilizes an ActiveX scrollbar as an option for users to modify a country’s population. After initially entering a country in the first form, a dropdown list in cell B13 can be used to select a country to modify. With the scrollbar overlaying cell B14, users can increase or decrease the population by increments of 1 million (100 million max). Changing this will also drive an adjustment to the shape graphic.

For this implementation, the ActiveX scrollbar will be stored within the class. Before entering the VBA editor, the scrollbar should be named on the application-side of Excel. This can be done in design mode, by clicking on the scrollbar and updating the name field, similar to how the name was changed for the shape. In the example file, we name the scrollbar “Pop_Scroll”.

The mechanics of storing an ActiveX control are similar to the mechanics of storing a shape. It must be done through the Set property, and a Set statement must exist within the property. If you recall in the previous (Shape object) example, we transferred the shape to the class by way of the RegionCollection collection class’ Insert subroutine. For this specific example, we will skip that step and simply add the control to the CountryDetails object after its creation.

First, let’s look at the Set property.

The syntax of this Set property is not much different from the previous property used for the Shape object (previous section). The sub-object must be set to the member variable (line 3), and the parameter must be declared as the sub-object type. Note about ActiveX controls: they can only be passed as function/property arguments through the OLEObject object type. This object will provide us with access to all properties and members of ActiveX controls.

In the class module’s declaration area, the member variable must also be declared as a OLEObject:

Stepping into the standard module, the scrollbar can be assigned to the object with one Set statement:

It’s also possible to assign the control to an identifier (similar to how the Shape object was treated above) before assigning it to the property.

Output

In addition to general output, this class now provides users with the ability to update country population details, which in turn updates the graphical representation. These features are implemented with member subroutines within the class module.

Update Population

This member subroutine is accessed through scrollbar control (procedure is hosted in the Sheet1 Excel Object module). The subroutine will fire any time an incremental change occurs on the scrollbar.

The procedure serves two purposes. First, it updates the country population based on the new scrollbar value (line 3). Second, it calls the Output_Population_Graphics member subroutine from the same module, using the “Me” keyword.

Update Graphics

This procedure handles calls to the graphical and output procedures. The bulk of activity occurs in lines 10-15, where shape coordinates and population values are assigned. These are then passed as arguments to the Population_Square_Coords member subroutine, which modifies the actual shape. You’ll notice in line 18 that the scrollbar is updated. While this may seem redundant as we previously just updated the scrollbar, this particular line is necessary for whenever a new country is selected from the dropdown list — this will update the scrollbar to the proper population. Finally, the Output_Country subroutine (in the standard module) is called to reprint the data table.

Resize Foreground Shape

This procedure does the physical re-sizing of the foreground (dark blue) shape. The script determines the new dimensions for the dark blue shape by calculating population against the maximum (light blue) shape dimensions.

One thing to note here is that shape properties (Width, Height, Left, Top, Textframe) are joined directly with the populationShape property. These effectively act as sub-properties to the custom object’s properties.

Print Data

Finally, we move back to the standard module where a basic For loop is used to print or update country data in table format.

Full Class Module

Other Notes

  • To keep the example file simple, the graphical representation is presented as a proportion of a maximum population of 100 million. In practice, a more practical example would be to show the proportion of population compared to all populations in a region. I decided against incorporating this in the post, as it further complicated the example.
  • This example presents one of the ways VBA can mimic inheritance. While it is not true inheritance (where an object is fully bound to another object, and only that object), it does reflect a practice of information from one object affecting another object.

Leave a Reply