Saving Custom Object Data in a Workbook

Previous posts have discussed advantages to using custom objects and custom collections. When used as globals within a VBA, custom objects provide a means of storing complex data structures outside of a standard worksheet. The efficiency gains and ease of access can help you build robust programs. Unfortunately, these custom objects and collections will be removed from memory once a workbook is closed. Even with this fault, there are a few approaches to ensuring class data remains when the workbook is reopened.


This is the second post in the Tips and Tricks with VBA Classes series. Today, we discuss saving class properties to persist after a workbook is closed.

When it comes to custom objects with a global scope, the objects will persist only while the file is open and VBA continues to execute. If you stop all VBA scripts—whether manually or when a debug error occurs—all global custom objects will be eliminated from memory. Given that closing a workbook stops all VBA processes, custom objects and collections are cleared from memory. When you re-open the workbook, those global objects and variables will need to be re-created. For complex custom objects, such as our collection of objects containing data about countries of the world, this means re-creating all previously built objects.

Unfortunately there’s no simple command or function available to save properties assigned to custom objects. This post discusses three approaches of saving class properties using features within the Excel application. These are approaches that I typically use — I prefer them as I’m able to easily view saved data within the workbook. They also allow saved data to easily travel with a workbook if it’s sent over e-mail.

There are other approaches and techniques, such as writing object data to external files or the system registry. These approaches will not be covered in this series.

Example File

We go back to the example file used in the previous post. This file contains the Interface coding module with some minor changes, and a new second coding module, Data_Management, to handle all the save, load, and delete operations. Macros must be enabled upon opening. See the previous post to learn more about its general functionality.

Worksheet Cells

The most straightforward means of saving class data is directly on a worksheet. This is done by treating an empty worksheet as a data table by storing collection objects on rows, and properties in columns.

Within the example file, you can see the saved data in the “Data Sheet CountryDetail” worksheet.


To save class properties to a workbook, loop through the entire collection in a For Loop. I’ve included a second counter variable, j, to increment starting at 2, which permits the use of header rows.

When working with multiple collections, it’s worthwhile to save one collection per worksheet. The main advantage of this approach is its ease in organizing data and presenting headers. If you’re not concerned about easily viewing/modifying saved data and want to store multiple collections on a single sheet, the worksheet strings approach (see next section) may be a better alternative.


If the saving action had us assigning object properties to cell values, the load action has us assigning cell values to object properties. In practice, this operation will typically be done after re-opening a spreadsheet — whether through a Workbook_Open event or a manual trigger. The mechanics have changed slightly when compared to saving data, as we can no longer rely on the count of the collection, given that the collection will not exist prior to loading. To adjust for this, a Do Loop (line 12) iterates down the worksheet until it reaches an empty value.

Instead of assigning the values directly to class properties, we use the class’ Insert member (line 13) to add a new record to the collection for each row of data.


If you provide users with a means of saving a custom objects, it’s a good idea to allow users to delete them as well. Typically, this is done by setting the collection object to “Nothing”, as is done in line 6 below. However, because the data is saved to the worksheet, the user could mistakenly re-load data to the collections. This could lead to frustrating situations where the user thinks data is deleted, but it inadvertently appears at a later time. Given this, it’s important to delete the saved data from the worksheet. This can be done with a Do Until Loop (line 8), which deletes all rows until empty. You may notice that a counter is not used in this loop — remember that deleting an entire row in Excel moves all rows up one. The loop will terminate once the first empty row “arrives” at row 2.

Worksheet Strings

This approach is similar to the worksheet cells method with one key difference. Instead of saving properties in specific columns on a spreadsheet, all properties of individual objects within the collection are saved as a String in a single cell (each object in the collection will continue to have its own row). A delimiter is used to convert the String to an array when the objects are loaded.

This approach is more effective for storing multiple collections, as it does not rely on individual columns to store each property. It’s also useful for objects and collections which store dynamic data, such as arrays or sub-collections. Different delimiter types can be used to manage dynamic content in loading operations (this will be discussed in a later post).

One drawback relates to a visual limitation within Excel. While an individual cell can hold up to 32,767 characters, you will only be able to see 1,024 characters on the screen. Anything after 1,024 is visually cut off, although it still exists. If you rely on the saved data to clean or check data integrity, the worksheet cells approach (above) might be a better alternative.

Within the example file, you can see the saved data in the “Data Array CountryDetail” worksheet.


The save procedure loops through each object in a collection (line 10) and creates a concatenated String of all object properties (line 11). Using the collection’s index number, the string is then added to a corresponding row in the worksheet (line 11).

