LLChoices

A member that needs the four choice columns reads the header row once as a block and matches the names in memory for the rest of that call. Nothing is held between calls: AddChoice appends rows, RemoveChoice deletes whole rows and Import clears the sheet, so the header row and the end row are read again on every call.

This class leaves Application.ScreenUpdating, Calculation and EnableEvents where it finds them, and builds no ApplicationState.

Depends on: DataSheet, CustomTable, Checking, BetterArray, TranslationObject

Version: 1.0 (2026-02-09)

Create #

create

Create a new LLChoices instance

Signature:

Public Function Create(ByVal choiceWorksheet As Worksheet, _
                       ByVal choiceStartRow As Long, _
                       ByVal choiceStartColumn As Long, _
                       Optional ByVal choiceName As String = vbNullString) As _

Wraps the given worksheet in a DataSheet and returns a configured LLChoices instance. The four columns this class works with are "list name", "ordering list", "label" and "short label", and AddHeaders writes them to the sheet in that order.

Parameters:

• choiceWorksheet: Worksheet. The worksheet hosting the choices.
• choiceStartRow: Long. First row of choice data.
• choiceStartColumn: Long. First column of choice data.
• choiceName: Optional String. Name for the choice object. Defaults to vbNullString.

Returns: LLChoices. The new instance.

---

ChoiceData #

choice-data

Associated DataSheet object

Signature:

Public Property Get ChoiceData() As DataSheet

Returns: DataSheet. The backing DataSheet.

---

Wksh #

wksh

Worksheet hosting the choice lists

Signature:

Public Property Get Wksh() As Worksheet

Returns: Worksheet. The host worksheet.

---

Name #

name

Name of the choice object

Signature:

Public Property Get Name() As String

The lazy default is written straight into the backing field, so a Get can change the object. It has to be that way: Seal closes the Let, and the default can only be worked out once the worksheet is known.

Returns: String. The choice object name.

---

ChoiceExists #

choice-exists

Check whether a choice exists

Signature:

Public Property Get ChoiceExists(ByVal choiceName As String) As Boolean

Answers a quiet False when the list name column is missing or the name is empty. Matching is case-SENSITIVE here, which is not the rule Categories uses -- AutoFilter criteria are case-insensitive and cannot be made otherwise.

Parameters:

• choiceName: String. Choice name to search for.

Returns: Boolean. True when the choice is found.

---

Sort #

sort

Sort choices in the worksheet

Signature:

Public Sub Sort()

Sorts on the list name first, then on the ordering column. The header row is read once and the three ranges are built from the positions it gives; the old shape asked the worksheet for the same header row four times.

---

AddChoice #

add-choice

Add a new choice with its categories

Signature:

Public Sub AddChoice(ByVal choiceName As String, _
                      ByVal longLabelData As BetterArray, _
                      Optional ByVal shortLabelData As Object = Nothing)

Appends a new choice entry with long labels and optional short labels. Silently exits when the choice already exists or the data is empty. The four columns are resolved from one header read, and the list name and the ordering number are written as one block each. They used to be written cell by cell, with both column numbers read back from the worksheet on every pass of the loop. Known and unchanged: on a sheet holding only headers DataEndRow answers one row below the header, so the first append lands one row low and leaves a blank row behind.

Parameters:

• choiceName: String. Name of the choice to add.
• longLabelData: BetterArray. Long label values for the categories.
• shortLabelData: Optional Object. BetterArray of short label values. Defaults to Nothing.

---

RemoveChoice #

remove-choice

Remove one or multiple choices

Signature:

Public Sub RemoveChoice(ByVal choiceNames As String, Optional ByVal sep As String = ";")

The sheet is sorted ONCE here, for the whole call. The sort used to run inside the per-name routine, so removing five names paid five full worksheet sorts.

Parameters:

• choiceNames: String. Choice names separated by sep.
• sep: Optional String. Delimiter. Defaults to ";".

---

Translate #

translate

Translate the choice worksheet labels

Signature:

Public Sub Translate(ByVal trads As TranslationObject)

The BODY of the two label columns only. TranslateRange writes back every cell it is handed, and this used to hand it ranges that started on the header cell, because DataRange defaults includeHeaders to True. A translation table holding a tag called "label" or "short label" therefore replaced the HEADER with its translation, and from that moment on every member of this class that needs the four headers left in silence and Categories came back empty.

Parameters:

• trads: TranslationObject. Translation provider.

---

ManageRows #

manage-rows

Add or remove rows via a single toggle

Signature:

Public Sub ManageRows(Optional ByVal del As Boolean = False)

Works on the FIRST ListObject of the host sheet, whatever it is, and leaves in silence when there is none. The two branches are not symmetric: adding uses CustomTable's own default of 5 rows, removing takes 1.

Parameters:

• del: Optional Boolean. When True, removes rows. Defaults to False.

---

AddRows #

add-rows

Add rows to the choice table

Signature:

Public Sub AddRows()

---

InsertRows #

insert-rows

Insert rows at the selected position

Signature:

Public Sub InsertRows(ByVal targetCell As Range, _
                       Optional ByVal insertShift As Boolean = False)

Mirrors the selection height inside the choices ListObject. When the selection is invalid the attempt is logged and ignored.

Parameters:

