Storing meta information about Excel cells

Content

Background

While I was designing my Spill Manager for Excel add-in I needed a way for the add-in to quickly find spill ranges and to remember which spill ranges the add-in should manage (which spills it needs to watch for size changes). I needed to be able to:

  1. Locate a spill's top-left cell. This has to be reliable, if the user inserts or removes columns or rows or cell blocks, the spill's top-left cell still must be simple to find
  2. Store spill setttings:
    1. whether to copy formatting across columns or down rows
    2. whether to clean the formatting outside of the spill on a shrink
    3. whether there is a header row/column to account for
    4. whether there is a totals row/column
    5. whether the spill range needs to be updated on each change automatically
    6. the last size of the spill range, to be able to detect a change in dimensions and act accordingly
  3. Save and retrieve the information easily.

Method

At first I thought I'd use a set of range names per spill area, a range name for each of those settings. That posed a couple of issues:

Then I had an idea: I'll use the LET function for this purpose. After all, a range name is nothing more than a named formula. Any valid formula you can write in a cell, can also be put in a name. Spill manager now keeps one range name (local to the worksheet in question) for each spill, using a formula like this one:

Name: Sheet1!_SpillManager_Watch_19dd36c539f_5798b737

RefersTo formula:

=LET(rng,'Spill Manager Training'!$B$3,direction,"row",cleanupOnShrink,1,hasHeader,1,hasFooter,1,liveUpdates,1,lastRows,5,lastCols,6,rng)

Note how the final argument of the LET function repeats the first one, so the range name returns the spill range's top-left cell. This means I can use the range name in a formula exactly the same as if the name referred to the range directly. It also means VBA code like this "just works":

Set rng = Range("Sheet1!_SpillManager_Watch_19dd36c539f_5798b737")

And rng will now contain a direct reference to the spill anchor.

Implementation

All we need now is some code that parses the LET formula to extract the properties it stores. For that I devised a class module. The class uses property names which match those in the LET formula above exactly. This means we can use the CallByName VBA function to dynamically set the properties in a simple loop. Here's that class. I called it "clsGetMetaFromCell". The ExtractProperties routine does the heavy lifting in retrieving the settings from the formula.

Option Explicit

Private mscleanupOnShrink As String '1
Private msdirection As String '"row"
Private mshasFooter As String '1
Private mshasHeader As String '1
Private mslastCols As String '6
Private mslastRows As String '5
Private msliveUpdates As String '1
Private moWatchedCell As Range
Private msmetaNameFormula As String

Private Sub ExtractProperties(rngName As String)
    'Example formula in named range
    '=LET(rng,'Spill Manager Training'!$B$3,direction,"row",cleanupOnShrink,1,hasHeader,1,hasFooter,1,liveUpdates,1,lastRows,5,lastCols,6,rng)
    Dim items As Variant
    Dim ct As Long
    'Since the LET function returns the range, we can retrieve the spill cell by evaluating the formula:
    Set WatchedCell = Range(rngName)
    items = Split(msmetaNameFormula, ",")
    'Now extract all properties we need, counting from the right
    'Since the sheet name can contain a comma we extract only the other properties.
    'The following starts from the two-but-last comma, which is the word "lastCols" in the formula:
    For ct = UBound(items) - 2 To 3 Step -2
        'We deliberately use the same property names in this class as we have in the LET named range
        'So we can use CallByName to set the values.
        'Because of this we need all properties to be string properties
        'It is left to the user of the class to do conversions where needed
        CallByName Me, CStr(items(ct)), VbLet, CStr(items(ct + 1))
    Next
End Sub

Public Sub UpdateProperties2Name()
    'This routine builds a new RefersTo formula and updates the name. That is relatively straightforward,
    'so I left that for you to do :-). I don't think we can get around hard-coding the property names here though.
    'One needs to call this routine from outside of the class after updating properties.
End Sub

Public Property Get cleanupOnShrink() As String
    cleanupOnShrink = mscleanupOnShrink
End Property

Public Property Let cleanupOnShrink(ByVal newValue As String)
    mscleanupOnShrink = newValue
End Property

Public Property Get direction() As String
    direction = msdirection
End Property

Public Property Let direction(ByVal newValue As String)
    msdirection = newValue
End Property

Public Property Get hasFooter() As String
    hasFooter = mshasFooter
End Property

Public Property Let hasFooter(ByVal newValue As String)
    mshasFooter = newValue
End Property

Public Property Get hasHeader() As String
    hasHeader = mshasHeader
End Property

Public Property Let hasHeader(ByVal newValue As String)
    mshasHeader = newValue
End Property

Public Property Get lastCols() As String
    lastCols = mslastCols
End Property

Public Property Let lastCols(ByVal newValue As String)
    mslastCols = newValue
End Property

Public Property Get lastRows() As String
    lastRows = mslastRows
End Property

Public Property Let lastRows(ByVal newValue As String)
    mslastRows = newValue
End Property

Public Property Get liveUpdates() As String
    liveUpdates = msliveUpdates
End Property

Public Property Let liveUpdates(ByVal newValue As String)
    msliveUpdates = newValue
End Property

Public Property Get WatchedCell() As Range
    Set WatchedCell = moWatchedCell
End Property

Private Property Set WatchedCell(ByVal oNewValue As Range)
    Set moWatchedCell = oNewValue
End Property

Public Property Get MetaName() As String
    MetaName = msMetaName
End Property

Public Property Let MetaName(ByVal newValue As String)
    msMetaName = newValue
End Property

Public Property Get metaNameFormula() As String
    metaNameFormula = msmetaNameFormula
End Property

Public Property Let metaNameFormula(ByVal newValue As String)
    msmetaNameFormula = newValue
    ExtractProperties newValue
End Property

A Class doesn't do much without implementing it, so here is an example routine that reads all names pertaining to my add-in:

Sub ProcessAllWatchedSpillCells()
    Dim nm As Name
    Dim ws As Worksheet
    Dim cMeta As clsGetMetaFromCell
    Dim metas As Collection
    Set metas = New Collection
    For Each ws In Worksheets
        For Each nm In ws.Names
            If nm.Name Like "*_SpillManager_*" Then
                Set cMeta = New clsGetMetaFromCell
                cMeta.MetaName = nm.Name
                'The next action also triggers the ExtractProperties routine inside clsGetMetaFromCell
                cMeta.metaNameFormula = nm.RefersTo
                'For demonstration purposes only:
                With cMeta
                MsgBox "Cell: " & .WatchedCell.Address(False, False, , True) & vbNewLine & _
                       "direction: " & .direction & vbNewLine & _
                       "footer: " & .hasFooter & vbNewLine & _
                       "header: " & .hasHeader & vbNewLine & _
                       "last cols: " & .lastCols & vbNewLine & _
                       "last rows: " & .lastRows & vbNewLine & _
                       "liveUpdates" & .liveUpdates
                End With
                'Store the list of properties so we can do something with them
                metas.Add cMeta
            End If
        Next
    Next
    'Now loop through them to act upon them
    For Each cMeta In metas
        'If we made a change to any property like
        cMeta.direction = "some other direction"
        'then we need to update the name
        cMeta.UpdateProperties2Name
    Next
End Sub

See, I left something for you to do. I bet ChatGPT and its comrades are perfectly capable of filling that gap.

Comments

Loading comments...