While not required, you will notice that I added the collection name–CountryDetails–to the beginning of the concatenated string. This will be useful if we later decide to store more than one collection on the worksheet (will be shown in a future post).


The load procedure uses a Do While Loop to iterate through each string until it reaches an empty value. Two data preparation operations must take place before running the CountryDetails object’s Insert method.

  1. Within the loop, use the intrinsic Split function (line 14) to convert the string into the array saveArray.
  2. On the following line (line 15), each component of the array is passed as an argument in the CountryDetails collection’s Insert subroutine. There’s a catch here — because the array saveArray is a Variant data type, individual elements of the array must be converted to their proper data type prior to adding to the collection. This is done with type conversion functions, in this case CStr, CLng, and CDbl.


The delete procedure is very similar to the same procedure for the worksheet cells storage method. Simply loop until an empty row is reached after deleting entire rows. This version of the procedure focuses on row 1 (not row 2) as we did not implement headers in this approach.

Named Constant String

The named constant string approach is similar to the previous worksheet strings approach, with one key difference: the strings are stored as named constants. While the idea is similar, the approach relies on iterating through Excel’s Names collection instead of cells in a worksheet.

Saved strings can be viewed in the Name Manager. This is found in the Formulas tab.

Name manager can be found in the Formulas tab.

Within the Name Manager dialog box, the saved named constants will appear along with all other items in the Names collection.

Named constants are given the prefix CountryDetails to distinguish them from other items in the Names collection.
Named constants are given the prefix CountryDetails to distinguish them from other items in the Names collection.

This approach does not require additional worksheets to save data, but there is a serious disadvantage: named constants can only contain up to 255 characters. While this may meet your initial requirements, scope creep and longer-than-expected strings may derail your plans at some point.


  1. The “Names” collection also contains a “Name” property which stores a label for each item in the collection. To avoid confusion, text will be italicized when referring to the “Name” property.
  2. While learning this approach, it’s important to distinguish between the Names Collection and the CountryDetails Collection. The Names collection is used to store the data in a string (for future conversion into an array), while the CountryDetails collection contains all the objects of country details.


Like the two prior approaches, we rely on a For Loop to iterate through each index in the custom Collection we intend to save. Within the loop, the Names collection Add method adds values to its Name and RefersTo properties.

The Name property includes the name of the CountryDetails Collection as a prefix, then a concatenated country name. This allows us to distinguish between other items in the Names Collection, permitting the storage of multiple named constants, and ensuring that a unique label is applied to each entry.

The RefersTo properties will store the string of properties. The “|” (pipe) character is added as a delimiter.


The load procedure for named constant strings is where things get quite different. No longer are we iterating through a spreadsheet with a Do Loop. Now, we must use a For Each Loop (line 11) to cycle through each item in the Names Collection. Additionally, we must include a conditional to distinguish between items in our collection and normal named references. This is done by using the Name property and comparing its first 14 characters to the string “CountryDetails”, which is the name of the collection (line 12).

Excel adds some extra characters (equals sign and quotation marks) when creating a named constant, so additional data cleaning is required when converting the string to an array. Four data preparation operations must take place before running the CountryDetails object’s Insert method.

  1. Remove quotation marks from the stored string using the intrinsic Replace function (line 13) — This removes the leading and trailing quotes Excel adds to the string when the Name is created.
  2. Convert the string to an array using the Split function with “|” (pipe) as the delimiter (line 13).
  3. Remove the equal sign from the left of the first element of the array using the intrinsic Right and Len functions (line 14).
  4. Use the appropriate data type conversion function (CStr, CLng, CDbl) on each array element (line 14), prior to passing as arguments through the Insert member subroutine. This resolves any “Data Type Mismatch” errors as each element of the array was originally assigned as a Variant data type.


The delete procedure operates within the same loop structure as the loading procedure — a For Each Loop (line 5) is used to iterate through the Names collection, and a conditional within the loop (line 6) ensures that no other named references or constants are inadvertently impacted. Within the conditional statement (line 7), the Delete method of the Names Collection deletes the named constant. The procedure finishes by setting the CountryDetails collection to “Nothing”.


Other Notes

  • You may notice that the countryPopDensity property was not saved in the examples above. This is because the property is generated by a member subroutine — as part of the class’ Insert procedure, countryPopDensity is computed, then added as a property. This is a matter of preference and performance. If countryPopDensity were a much more complex computation, I may have opted to save and load the property to avoid repeat computations.
  • Regardless of the approach, the Load subroutines in these examples all begin by setting the object to “Nothing”. This approach prevents errors from occurring if a user reloads the same existing object.
  • A future post will discuss deleting individual objects (and saving the updating the collections) from class collections.

Leave a Reply