• targetCell: Range. Selection anchoring the insertion point.
• insertShift: Optional Boolean. When True, inserts worksheet rows. Defaults to False.

---

DeleteRows #

delete-rows

Delete rows intersecting the selection

Signature:

Public Sub DeleteRows(ByVal targetCell As Range, _
                       Optional ByVal includeIds As Boolean = True, _
                       Optional ByVal forceShift As Boolean = False)

Parameters:

• targetCell: Range. Selection identifying rows to delete.
• includeIds: Optional Boolean. When True, renumbers the ID column. Defaults to True.
• forceShift: Optional Boolean. When True, forces worksheet row deletion. Defaults to False.

---

RemoveRows #

remove-rows

Remove empty rows from the choice table

Signature:

Public Sub RemoveRows()

---

AllChoices #

all-choices

All distinct choice names

Signature:

Public Property Get AllChoices() As BetterArray

Worksheet order is kept: the first time a name is seen is where it lands. The column is read as ONE block and the names are de-duplicated in memory. It used to read every cell TWICE from the worksheet, once to ask whether the value was already held and once to push it, and the "already held" test copied the whole internal array on every call. On a 2,000-row choices sheet that was about 8,000 worksheet crossings.

Returns: BetterArray. The distinct choice names. Never Nothing.

---

Categories #

categories

Categories of a choice

Signature:

Public Property Get Categories(ByVal choiceName As String, _
                               Optional ByVal useShortlabels As Boolean = False) _

Returns the long label categories for the specified choice. When useShortlabels is True, short labels replace long labels where available. Falls back to long labels for missing short entries. Matching on the choice name is case-INSENSITIVE, because AutoFilter criteria are and cannot be made otherwise. There used to be a full Range.Find in front of the filter, only to decide whether to bother filtering; the filter already answers "no rows matched" with a zero-length array. Dropping it means a sheet holding both list_a and List_A now answers as if they were one name. That is a data fault on the sheet, and it is also one of the ways the filter itself used to go wrong. A missing header is logged and answered with an empty array, the way every other member of this class answers. It used to raise out of the class.

Parameters:

• choiceName: String. The choice to retrieve categories from.
• useShortlabels: Optional Boolean. When True, uses short labels. Defaults to False.

Returns: BetterArray. The category values. Never Nothing. Safe to change in place.

---

ConcatenateCategories #

concatenate-categories

Concatenate all categories of a choice

Signature:

Public Property Get ConcatenateCategories( _
        ByVal choiceName As String, _
        Optional ByVal sep As String = " | ", _
        Optional ByVal trads As Object = Nothing) As String

Joins the categories with a separator. Optionally translates each category via the supplied TranslationObject.

Parameters:

• choiceName: String. The choice whose categories to concatenate.
• sep: Optional String. Separator. Defaults to " | ".
• trads: Optional Object. A TranslationObject. Defaults to Nothing.

Returns: String. The concatenated categories.

---

AddHeaders #

add-headers

Write the default headers to the choice worksheet

Signature:

Public Sub AddHeaders()

Writes the four standard column headers starting at the configured origin cell, in this order: list name, ordering list, label, short label. The label header is always the DEFAULT one. A sheet that carried a translated label column loses it here.

---

Export #

export

Export the current choice object to a workbook

Signature:

Public Sub Export(ByVal Wkb As Workbook, Optional ByVal Hide As Long = xlSheetHidden)

Delegates to DataSheet.Export, including hidden names.

Parameters:

• Wkb: Workbook. Destination workbook.
• Hide: Long. Worksheet visibility constant. Defaults to xlSheetHidden.

---

Import #

import

Import choices from a worksheet

Signature:

Public Sub Import(ByVal fromWksh As Worksheet, _
                   ByVal fromStartRow As Long, _
                   ByVal fromStartcol As Long, _
                   Optional ByVal clearSheet As Boolean = False)

Optionally clears the sheet and rewrites headers before importing. Creates a temporary DataSheet from the source range and delegates to DataSheet.Import. Two things to know before passing clearSheet:=True. Cells.Clear takes the ListObject of the sheet with it, and ManageRows, AddRows, InsertRows and DeleteRows all need that table, so they do nothing until something builds it again. And the import itself matches source columns to target columns case-SENSITIVELY, unlike every lookup this class makes on its own.

Parameters:

• fromWksh: Worksheet. Source worksheet containing choices.
• fromStartRow: Long. First data row in the source.
• fromStartcol: Long. First data column in the source.
• clearSheet: Boolean. When True, clears the sheet before import. Defaults to False.

---

HarvestCollaboratorCheckings #

harvest-collaborator-checkings

Whether the choice object has logged diagnostic messages

Signature:

Private Sub HarvestCollaboratorCheckings(ByVal collaboratorChecks As Checking)

DataSheet and CustomTable each keep a store of their own, and nobody read either of them before this line.

Parameters:

• collaboratorChecks: Checking. What the collaborator filed.

Returns: Boolean. True when diagnostic entries exist.

---

CheckingValues #

checking-values

Retrieve the diagnostic log entries

Signature:

Public Property Get CheckingValues() As Object

Returns the internal Checking object containing all logged messages. Returns Nothing when no entries have been recorded.

Returns: Object. A Checking instance, or Nothing.

---