Storing Arrays as Class Properties

There may be situations where a single class property is needed to store multiple values. This can support a list of data or provide the foundation for a loop. VBA classes provide the framework to store arrays as class properties.

Overview

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

If you’ve used VBA classes in the past, you’re well aware of their primary function — storing data. Previous tutorials on classes have focused on storing basic data types, such as simple Integer or String values. Today’s post provides an overview on storing arrays as class properties.

Example File

We continue to use the region/country collection in the example file. Macros must be enabled upon opening the file.

The example file’s concept focuses on an array to store a list of languages commonly spoken in each country. Cell “B6” provides an input area for languages. Users can include multiple languages by separating them by comas. A Split function within the procedure then converts the String into an array (separating at comas) of the Variant data type. Finally, when the array is printed, it is converted back into coma separated String for display.

This post discusses three different approaches to storing arrays as class properties. When operating the example file, all three approaches are used when adding a new country. Each approach works independently and prints out put in separate columns (G, H, and I).

The objects (countries) storing the array exist within a larger collection (region). While this example uses a collection of objects, the concepts in this post also apply to stand-alone objects.

Storing Arrays as Class Properties

The storage of an array within a class derived object is the easy part — we can store it in a member variable as if it were a basic Variant data type. The challenge relates to transferring the array from the procedural code, to the member variable. The class’ Let property will be used to facilitate this transfer.

In a previous post, we discussed expanding the functionality of class properties. We build on that concept in this post — using properties to facilitate storage of arrays requires some additional coding.

This post provides an overview of three different approaches of storing arrays as class properties. The three approaches vary by ease of implementation and flexibility.

Directly Assign Array to a Property

In their basic form, arrays can be assigned to a class property similarly to any alpha or numeric data type. The one catch is that the associated Let and Get properties, as well as the member variable, must be declared as a Variant.

In the example file, the array assignment takes place within the collection class’ Insert member subroutine. The array is passed from the standard module, to the Insert subroutine through the countryLangArrayPass argument (also declared as a Variant). The array argument is then assigned directly to the object’s countryLangArrayPass property.

This array assignment could have just as easily been done in a standard module by pointing to a specific object within the collection (this is shown in the following examples). Regardless, this is a useful approach if an array must be added immediately after an object is instantiated.

Within the child class (CountryGeneral), a normal looking Let property will handle the array. Note the parameter Value is declared as a Variant.

This approach is ideal for a quick implementation. Outside of declaring the data types as Variant, there’s nothing atypical involved in this implementation. One drawback: the array stored in the class can be replaced, but it cannot be modified.

Build Array within a Property Using a String

Another approach involves building an array within a class property. There is only one difference between this approach and the previous “Directly Assign” approach — we move the Split function from the standard module into the class’ Let property.

Even though we are passing a String value through the property, we still need to declare the argument Value as a Variant data type within the Let property. VBA requires that the Get property’s returned data type be identical to the Let property’s argument data type. Given that we would use the Get property to access the array later, and arrays can only be returned from properties as Variants, we must use the Variant data type in both the Let and Get property.

Within the property, the Split function converts each coma separated value into an element of an array. This array is then stored in the mCountryLangArrayString member variable declared at the top of the module. One limitation of this approach is that some older versions of Excel limit the character length of property arguments.

In the example file, we include the assignment within a standard module, not the collection class’ Insert subroutine (as was done in the first approach). The assignment can be seen in the Inputs_Country subroutine within the “Interface” module. A snippet is included here.

The first line reads the user-entered coma separated string from the spreadsheet. The second line assigns the value to the property. Note that no array processing takes place in these two lines — variable countryLangArrayString is a String data type until it reaches the countryLangArrayString property.

Similar to the previous method, the array stored in the class can be replaced, but it cannot be modified.

Build an Array Incrementally within a Property

The previous two approaches lack some flexibility, specifically if a stored array needs to be modified. In those examples, the only way to mimic an array modification is to completely replace the array. This would mean calling the existing array from the Get property, making modifications within the standard module, then re-inserting the array through the Let property. When working with very large arrays, this may result in noticeable system overhead. One way around this is to modify an array directly in a class property.

The example file provides two mechanisms for adding to the array. First, initial languages are added right as a country object is created in the “Country Data” form. Second, new values can be added using the “Add New Language” form.

By building an array—value by value—within a property, we obtain full flexibility to add values at various points of an object’s existence. Let’s look at how we add the initial values, done within a standard module. The following snippet can be found in the Inputs_Country subroutine within the “Interface” module.

This borrows the countryLangArrayPass (the array created from the Split function used in the first example) for use in a For Each loop. Each individual value is assigned to the countryLangArrayBuild property. In a property’s traditional form, each iteration would overwrite the previous, but that’s not the case here. Let’s take a look at the actual property in the class module.

Obviously this is not the typical 1-line property procedure. This procedure accomplishes 4 primary tasks:

  1. Determine if the array has been set (line 5).
  2. Identify the array’s upper limit. If the array has already been set, add 1 to its upper-bound (line 6). If it has not been set, start at zero (line 8).
  3. Update the array’s dimension with a ReDim Statement (line 10).
  4. Assign the new value as the array’s new element (line 11).

These tasks work together to ensure that any new property assignments are added as a new element at the end of the array. Without this, each new language assignment would simply replace the existing value. To demonstrate this further, new languages can be added (after object creation) with the second form “Add New Language”. Use the dropdown list in cell “B14” to select an existing country, then enter a language in cell “B15”.

Form to add values to existing array within a class.

This form uses its own subroutine to handle interaction between the form and the class.

This subroutine simply reads the value in cell “B15” and assigns it to the property. The property will do its job (as explained above) and add the new value to the end of the existing array. It will never replace or fully overwrite the existing array.

One other note with this approach: the member variable for the array (within the declarations area of the class module) must include brackets at the end of its identifier. This permits the array to be resized in the class module (in the other two approaches, the Split function does this for us).

While we can add values, this example is not set up to delete or remove specific elements from existing arrays. This is possible, but it is not discussed in this post (maybe in a future post).

Output

Gathering an array from an object is straightforward — you simply assign the object/property to something and treat it like a normal array. The full output procedure in the example file is as follows:

If you look at the For Each statement on lines 19, 25, and 31, the property call is being treated just like an array (because it effectively is one). The For Each loop is used to build a coma separated String from the array, to be displayed on the spreadsheet. One For Each loop exists for each approach shown above.

Within the class module itself, the Get property to return arrays is structured as if it were returning an Integer or String. As discussed above, the array can only be returned as a Variant data type. Here’s an example of the Get property for the “array build” method. Properties for the other two approaches are nearly identical, only changing in semantics.

While not included in the example file, it is possible to point to a specific element within an array. The following snippet points to the first array element.

The property can simply be treated as if it were a normal array, with the index number placed within brackets at the end.

Full Class Module

Other Notes

  • Multi-dimensional arrays and other data structures can also be stored as class properties.
  • Next week’s post will focus on storing shapes, controls, and other objects as class properties.

Leave a Reply